How to Configure and Tune MAXDOP

When I began learning SQL Server administration, I researched numerous articles about configuring Max Degree of Parallelism (MAXDOP). MAXDOP controls the number of logical processors a single parallel plan can utilize. A setting of 0 allows SQL Server to use all available processors (up to 64). A setting of 1 forces each query to execute on a single thread. Intermediate values allocate a predictable portion of CPU capacity to each parallel operator.

How to set MAXDOP

Despite extensive information available, conflicting recommendations about MAXDOP configuration are common. To establish clarity, I rely on Microsoft’s official guidance. While this may not be universally optimal, it provides a solid foundation. Below are Microsoft’s current recommendations for SQL Server 2016 and newer:

For simplified implementation, I developed a T-SQL script that provides recommendations automatically:

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 straightforward. Use the recommended value from the script and apply it with the following configuration:

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

Monitoring MAXDOP effectiveness requires careful observation. Since MAXDOP changes impact query performance, when should adjustments be made? My approach is to measure performance of both slow and reasonably fast queries. Apply Microsoft’s recommended MAXDOP setting, then monitor overall performance over subsequent hours, days, and weeks. If queries exhibit slower performance, examine the query plans. Indicators of excessive MAXDOP include queries that previously ran efficiently with minimal threads now utilizing more threads without performance improvement, or increased execution wait times. Conversely, insufficient MAXDOP manifests as queries constrained to single threads when they would benefit from parallelism, or elevated CPU wait statistics. Adjust settings based on observed performance patterns.

Using Query OPTION (MAXDOP n)

Database administrators or developers might use the OPTION (MAXDOP n) query hint to enforce specific parallelism for individual queries. This practice is generally not recommended for production environments, but can be valuable for performance testing and troubleshooting. Here’s an example of this hint:

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

Database-level MAXDOP settings may not be optimal for every query, making query-level hints necessary in some cases to meet performance requirements. However, use this approach sparingly and maintain awareness of queries using hints. When SQL Server configurations change, adjusting at the database level is more efficient than locating and modifying individual queries.

Every environment has unique characteristics. Begin with Microsoft’s recommendations and adjust based on performance requirements. Continuous monitoring and measurement are essential to prevent performance issues that require emergency intervention.