What is the MAXDOP setting in SQL Server?

Default “Max degree of parallelism” (MAXDOP)

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”.

By the way, this check is a part of our SQL Server Health Check service.

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.
Figure 1 – SQL Server MAXDOP setting.

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

  1. EXEC sp_configure 'show advanced options',
  2. GO
  3. RECONFIGURE WITH OVERRIDE;
  4. GO
  5. EXEC sp_configure 'max degree of parallelism', 16;
  6. GO  
  7. RECONFIGURE WITH OVERRIDE; 
  8. GO

More information:

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

Picture of Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *