How to Configure and Tune MAXDOP

MAXDOP is a limit per task, not per query, and that’s the part I had wrong for a long time. I read it as a cap on how many processors a query could use, so I assumed MAXDOP 8 meant a query would never occupy more than 8 schedulers. The documentation is specific that it “isn’t a per request or per query limit,” and that “a single request can spawn multiple tasks up to the MAXDOP limit, and each task uses one worker and one scheduler.” A plan with several parallel branches can have more workers going than the number you set.

Setting it to 0 lets SQL Server use all available processors up to 64, which the docs say “isn’t the recommended value for most cases.” Setting it to 1 suppresses parallel plans. Anything from 1 to 32,767 sets the cap, and if you set it higher than the processors you have, you get the number you have.

How to set MAXDOP

There are so many conflicting blog posts out there about how to set MAXDOP, so to cut through the noise I tend to fallback to Microsoft’s recommendation. This isn’t a one-size-fits-all recommendation, but sometimes the shoe just fits. Below is Microsoft’s current recommendations for SQL Server 2016 or newer:

To make this even simpler, I rewrote this as a T-SQL script to give me the recommendation without needing to think about it:

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;

One caveat on the script. It reads numa_node_count from sys.dm_os_sys_info and divides the CPU count by it, and Microsoft’s table means soft-NUMA nodes rather than hardware ones. The soft-NUMA documentation says that “with SQL Server 2016 (13.x), whenever the SQL Server Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default”, and that those nodes “ideally contain eight cores, but can go down to four or up to eight physical cores per node”. The guidance is written to keep a parallel query’s workers inside one of those nodes. On a big socket where soft-NUMA has kicked in, the number my script divides by isn’t necessarily the number the table is talking about, so I’d check sys.dm_os_nodes before trusting the output on anything large. It’s been right on everything I’ve run it against, which have all been modest two-socket boxes.

The results are pretty straight forward, take the recommended value from the script and use the following to make the actual change:

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 things. Changing MAXDOP can and will impact query performance, so I measure the slowest query I have and a reasonably fast one before I touch anything, then set it per Microsoft’s recommendation and watch what happens over the next few hours and days.

If queries get slower, the plan tells you which direction you went wrong. Too high looks like queries that used to run fine on one thread now spreading across several and not finishing any sooner, and waiting longer to start while the threads get scheduled. Too low looks like the opposite, one thread grinding through work that could have been split up, with SOS_SCHEDULER_YIELD climbing in the wait stats.

I’d rather change this at the same time as the cost threshold for parallelism than on its own. MAXDOP sets how wide a parallel plan can go, and the cost threshold decides whether a query goes parallel at all, so lowering MAXDOP while the threshold sits at the default still leaves small queries going parallel, just less widely. I expected the cost threshold documentation to defend that default of 5 and it doesn’t: “The default value of 5 is a starting point, not a recommendation. On modern SQL Server systems, raising it can help to keep smaller OLTP queries executing with serial plans.” I don’t have a clean before and after to show for that, it’s just the order I do them in now.

If you’d asked me, I’d have said the threshold was a hard gate, and it isn’t quite. The same page says “in certain cases, a parallel plan might be chosen even though the query’s plan cost is less than the current cost threshold for parallelism value,” because the decision gets made from a cost estimate produced earlier in optimization. So a cheap query running parallel isn’t automatically a sign the setting is wrong.

Using Query OPTION (MAXDOP n)

The OPTION (MAXDOP n) query hint forces a specific degree of parallelism on one query. I use it for measuring rather than for fixing, since running the same query at a few different values tells me what the setting is actually worth before I change anything server-wide. You can run it in production, it’s a hint on one statement and it doesn’t change any configuration. Here’s an example of how to use this 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 using it at the query level may be the only option to satisfy stakeholders. Just keep in mind the queries using this hint and use it sparingly. If your SQL Server ever changes, it’s easier to change it once at the database level, than to find and change every query and remeasure their performance to use the right setting. The hint isn’t the only override either, there’s a database scoped configuration and a Resource Governor workload group setting, and a query can be sitting under any of them.

Two things I’d check before assuming the instance is misconfigured. On SQL Server 2019 and newer, setup recommends a MAXDOP value during installation based on the processors it sees, so a server someone installed carefully may already be set correctly and the script will agree with it. And on 2022 there’s DOP feedback, which adjusts parallelism for repeating queries on its own based on elapsed time and waits, so a query’s actual degree of parallelism may not match what you set.

With all of this said, no two shops are the same. Follow the Microsoft recommendation until it no longer works for you. Ongoing measurements and monitoring is important to avoid those 2:00AM wake up calls.