SQL Server MAXDOP Recommendation Script

calendar Apr 17, 2026 / · 6 min read · sql server maxdop performance sp_configure numa sql scripts database administration cpu configuration  ·
Share on: linkedin copy

MAXDOP (max degree of parallelism) controls how many CPU threads SQL Server can use for a single parallel query. The default value of 0 allows SQL Server to use all available processors, which can cause large parallel queries to monopolise the server. This script reads the CPU topology from system DMVs and calculates Microsoft's recommended MAXDOP value for your instance.

Purpose and Overview

Setting MAXDOP incorrectly is one of the most common SQL Server configuration gaps. With MAXDOP 0 (unlimited), a single parallel query can consume every logical processor on the server, blocking other queries from getting CPU time. Setting MAXDOP too low, such as 1, disables parallelism entirely and slows down queries that benefit from parallel execution.

Microsoft's recommendation is based on CPU topology:

  • Single NUMA node: set MAXDOP to the number of logical processors, up to 8
  • Multiple NUMA nodes without hyperthreading: set MAXDOP to 8
  • Multiple NUMA nodes with hyperthreading: set MAXDOP to the number of physical cores per NUMA node, up to 8

This script reads the topology automatically from sys.dm_os_sys_info and sys.dm_os_schedulers, calculates the recommended value, and compares it to the current sp_configure setting.

Code Breakdown

Script 1: MAXDOP Recommendation Calculator

 1DECLARE @LogicalCPUs        INT,
 2        @PhysicalCores      INT,
 3        @NUMANodes          INT,
 4        @HyperthreadRatio   INT,
 5        @LogicalPerNUMA     INT,
 6        @PhysicalPerNUMA    INT,
 7        @MaxDOPRecommended  INT;
 8
 9SELECT
10    @LogicalCPUs      = cpu_count,
11    @HyperthreadRatio = hyperthread_ratio
12FROM sys.dm_os_sys_info;
13
14SELECT @NUMANodes = COUNT(DISTINCT parent_node_id)
15FROM sys.dm_os_schedulers
16WHERE status = 'VISIBLE ONLINE'
17  AND is_online = 1
18  AND parent_node_id < 64;   -- exclude the DAC node (node 64)
19
20SET @PhysicalCores    = @LogicalCPUs / NULLIF(@HyperthreadRatio, 0);
21SET @LogicalPerNUMA   = @LogicalCPUs / NULLIF(@NUMANodes, 0);
22SET @PhysicalPerNUMA  = @PhysicalCores / NULLIF(@NUMANodes, 0);
23
24-- Microsoft MAXDOP recommendation logic
25IF @NUMANodes = 1
26BEGIN
27    -- Single NUMA: use logical CPUs up to 8
28    SET @MaxDOPRecommended = CASE WHEN @LogicalCPUs < 8
29                                  THEN @LogicalCPUs
30                                  ELSE 8 END;
31END
32ELSE IF @HyperthreadRatio > 1
33BEGIN
34    -- Multiple NUMA with hyperthreading: use physical cores per NUMA, up to 8
35    SET @MaxDOPRecommended = CASE WHEN @PhysicalPerNUMA < 8
36                                  THEN @PhysicalPerNUMA
37                                  ELSE 8 END;
38END
39ELSE
40BEGIN
41    -- Multiple NUMA without hyperthreading: use logical per NUMA, up to 8
42    SET @MaxDOPRecommended = CASE WHEN @LogicalPerNUMA < 8
43                                  THEN @LogicalPerNUMA
44                                  ELSE 8 END;
45END
46
47SELECT
48    @@SERVERNAME                                            AS server_name,
49    @LogicalCPUs                                            AS logical_cpus_total,
50    @PhysicalCores                                          AS physical_cores_total,
51    @NUMANodes                                              AS numa_nodes,
52    @LogicalPerNUMA                                         AS logical_cpus_per_numa,
53    @PhysicalPerNUMA                                        AS physical_cores_per_numa,
54    CASE WHEN @HyperthreadRatio > 1
55         THEN 'Yes (' + CAST(@HyperthreadRatio AS VARCHAR) + ':1)'
56         ELSE 'No'
57    END                                                     AS hyperthreading,
58    @MaxDOPRecommended                                      AS maxdop_recommended,
59    (SELECT CAST(value_in_use AS INT)
60     FROM sys.configurations
61     WHERE name = N'max degree of parallelism')             AS maxdop_current,
62    CASE
63        WHEN (SELECT CAST(value_in_use AS INT)
64              FROM sys.configurations
65              WHERE name = N'max degree of parallelism')
66              = @MaxDOPRecommended
67        THEN 'OK — matches recommendation'
68        WHEN (SELECT CAST(value_in_use AS INT)
69              FROM sys.configurations
70              WHERE name = N'max degree of parallelism')
71              = 0
72        THEN 'WARNING — unlimited parallelism (MAXDOP 0)'
73        ELSE 'REVIEW — current setting differs from recommendation'
74    END                                                     AS status;

