How to Configure and Tune MAXDOP

When I first started learning about SQL Server administration, I read many articles on configuring Max Degree of Parallelism (MAXDOP). MAXDOP limits the number of logical processors a single parallel plan can use. A setting of 0 lets SQL Server use all available processors (up to 64). A setting of 1 forces each query to run on a single thread. Intermediate values reserve a predictable share of CPU capacity for each parallel operator.

How to set MAXDOP

All that said, 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;

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 when should you change it? My advice is to measure the performance of both your slowest query and a reasonably fast one. Set MAXDOP per Microsoft’s recommendation, then keep an eye on overall performance over the next few hours, days, weeks, etc… If you find queries are running slower, check the query plan. You’ll notice MAXDOP is likely set too high if queries that once ran fast with no additional threads are now using more threads and not running any faster, or queries are waiting longer to start executing. You’ll notice the opposite problem if MAXDOP is set too low. Queries will be suffocating on just the one thread, begging for some help from the server to pull the data it needs, or CPU WAITSTATS like . Don’t be afraid to tinker with it as you learn more.

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 parralelism on a specific query. In most cases, this isn’t a recommended practice, and is usually best suited for measuring and troubleshooting performance with different settings, you CAN do this in PROD. 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.

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.