Optimizing SQL for SharePoint
When I do Risk Assessments (that’s the proactive side of my job, rather than fixing issues, I’m trying to prevent issues from happening :) at customers, certain things always popup when it comes to the configuration of SQL server and I wanted to share these things with you so hopefully this things won’t be flagged when I come along for a Risk Assessment at your place ;) I will cover the top two SharePoint-SQL ‘issues’ I find most at customers:
- AUTO_UPDATE_STATISTICS & AUTO_CREATE_STATISTICS
MAXDOP should be set to 1 on the SQL Server instance.. why? Because the SharePoint Product Group says so.. ! But Robin hang on.. WTH is MAXDOP? MAXDOP stands for Max Degree of Parallelism, let me quote a MSDN article which describes this setting best:
When SQL Server runs on a computer with more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors up to 64 processors. To suppress parallel plan generation, set max degree of parallelism to 1. Set the value to a number greater than 1 to restrict the maximum number of processors used by a single query execution. The maximum value for the degree of parallelism setting is controlled by the edition of SQL Server, CPU type, and operating system. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored. Source : http://msdn.microsoft.com/en-us/library/ms181007.aspx
Makes sense? In very short it comes down that SQL Server can utilize the amount of processors that are available to execute the queries in parallel and thus very efficiently. Now this does sound like a very cool nifty feature eh? It actually is.. however, sometimes SharePoint tries to produce some ad-hoc SQL queries where SQL Server has no clue what the most optimal path could be and thus it could lead to bad performance when it tries to utilize all the processors that are available. Furthermore, the PG has tested a lot with variable settings and came to the conclusion to NOT to use MAXDOP is the most stable and performing way. And, when MAXDOP can be helpful, it is stated in the stored procedure itself.
Now, why this is not done at most installations is most probably because a) it’s the default setting when installing SQL Server and b) although it is listed in TechNet article, it is just an one-liner in the section called “Set SQL Server options” at “Storage and SQL Server capacity and configuration (SharePoint Server 2010)” which you can read about here http://technet.microsoft.com/en-us/library/cc298801.aspx and c) setting MAXDOP to be 0 (or any number other 1) is actually a SQL Best Practice, hence the default value.
And here is a blog post proofing that MAXDOP set to 0 was not good idea and by setting it to 1 solved the issue (please note that this post is about SharePoint 2007) http://blogs.msdn.com/b/grahamk/archive/2009/06/15/troubleshooting-sql-server-deadlocks-and-performance-when-running-sharepoint.aspx
AUTO_UPDATE_STATISTICS & AUTO_CREATE_STATISTICS
There is lot of confusion and discussion about these two settings that are set per database. The reason for the confusion is, that these settings are different per SharePoint version, for SharePoint 2007 it is recommended to enable AUTO_UPDATE_STATISTICS & AUTO_CREATE_STATISTICS.. However, since the release of SP2 for SharePoint 2007, the product team introduced a new timerjob called “Database statistics” which itself takes care in updating the statistics for the databases. This timerjob is also available in SharePoint 2010 therefore we recommended disabling AUTO_UPDATE_STATISTICS. When a new content database is created, the AUTO_CREATE_STATISTICS is set to be disabled which is the recommendation. It can happen, after a migration from SharePoint 2007 to 2010, that this option is still enabled and therefore should be set to disabled as well.
In short, SharePoint 2010, both should be set to be disabled. For SharePoint 2007, it is recommended to have them both enabled.
And just to be clear, it is all about “AUTO”, it does not mean that the statistics are never updated. It’s just that SQL Server itself is not responsible anymore to do the updating, instead we have SharePoint that is responsible for it.
For more detail and information, please read this post http://blogs.msdn.com/b/chunliu/archive/2011/11/17/auto-update-statistics-and-auto-create-statistics-on-or-off-for-sharepoint-2010-databases.aspx that covers all the information on the SharePoint 2010 databases side.
- Database Maintenance for Microsoft SharePoint 2010 Products http://www.microsoft.com/download/en/details.aspx?id=24282 by Bill Bear and Bryan Porter