The script reads cpu_count and hyperthread_ratio from sys.dm_os_sys_info. When hyperthreading is enabled, cpu_count is the logical CPU count (physical cores × 2) and hyperthread_ratio is the multiplier (typically 2). Dividing cpu_count by hyperthread_ratio gives physical core count.

NUMA node count comes from sys.dm_os_schedulers, which shows one row per online scheduler. Filtering parent_node_id < 64 excludes node 64, which SQL Server reserves for the Dedicated Admin Connection (DAC) and should not be counted in the topology calculation.

The three-branch decision tree mirrors Microsoft's documented guidance. The final SELECT adds a status column that compares the calculated recommendation to the live value_in_use from sys.configurations.

 1-- Review the calculated recommendation from Script 1 before running this
 2EXEC sys.sp_configure N'max degree of parallelism', 8;  -- replace 8 with your recommended value
 3RECONFIGURE WITH OVERRIDE;
 4GO
 5
 6-- Verify the change
 7SELECT
 8    name,
 9    value,
10    value_in_use,
11    description
12FROM sys.configurations
13WHERE name = N'max degree of parallelism';

sp_configure applies the change to the running instance immediately after RECONFIGURE WITH OVERRIDE. No SQL Server restart is required. Replace 8 with the output of @MaxDOPRecommended from Script 1 before executing.

Key Benefits and Use Cases

  • Calculates the correct MAXDOP value automatically without manual CPU counting
  • Surfaces the gap between the current setting and the Microsoft recommendation
  • Works on any SQL Server version from 2008 onward
  • Useful for new instance configuration, post-migration audits, and periodic health checks
  • Covers single-NUMA, multi-NUMA, and hyperthreaded topologies correctly

Performance Considerations

  • MAXDOP is a starting point, not a final answer: Microsoft's formula gives a sensible default, but heavily OLTP-loaded servers often benefit from testing with MAXDOP 4 or lower. Analytical/reporting workloads may benefit from a higher value.
  • Cost threshold for parallelism: MAXDOP works alongside the cost threshold for parallelism setting. Raising the cost threshold (default: 5) to 25–50 prevents simple low-cost queries from going parallel, which is often a higher-value change than MAXDOP alone.
  • Database-scoped configuration: SQL Server 2016 and later supports per-database MAXDOP via ALTER DATABASE SCOPED CONFIGURATION. You can set a lower MAXDOP for a high-concurrency OLTP database while leaving a higher value for a reporting database on the same instance.
  • Availability Groups: apply the same MAXDOP calculation independently on each AG node. Each node may have a different physical CPU count if AG nodes are heterogeneous VMs.

Practical Tips

To check MAXDOP across multiple instances from a Central Management Server:

1-- Run against all registered servers via CMS multi-server query
2SELECT
3    @@SERVERNAME                AS server_name,
4    name,
5    value_in_use                AS maxdop_current
6FROM sys.configurations
7WHERE name = N'max degree of parallelism';

To also set the cost threshold for parallelism alongside MAXDOP:

1-- Set cost threshold for parallelism (recommended: 25-50 for OLTP)
2EXEC sys.sp_configure N'cost threshold for parallelism', 50;
3RECONFIGURE WITH OVERRIDE;
4GO

After changing MAXDOP, monitor sys.dm_exec_query_stats for query CPU time changes over the following week. Look for queries where total_worker_time / execution_count increased significantly — those may need query-level hints or plan guides if the new MAXDOP is too restrictive for them.

Conclusion

MAXDOP is one of the first settings to review on any new or inherited SQL Server instance. This script removes the manual arithmetic by reading the CPU topology directly from system DMVs and applying Microsoft's documented recommendation logic automatically. Use the output as a starting point, then tune further based on observed workload behavior.

References

Posts in this series