SQL Server Settings: MAXDOP

What is MAXDOP and how does it affect my SQL Server?

MAXDOP is short for Maximum Degree of Parallelism. SQL Server uses the MAXDOP setting to determine the maximum number of processors that can be used for the execution of a single query or operation. In simple terms, it controls how many CPU cores SQL Server can utilize to process a query.

How MAXDOP works in SQL Server

When SQL Server processes a query, it can break down the task into multiple smaller tasks and execute them concurrently on different processors. This is known as parallel execution, or “parallelism.”

MAXDOP specifies the maximum number of CPU cores that can be utilized for this parallel execution. This parallelism can improve query performance for large datasets or complex operations. However, an incorrect MAXDOP setting can also lead to inefficient query execution plans, increased CPU utilization, and potentially degraded performance.

MAXDOP is set at the server level. The default configuration setting for MAXDOP is usually set to 0 (unlimited). This means SQL Server can use all available processors for parallel queries. While this setting can be suitable for some workloads, it may not be optimal in all situations.

If SQL Server is allowed to use too many CPU cores for parallel execution, it may cause excessive contention and actually slow down the performance of other tasks and queries running on the server. On the other hand, if SQL Server is restricted to use too few CPU cores, it might not fully utilize the available hardware resources, resulting in slower query processing times.

It’s the Goldilocks Principle as applied to SQL Server. Not too many cores, nor too few. It needs to be just right.

Configuring MAXDOP for your SQL Server

Microsoft provides some initial guidance for MAXDOP. However, configuring MAXDOP appropriately for your specific environment and workload is crucial. You may need to adjust MAXDOP based on factors such as the number of processors, the nature of your queries, workload characteristics, and the specific requirements of your SQL Server environment. It is essential to carefully tune this setting based on the characteristics of your database and the queries it processes to achieve the best overall performance.

How to Check the MAXDOP Setting

You can view your server’s MAXDOP setting by viewing the sever properties in SQL Server Management Studio. Simply right click on your server and select “Properties”, and navigate to the “Parallelism” section under “Advanced”.

You can also use the built-in sp_configure system stored procedure to view and change the MAXDOP setting. sp_configure is a system stored procedure designed to show and change server-level configuration settings on SQL Server. To use sp_configure to find your MAXDOP setting, run the following code:

EXEC sys.sp_configure 'max degree of parallelism'; 

How to change the MAXDOP Setting

Changing your server’s MAXDOP setting is just as easy as viewing it. From SQL Server Management Studio simply change the existing value to the desired value and click “OK” at the bottom of the properties widow.

To change the MAXDOP setting using sp_configure, use the below code to set the desired value:

EXEC sys.sp_configure 'max degree of parallelism', '<desired value>';
RECONFIGURE;

Of course, you should be careful when changing MAXDOP or any other setting in SQL Server. A change may help or hurt performance. And remember, since MAXDOP is set at the SQL Server instance level, it will affect all databases on the SQL Server. A change that helps one query for one database may adversely affect other queries and other databases.

It’s important to baseline performance and monitor the results after making a change.

For more information

Do you have questions about your SQL Server’s MAXDOP setting? Or are you having some performance concerns? Here are some other posts and links that may be helpful.

• Configure the max degree of parallelism (server configuration option) – SQL Server | Microsoft Learn
• dbatools docs | Set-DbaMaxDop
• dbatools docs | Test-DbaMaxDop
• Infographic: 5 Commonly Overlooked SQL Server Settings
• What is a SQL Server Recovery Model?
• Why is My SQL Server Slow? 14 Common Reasons
• Do I Still Need a SQL Server Health Check?

If you’d like some assistance assessing your SQL Server workload, contact us. We’re happy to help.