What is the MAXDOP Setting in SQL Server?
Category: Performance
The Maximum degree of parallelism option is used to limit the number of processors to be used by SQL Server 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 issues and limit concurrency.
This setting goes together in harmony with “Cost threshold for parallelism” (CTP).
Microsoft best practices for setting MaxDOP
When the query goes in parallel, you don’t want it to use all CPU cores, as a faulty query will affect everything SQL Server does.
But you don’t want to allow queries to go in parallel and only allow CPU cores to be idle.
There is no single rule that works for all SQL Servers.
MaxDOP configuration will depend on the machine and SQL Server version.
Note: SQL Server 2019 (15.x) automates recommendations for MaxDOP configuration settings during the installation process. You can accept the suggested setting or enter your value.
How to change the maximum degree of parallelism?
To change the default value at the server level using SSMS, follow these steps:
- Open Object Explorer, right-click instance and select Properties.
- Click Advanced.
- Adjust the values in the Max Degree of Parallelism box.
You can also change it using TSQL as in 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
