Share via

SharePoint - MAXDOP=1

  • Article

Many, many times I discuss with SharePoint SQL people this issue of Max Degree of Parallelism or MAXDOP.

https://technet.microsoft.com/en-us/library/cc298801.aspx#Section6_3

states pretty clearly, that "To ensure optimal performance, we strongly recommend that you set max degree of parallelism (MAXDOP) to 1 SQL Server instances that host SharePoint Server 2010 databases. "

this has been tested pretty comprehensively and gives best performance and without this setting it is possible to see Table Scans for some content databases under various conditions. Yes, many of the Stored Procedures now have this set as a hint in many (>150 stored procs) but as a general rule for a SharePoint specific SQL environment, set this value to 1.

 

This setting would be strongly desirable for very large environments that also have a SharePoint dedicated SQL instance. If your environment is small with low demands upon SQL, or the SQL instance is shared with other applications, this can be overlooked.

The issues of table-scans and slow index rebuilds are being heavily addressed by improvements in SharePoint 2013. Nevertheless, the recommendation remains:

https://technet.microsoft.com/en-us/library/ee805948(office.15).aspx

 

an interesting example showing good methods to identify if this is needed for your environment

https://blogs.msdn.com/b/grahamk/archive/2009/06/15/troubleshooting-sql-server-deadlocks-and-performance-when-running-sharepoint.aspx

 

oh and look what happens in SP2013...

New-SPConfigurationDatabase : This SQL Server instance does not have the required "max degree of parallelism" setting of 1. Database provisioning operations will continue to fail if "max degree of parallelism" is not set 1 or the current account does not have permissions to change the setting. See documentation for details on manually changing the setting

Comments

  • Anonymous
    January 01, 2003
    Yes, while 2007 does not have any official document on MAXDOP, the logic behind the setting is exactly the same. So yes, a 2007 farm would also benefit if it is possible to set this. Reminder - it is global for the SQL instance, so do it only if your SQL is purely for SharePoint.

  • Anonymous
    November 16, 2011
    What is the recommended MAXDOP setting for SharePoint 2007?  Should it also be set to 1?

  • Anonymous
    November 07, 2014
    As a related side note...

    http://www.jamesbl.com/post/2013/08/21/SharePoint-2013-and-MAXDOP–No-Configuration-Required.aspx