Is MAXDOP (Max Degree of Parallelism) Configured Correctly?

What is MAXDOP in SQL Server?

Max degree of parallelism (MAXDOP) is a setting in SQL Server that controls how many processors may be used for parallel plan execution. Parallel plan execution is good—it lets SQL Server make the best use of all those processors in modern servers. However, MAXDOP can be configured incorrectly. Too much or too little parallelism can negatively impact server performance.

Windows CPU Monitor

There’s a lot of information on the internet about how to set Max Degree of Parallelism (MAXDOP) correctly. Microsoft even provides a knowledge base article with their recommendations. However, if you look at it, there’s a fair amount of information to digest. I’m lazy forgetful efficient, so I wanted to put this into a script I could easily reuse and not have to remember all the details.

Please note that these are just guidelines, and you should consider carefully whether they fit your workloads and scenarios. As is the case anytime you are evaluating system settings, you should test carefully before and after making changes.

The full script is attached at the end. It’s designed for SQL Server 2016 or later. I’m going to cover a few of the key points here to explain how and why the script does certain things.

What is the Effective MAXDOP Setting?

Max Degree of Parallelism can be set to a value ranging between 0 and 32,767. The value tells SQL Server how many processors it should use for parallel plan execution. The default MAXDOP setting of 0 tells SQL Server to use all available   processors (up to a max of 64). However, just because the computer has a processor, doesn’t make it an available processor. Licensing and other factors can impact whether SQL Server considers a processor available. So, when MAXDOP is configured as 0, the script looks up the number of online schedulers using the sys.dm_os_schedulers  system view.  This ensures that it’s using the correct effective MAXDOP setting.

SELECT 
    @EffectiveMaxDop = CAST(value_in_use AS INT)
FROM 
    sys.configurations
WHERE 
    [name] = N'max degree of parallelism';

IF @EffectiveMaxDop = 0
BEGIN
		SELECT 
		@EffectiveMaxDop = COUNT(*)
	FROM
		sys.dm_os_schedulers
	WHERE 
		scheduler_id <= 1048575
		AND is_online = 1;
END

Getting the NUMA Nodes and Processors

The next step is to get the NUMA nodes and the logical processors per node. In SQL Server 2016, this is pretty simple. Both can be retrieved from the sys.dm_os_nodes view. The only caveat is that the dedicated DAC node should be excluded, which is accomplished by skipping the node_id with the value 64.

SELECT 
    @NumaNodeCount = COUNT(*), @LogicalProcessorPerNumaNodeCount = MAX(online_scheduler_count)
FROM 
    sys.dm_os_nodes 
WHERE node_id <> 64; --Excluded DAC node

Determining if MAXDOP is in the Recommended Range

I’m not going to cover the logic for determining this in great detail, as it’s covered extensively in the Microsoft KB article referenced in the opening paragraph of this article. The primary factors in determining which recommendation applies are:

1. Do you have a single NUMA node or multiple nodes?
2. Is the number of processors per NUMA node greater than the threshold defined in the KB article?

The script evaluates these primary factors and provides a message based on the result.

The Results

When you execute the script, you will see a message like this if the configuration follows recommended guidelines:

MAXDOP setting is in the recommended range. Reported values – NUMA: [1], Logical Processor: [2], MAXDOP: [2]

If MAXDOP is not in the recommended range, you will see a message like this:

Notice that this is reported as an error. This may not be desired in some cases, particularly if you monitor your SQL Server for Transact SQL errors and don’t want this script to trigger any alerts. In that case, you can modify the final RAISERROR in the script to change the severity from 11 to 10-or-less (I typically use 0). To understand  RAISERROR syntax and behavior better, visit Microsoft’s documentation here.

Original as an error:

RAISERROR('MAXDOP is not set based on recommendations in KB2806535. %s Reported values - NUMA: [%d], Logical Processor: [%d], MAXDOP: [%d]', 
	11, 1, @ResultMessage, @NumaNodeCount, @LogicalProcessorPerNumaNodeCount, @EffectiveMaxDop);

Summary

This is a handy script for quickly checking the MAXDOP setting without looking up or memorizing the recommendations provided by Microsoft. In my next article, I’m going to use this script to help define a new Advisory Condition for the SentryOne Monitoring Platform. With that Advisory Condition, you can be automatically be alerted if any of your SQL Servers have a MAXDOP setting that doesn’t match the Microsoft recommendation. You can also automate setting Max Degree of Parallelism to align with the recommendation, which is pretty handy in cases where you are certain you trust the recommendation.

DOWNLOAD THE FULL SCRIPT HERE