SQL Server MAXDOP Recommendation Script

calendar Apr 17, 2026 / · 8 min read · sql server maxdop performance sp_configure numa sql scripts database administration cpu configuration sys.configurations sys.dm_os_schedulers sys.dm_os_sys_info  ·
Share on: linkedin copy

MAXDOP (max degree of parallelism) caps how many processors SQL Server uses for a single parallel query plan. The default value of 0 lets SQL Server use all available processors, up to 64, which Microsoft says is not the recommended value for most cases. This script reads the CPU and NUMA layout from system DMVs, applies Microsoft's SQL Server 2016 and later guidance, and compares the result with the current setting.

Purpose and Overview

Microsoft's MAXDOP guidance changed with SQL Server 2016. Through SQL Server 2014 the rule for multi-NUMA servers capped MAXDOP at 8; from 2016 onward the cap depends on the number of logical processors per NUMA node, and soft-NUMA (created automatically when a node has more than eight physical cores) counts as a NUMA node. A script that hard-codes the older rule gives the wrong answer on a modern server.

MAXDOP 0 on a large server lets one parallel query take every processor SQL Server can see, which starves other queries of CPU time. Setting it to 1 suppresses parallel plan generation entirely and slows queries that benefit from parallel execution.

For SQL Server 2016 and later, the Microsoft guidance is:

  • Single NUMA node, 8 or fewer logical processors: keep MAXDOP at or under the number of logical processors
  • Single NUMA node, more than 8 logical processors: keep MAXDOP at 8
  • Multiple NUMA nodes, 16 or fewer logical processors per node: keep MAXDOP at or under the number of logical processors per NUMA node
  • Multiple NUMA nodes, more than 16 logical processors per node: keep MAXDOP at half the number of logical processors per NUMA node, with a maximum of 16

The guidance counts logical processors and has no separate hyperthreading rule. This script reads the layout from sys.dm_os_sys_info and sys.dm_os_schedulers, calculates the upper bound, and compares it to the current sp_configure value.

Code Breakdown

Script 1: MAXDOP Recommendation Calculator

 1DECLARE @LogicalCPUs        INT,
 2        @NUMANodes          INT,
 3        @LogicalPerNUMA     INT,
 4        @MaxDOPRecommended  INT,
 5        @MaxDOPCurrent      INT;
 6
 7SELECT @LogicalCPUs = cpu_count
 8FROM sys.dm_os_sys_info;
 9
10SELECT @NUMANodes = COUNT(DISTINCT parent_node_id)
11FROM sys.dm_os_schedulers
12WHERE status = 'VISIBLE ONLINE'
13  AND is_online = 1;   -- user schedulers only; the DAC scheduler has its own status
14
15SET @LogicalPerNUMA = @LogicalCPUs / NULLIF(@NUMANodes, 0);
16
17IF @LogicalCPUs IS NULL OR ISNULL(@NUMANodes, 0) = 0
18BEGIN
19    RAISERROR('Could not read sys.dm_os_sys_info or sys.dm_os_schedulers. This script needs VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later).', 16, 1);
20    RETURN;
21END
22
23SELECT @MaxDOPCurrent = CAST(value_in_use AS INT)
24FROM sys.configurations
25WHERE name = N'max degree of parallelism';
26
27-- Microsoft MAXDOP guidance for SQL Server 2016 and later
28IF @NUMANodes = 1
29BEGIN
30    -- Single NUMA node: logical processors, up to 8
31    SET @MaxDOPRecommended = CASE WHEN @LogicalCPUs < 8
32                                  THEN @LogicalCPUs
33                                  ELSE 8 END;
34END
35ELSE IF @LogicalPerNUMA <= 16
36BEGIN
37    -- Multiple NUMA nodes, 16 or fewer logical processors per node
38    SET @MaxDOPRecommended = @LogicalPerNUMA;
39END
40ELSE
41BEGIN
42    -- Multiple NUMA nodes, more than 16 logical processors per node:
43    -- half the logical processors per node, maximum 16
44    SET @MaxDOPRecommended = CASE WHEN @LogicalPerNUMA / 2 < 16
45                                  THEN @LogicalPerNUMA / 2
46                                  ELSE 16 END;
47END
48
49SELECT
50    @@SERVERNAME                                            AS server_name,
51    @LogicalCPUs                                            AS logical_cpus_total,
52    @NUMANodes                                              AS numa_nodes,
53    @LogicalPerNUMA                                         AS logical_cpus_per_numa,
54    @MaxDOPRecommended                                      AS maxdop_upper_bound,
55    @MaxDOPCurrent                                          AS maxdop_current,
56    CASE
57        WHEN @MaxDOPCurrent = 0
58        THEN 'WARNING - unlimited parallelism (MAXDOP 0)'
59        WHEN @MaxDOPCurrent = @MaxDOPRecommended
60        THEN 'OK - matches recommendation'
61        WHEN @MaxDOPCurrent < @MaxDOPRecommended
62        THEN 'OK - at or under the recommended upper bound'
63        ELSE 'REVIEW - current setting is above the recommendation'
64    END                                                     AS status;

