dbatools is a free PowerShell module with over 180 SQL Server administration, best practice and migration commands included.


Test-DbaMaxDop

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?

We have implemented the algorithm shared by Microsoft KB2806535 and also on Sakthivel Chidambaram calculator.

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.

You can read more about this SQL Server 2016 new feature on MSDN and SQL Server Database Engine Blog

Can I use this command to re-configure the Max DOP?

Not this one. Check out Set-DbaMaxDop instead.

Screenshots

Example without SQL 2016 version
Example without SQL 2016 version
Example without SQL 2016 version with -Detailed switch
Example without SQL 2016 version with -Detailed switch
Example with SQL 2016 version
Example with SQL 2016 version
Example with SQL 2016 version with -Detailed switch
Example with SQL 2016 version with -Detailed switch

Examples

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

This command was created by Cláudio Silva. You can find Cláudio on Twitter and LinkedIn.

Get-Help

From PowerShell, execute Get-Help Test-DbaMaxDop -Detailed for more information on this function.

Source Code

Want to see the source code? View Test-DbaMaxDop.ps1 on GitHub
 

Related commands