SharePoint - MAXDOP=1

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