What is Max DOP?
Max DOP stands for max degree of parallelism.
What does this mean? What I am configuring?
You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution.
This option is configured at INSTANCE LEVEL as the default but you can also force a different one when running a query using a QUERY HINT (search for MAXDOP keyword).
How do we calculate the recommended value?
Please, remember that this values are just general recommendations for SQL Server which are a good starting point for setting the “max degree of parallelism” option.
Note: We do not make any kind of verification if the databases are part of some software that needs special values on this setting (some examples: SharePoint, SAP, BizTalk, Dynamics NAV). That is why we try to find some patterns and add some recommendations to “Notes” column and we encourage you to read them before taking any action.
I heard that SQL Server 2016 allows DBAs to configure Max DOP per database, is that true?
Not only is it true, as we’ve already taken this feature into account! When you run the command through SQL Server 2016 instance three more columns (‘InstanceVersion’, ‘Database’ and ‘DatabaseMaxDop’) will be shown, as seen in the screenshots section.
Can I use this command to re-configure the Max DOP?
Not this one. Check out Set-DbaMaxDop instead.
Shows the current Max DOP setting for servers sql2008 and sqlserver2012 with the recommended value.
Test-DbaMaxDop -SqlServer sql2008, sqlserver2012
Shows Max DOP setting for server sql2014 with the recommended value. As the -Detailed switch was used will also show the ‘NUMANodes’ and ‘NumberOfCores’ of each instance
Test-DbaMaxDop -SqlServer sql2014 -Detailed
Get Max DOP setting for servers sql2016 with the recommended value. As the -Detailed switch was used will also show the ‘NUMANodes’ and ‘NumberOfCores’ of each instance. Because it is an 2016 instance will be shown ‘InstanceVersion’, ‘Database’ and ‘DatabaseMaxDop’ columns.
Test-DbaMaxDop -SqlServer sqlserver2016 -Detailed