SharePoint Performance and File Groups for Temp db, Search db, and Content dbs

I just finished the Large SharePoint and SQL session at Teched. One of the things Paul demo’ed in the session was multiple files for the content database.  In the past the temp db was the only database that was supported with file groups since SharePoint databases hadn’t supported them.  More recently with the testing that was done, file groups are now supported on search, temp, and content databases.  I knew that search and temp db were supported, but didn’t think that content was supported.  That’s legacy.  Paul is right.  (Sorry Paul.) Content database file groups are supported and this is clearly documented on "Technet article: Physical storage recommendations (Office SharePoint Server)" updated April 23, 09.

 

In the case study "Using Microsoft Office SharePoint Server to implement a large-scale content storage scenario with rapid search availability" published by Microsoft, Paul and team recommended splitting the Temp db and search database into multiple data files to match the number of core processors in SQL. Paul and team did a number of performance tests and found significant gains.  In some environments I’ve seen huge bottlenecks addressed with these somewhat simple configuration changes.  I suggested in the talk today that the gains are next to adding RAM.  For performance reasons it totally makes sense to split the temp and search database with multiple files per core.  (Note for smaller environments or those not experiencing disk I/O bottlenecks, I’d keep this in your back pocket as a future option.)

 

The content database multiple file groups may give you some better disk I/O, but the database isn’t designed to split tables across the files or file groups. If you read the article on Technet, you’d expect that you will find some performance gains in splitting large content databases across multiple physical LUNS or drives. From the Physical Storage article…  "For improved performance for large content databases and the SSP search database, consider using multiple data files."

 

I would caution SharePoint admins and SQL admins to weigh out the complexity of files and filegroups in relation to backup/restore and maintenance with performance.  Most gains will come from the temp db, and search performance.

 

In the Physical Storage Article on Technet note the wording: “The use of multiple data files for databases other than content database and the SSP search database is not supported.”  The temp database since it isn’t a SharePoint database is NOT excluded as a result of this statement.  This element has been clarified by the SharePoint Customer Advisory team to include the temp db.  It simply isn’t called out because it’s not a SharePoint database.

This site uses Akismet to reduce spam. Learn how your comment data is processed.