Default max degree of parallelism (MAXDOP)
The max degree of parallelism option is used by SQL Server to limit the number of CPUs to be used for parallel query execution.
This is just a fancy way of saying, how many CPU cores do I want utilized when query runs.
By default, SQL Server uses all available CPUs during query execution.
Defaults are not good.
While using all CPUs available for large queries sometimes makes sense, in most cases this is a bad setting. It can cause performance problems and limit server's concurrency.
This setting goes hand in hand with the cost threshold for parallelism.
Microsoft best practices for MAXDOP
When a query goes parallel (multiple CPU cores are used to answer the query), you do not necessarily want all CPU cores used. This is where one heavy/bad query will impact everything your SQL Server runs.
But you do not want to let queries go parallel and just have CPU cores idle.
There is no one rule which works for cases.
MAXDOP configuration will depend on the machine and the version of the SQL Server.
Check out the Microsoft best practice guide for setting 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 check and change the max degree of parallelism (MaxDOP) settings
To change the default value at the server level using SSMS, follow these steps:
- Open Object Explorer
- Right-click the instance
- Select Properties
- Click the Advanced node
- In the Max Degree of Parallelism box, set the value.
Also, you can change MAXDOP using T-SQL, using TSQL 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
