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

 

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

More information:

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

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

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

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.