How to Configure and Tune MAXDOP

When I first started learning SQL Server administration, I read a lot of articles on configuring Max Degree of Parallelism (MAXDOP). Most of them described it as a limit on how many processors a query can use. Microsoft’s documentation is more specific. The limit applies per task, and it “isn’t a per request or per query limit.” A single parallel request can start several tasks, each using one worker and one scheduler, so a plan with more than one parallel branch can have more workers running than the number you set.

A setting of 0 lets SQL Server use all available processors, up to 64, and the same page says that “isn’t the recommended value for most cases.” A setting of 1 stops SQL Server from generating parallel plans. Any value from 1 to 32,767 sets the limit, and if it’s higher than the number of processors available, SQL Server uses the number available.

How to set MAXDOP

There are a lot of conflicting blog posts about how to set MAXDOP, so I fall back to Microsoft’s recommendation. It isn’t right for every server, but sometimes it fits as-is. These are Microsoft’s current recommendations for SQL Server 2016 and newer:

The NUMA nodes in that table are the soft-NUMA nodes SQL Server creates on its own when it detects more than eight physical cores per NUMA node or socket, or the hardware NUMA nodes if soft-NUMA is turned off. The page says the recommendations are aimed at keeping all the worker threads of a parallel query inside one of those nodes.

To make this simpler, I rewrote the table as a T-SQL script that gives me the recommendation without working it out by hand:

DECLARE @maxdop INT
	,@cpu_count INT
	,@numa_count INT
	,@cpu_per_node INT
	,@recommended INT;

SELECT @maxdop = CONVERT(INT, value_in_use)
FROM sys.configurations
WHERE name = 'max degree of parallelism';

SELECT @cpu_count = cpu_count
	,@numa_count = numa_node_count
FROM sys.dm_os_sys_info;

IF @numa_count = 1
BEGIN
	IF @cpu_count <= 8
		SET @recommended = @cpu_count;
	ELSE
		SET @recommended = 8;
END
ELSE
BEGIN
	SET @cpu_per_node = @cpu_count / @numa_count;

	IF @cpu_per_node <= 16
		SET @recommended = @cpu_per_node;
	ELSE
		SET @recommended = CASE 
				WHEN (@cpu_per_node / 2) > 16
					THEN 16
				ELSE (@cpu_per_node / 2)
				END;
END

SELECT @maxdop AS Current_MAXDOP
	,@recommended AS Recommended_MAXDOP
	,CASE 
		WHEN @maxdop = @recommended
			THEN 'Pass'
		ELSE 'Fail'
		END AS MAXDOP_Status;

The script divides cpu_count by numa_node_count from sys.dm_os_sys_info. The documentation for that view says numa_node_count “includes physical NUMA nodes and soft NUMA nodes,” which matches what the table is counting. The column exists from SQL Server 2016 SP2 onward, so the script won’t run on anything older.

The results show the current value, the recommended one, and whether they match. To make the change, put the recommended value into this:

USE master;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

EXEC sp_configure 'max degree of parallelism', 8; -- <-- Set your recommended value here.
GO
RECONFIGURE WITH OVERRIDE;
GO

EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

How to Monitor MAXDOP

This is where things can get messy if you overthink them. Changing MAXDOP will change query performance, so I measure both the slowest query I have and a reasonably fast one first. Then I set MAXDOP to Microsoft’s recommendation and keep an eye on overall performance over the next few hours, days and weeks.

If queries get slower, check the query plan. MAXDOP is likely too high if queries that used to run fast on a single thread are now using more threads without running any faster, or are waiting longer to start. If it’s too low, you’ll see the opposite, with queries stuck on one thread doing work that could have been split up. Don’t be afraid to adjust it as you learn more about the server.

The cost threshold for parallelism is worth looking at alongside it. MAXDOP limits how wide a parallel plan can go, and the cost threshold decides whether SQL Server considers a parallel plan at all. The cost threshold documentation says the default of 5 “is a starting point, not a recommendation,” and that raising it can keep smaller OLTP queries on serial plans. It suggests changing it in small increments and watching a full business cycle between changes. The same page has a short table of symptoms. When the threshold is too low, too many light queries go parallel and CXPACKET and CXCONSUMER dominate the wait stats. When it’s too high, CPU-heavy queries that should go parallel don’t, and SOS_SCHEDULER_YIELD dominates instead.

One detail on that page is easy to miss. A query can still get a parallel plan when its cost is below the threshold, because the decision is made from a cost estimate taken earlier in optimization than the final plan cost you see. So a cheap query running in parallel isn’t proof the threshold is set wrong.

Using Query OPTION (MAXDOP n)

Some DBAs, or a sly developer, might use the OPTION (MAXDOP n) query hint to force a specific degree of parallelism on a specific query. In most cases this isn’t a recommended practice, and it’s best suited to measuring and troubleshooting performance at different settings. You can use it in production. Here’s an example of the hint:

SELECT column1, column2
FROM VeryLargeTable
OPTION (MAXDOP 4);

Setting MAXDOP at the database level probably won’t be the right choice for every query, so the query level may be the only way to satisfy stakeholders. Keep track of the queries using this hint and use it sparingly. If the server ever changes, it’s easier to change the setting once than to find every query with a hint and remeasure it.

The query hint is one of three ways to override the server setting. The MAXDOP documentation lists the query level (the hint, or a Query Store hint), the database level through a database scoped configuration, and the workload level through a Resource Governor workload group. When a query’s parallelism doesn’t match the server setting, one of those is usually the reason.

Two newer features can also explain a value you didn’t set yourself. Starting with SQL Server 2019, setup recommends a MAXDOP value during installation based on the processors it finds, so a newer install may already match the table. SQL Server 2022 added degree of parallelism (DOP) feedback, which adjusts parallelism for repeating queries based on their elapsed time and waits.

No two shops are the same. Follow the Microsoft recommendation until it no longer works for you, and keep measuring after the change so that problems show up before the 2:00 AM wake-up calls do.