sys.dm_os_sys_info.cpu_count is the number of logical CPUs on the system. The script divides it by the NUMA node count to get logical processors per node, which is the quantity Microsoft's table uses. It does not use hyperthread_ratio: that column is the ratio of logical or physical cores exposed by one physical processor package, not a hyperthreading multiplier, so dividing by it does not give a physical core count.

NUMA node count comes from sys.dm_os_schedulers, where parent_node_id is the NUMA node a scheduler belongs to. Filtering on status = 'VISIBLE ONLINE' keeps the schedulers that process user requests and drops the hidden schedulers and the dedicated administrator connection (DAC) scheduler, whose status is VISIBLE ONLINE (DAC). Because the guidance refers to soft-NUMA nodes where SQL Server has created them, the node count here reflects what SQL Server reports, not just the hardware.

The three branches follow Microsoft's 2016+ table. Because the guidance says to keep MAXDOP "at or under" the figure for most rows, the output column is an upper bound and the status column treats any non-zero value at or below it as acceptable. If the instance has CPU affinity configured, cpu_count still reports every logical CPU on the machine, so adjust the result by hand.

On SQL Server 2008 through 2014 the multi-node rows differ: with 8 or fewer logical processors per node, keep MAXDOP at or under that number, and with more than 8, keep it at 8. This script does not implement that older table.

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

max degree of parallelism is an advanced option, so show advanced options must be set to 1 before sp_configure can change it. The change takes effect immediately after RECONFIGURE, with no restart. Replace 8 with a value at or below the maxdop_upper_bound from Script 1 before executing. Microsoft's own example sets show advanced options back to 0 afterward, which you can do once the change is verified.

Key Benefits and Use Cases

  • Calculates the MAXDOP upper bound automatically without manual CPU counting
  • Surfaces the gap between the current setting and the Microsoft guidance
  • Useful for new instance configuration, post-migration audits, and periodic health checks
  • Handles single-NUMA and multi-NUMA layouts under the SQL Server 2016 and later rules
  • Flags MAXDOP 0 so an unlimited setting is easy to find across a fleet

Performance Considerations

  • MAXDOP is a starting point, not a final answer: the Microsoft figure is an upper bound. Heavily OLTP-loaded servers sometimes do better with a lower value after testing, and analytical or reporting workloads may do better near the bound.
  • Cost threshold for parallelism: MAXDOP works alongside the cost threshold for parallelism setting. Microsoft describes the default of 5 as "a starting point, not a recommendation" and notes that raising it can keep smaller OLTP queries on serial plans. Raise it in small increments and observe a full business cycle.
  • Overrides: Microsoft documents MAXDOP overrides at the query level (the MAXDOP query hint), the database level (ALTER DATABASE SCOPED CONFIGURATION, SQL Server 2016 and later), and the Resource Governor workload group level. A lower database-scoped MAXDOP suits a high-concurrency OLTP database that shares an instance with a reporting database.
  • Availability Groups: run the calculation on each replica, because replicas on different hardware can report different CPU and NUMA layouts.

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 raise the cost threshold for parallelism alongside MAXDOP:

1-- Example value only; raise in small increments and observe a full business cycle
2EXEC sys.sp_configure N'show advanced options', 1;
3RECONFIGURE;
4GO
5EXEC sys.sp_configure N'cost threshold for parallelism', 20;
6RECONFIGURE;
7GO

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 logical processor and NUMA layout from system DMVs and applying Microsoft's SQL Server 2016 and later guidance. Use the output as an upper bound, then tune further based on observed workload behavior.

References

Posts in this series