Updated on May 23, 2013
I had an opportunity to present on SQL Server User Group Indonesia monthly meeting on Oct 27, 2011 about SQL performance tuning from SharePoint perspective. Most of DBAs might be familiar with SQL stuff, but not on what SharePoint expects from SQL Server. Understanding how SharePoint works is really important in tuning SQL Server.
Most of the SharePoint contents are stored in SQL Server. Documents, picture, pages, Lists, and its configuration. Document and picture are stored as binary in SQL, so do not expect to find the physical files in SharePoint server.
Most of physical files that are not stored in database located under 12 hive (14 hive in SharePoint 2010). This folder contains customization files such as CSS, master pages, features, ASPX page. There are some occasion that developer prefer to store master page and layout in the library so it is actually in your SQL tables. So please communicate closely with the development team to understand exactly on file location. However, rest assure that 90% of SharePoint contents are inside SQL Server.
Below are some guidance to configure SQL Server for SharePoint performance:
Configure instance level index fill factor to 70%. Most of veteran DBAs now that this setting is optimized for read operation with moderate insert and update. You may think to optimize fill factor on table and index level, but this is not possible in SharePoint database. Microsoft does not recommend to alter table and index of SharePoint DBs. Default fill factor is 0 so you need to alter this setting. The 70% is not a magic bullet, do continuous monitoring and adjust fill factor accordingly.
Set MAXDOP to 1; contrary to popular belief, SharePoint does not benefit much from parallelism. There are lots of cases when SharePoint produces ad-hoc queries that SQL Server may struggle find the most optimal path. Setting the value to 1 means forcing SQL Server to not create parallel plan. More discussion and testing about this are here:
Turn OFF update statistics; and configure proper schedule to refresh it. Make sure to set proper schedule for this to avoid outdated statistics.
Plan your data and log file growth. The default data file growth in SQL Server is 1 MB so it does not make any senses for SharePoint system. Imagine somebody is uploading 5 MB file and there are 100 uploads per day. It means your data file will grow 500 times/day; this will create bottleneck in IO system and usually escalated to CPU. I usually prefer to do capacity planning to forecast database size for 3-6 months. So if you think that your data growth will be 50 GB in 6 months, then set the data file size to 50 GB and configure its growth rate to something around 500 MB. Under previous scenario, your database only needs to grow once per day to accommodate 500 uploads. This reduce IO bottleneck significantly and speed up SharePoint performance.
Plan your tempdb; this can be a very long discussion and deserve separate post. There is a reference from Paul Randall about tempdb optimization here:
There is a long whitepaper from Microsoft about this: