Item: Default Max degree of parallelism
The max degree of parallelism option is used by SQL Server to limit the number of processors to use for parallel plan execution.
By default, SQL uses all available CPUs during query execution.
Defaults are not good.
While this is great for large queries, it can cause performance problems and limit concurrency.
This setting goes together in tuning with the “Cost threshold for parallelism”.
Microsoft best practices to set MaxDOP
When query goes parallel, you do not necessarily want it to use all CPU cores, as one bad query will impact everything SQL Server is doing.
But you do not want to let queries go parallel and just have CPU cores idle.
There is no single rule that works for all SQL Servers.
MaxDOP configuration will depend on the machine and the version of the SQL Server.
Check out the Microsoft best practice guide for set the max degree of parallelism.
Note: SQL Server 2019 (15.x) automatizes the recommendations for settings MaxDOP configuration during the installation process. You can accept the recommended setting or enter your value.
How to change the max degree of parallelism
To change the default value, at the server level, using SSMS, follow the steps:
- Open Object Explorer, right-click the instance and select Properties.
- Click the Advanced node.
- In the Max Degree of Parallelism box, set the value.
Also, you can change it using TSQL, like the example below:
EXEC sp_configure 'show advanced options', GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure 'max degree of parallelism', 16; GO RECONFIGURE WITH OVERRIDE; GO