What is the MAXDOP setting in SQL Server?

Category: Performance
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:

1. Open Object Explorer, right-click the instance and select Properties.
2. Click the Advanced node.
3. In the Max Degree of Parallelism box, set the value.

Also, you can change it using TSQL, like the example below:

More information:

Microsoft – Configure the max degree of parallelism server configuration option
Ahmad Yaseen – SQLShack – Importance of SQL Server max degree of parallelism