Max degree of parallelism is higher than a NUMA node has cores

What this check looks for

The configured max degree of parallelism from sys.configurations, compared against the smallest NUMA node on the machine, counted from sys.dm_os_schedulers.

The count deliberately excludes schedulers whose parent_node_id is 64 or above. That is the hidden scheduler node used by the dedicated admin connection, and counting it makes every server look as though it has a one core node.

MAXDOP 0 and MAXDOP 1 are not reported here. They are separate checks, because they mean different things: 0 is “use everything” and 1 is “never go parallel”.

The counts here are logical processors, so on a machine with hyper-threading enabled these numbers are double the physical core count.

Why it matters

A parallel query whose degree exceeds one NUMA node has to reach across to another node’s memory on every exchange.

On a NUMA machine, each node has its own memory attached to it. Access to the memory attached to your own node is fast; access to another node’s memory goes across the interconnect and is measurably slower. SQL Server is NUMA aware and tries to keep a query’s threads and its memory together, which works right up until the query needs more threads than the node has.

Past that point the threads are spread across nodes, and every exchange operator in the plan, every repartition and every gather, moves rows between them. A query can spend more time moving rows between nodes than doing the work.

The symptom is high CXPACKET waits with no obvious cause. The queries look reasonable, the indexes look reasonable, the hardware looks generous, and the waits say parallelism. That sends people to the query, which is not where the problem is.

The general guidance is a maximum of eight, and never more than the cores in one NUMA node. Those two rules together are what this check enforces, and on modern hardware with large core counts they very often disagree with whatever was set during installation.

How to confirm it yourself

The node layout, with the DAC node excluded:

SELECT [parent_node_id],
       COUNT(*) AS [logical_processors]
  FROM sys.dm_os_schedulers WITH (NOLOCK)
 WHERE [status] = 'VISIBLE ONLINE'
   AND [parent_node_id] < 64
 GROUP BY [parent_node_id]
 ORDER BY [parent_node_id];

The current setting:

SELECT [name], [value], [value_in_use], [description]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] IN ('max degree of parallelism', 'cost threshold for parallelism');

And what the hardware actually is:

SELECT [cpu_count], [hyperthread_ratio], [socket_count],
       [cores_per_socket], [numa_node_count]
  FROM sys.dm_os_sys_info WITH (NOLOCK);

Whether parallelism is actually a problem here:

SELECT [wait_type], [waiting_tasks_count],
       [wait_time_ms] / 1000 AS [wait_time_seconds]
  FROM sys.dm_os_wait_stats WITH (NOLOCK)
 WHERE [wait_type] IN ('CXPACKET', 'CXCONSUMER', 'LATCH_EX')
 ORDER BY [wait_time_ms] DESC;

How to fix it

Set MAXDOP to the smaller of eight and the number of logical processors in one NUMA node. The change takes effect immediately and needs no restart:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism', 8;   -- or your node size, if smaller
RECONFIGURE;

Set the cost threshold at the same time. The default of 5 dates from hardware that no longer exists, and it means almost every query is considered for a parallel plan. A value between 25 and 50 is a far more useful starting point, and the two settings work together: the cost threshold decides which queries go parallel, MAXDOP decides how wide they go.

EXEC sp_configure 'cost threshold for parallelism', 30;
RECONFIGURE;

Where a single database needs something different, set it at the database level rather than moving the instance:

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;

Data warehouse workloads legitimately want wider parallelism than an OLTP instance. That is a reason to override for a database, not a reason to leave the instance setting high.

Measure before and after. sys.dm_os_wait_stats can be cleared with DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) so the comparison starts from a known point.

How long it takes

About an hour and a half, nearly all of which is deciding the right number and watching the effect. The change itself is one statement.

Report Why you would go there
Parallelism Calibration What MAXDOP and cost threshold should be for this instance.
Configuration Values Every setting against its default, including these two.
Waits Whether CXPACKET really is significant here.
CPU by Query The queries going parallel and what it costs.
SQL CPU Schedulers The scheduler and node layout in the product rather than a query.
Server Overview The hardware this instance is running on.
Check
Max degree of parallelism MAXDOP left at 0, meaning use every core.
Cost threshold for parallelism The other half of the parallelism configuration.
Not using all cores Cores the instance cannot see at all.
Memory grants are queuing Parallel plans multiply the memory grant across threads.

Frequently asked questions

Our server has one NUMA node. Why does this fire? Then it fired on the guidance of eight rather than on the node size. On a single node machine with more than eight logical processors, eight is still the usual starting point.

Is hyper-threading counted? Yes. sys.dm_os_schedulers counts logical processors, so a node with 8 physical cores and hyper-threading shows 16. Setting MAXDOP to 16 there is not the same as using 16 cores.

What about soft NUMA? SQL Server 2016 and later create soft NUMA nodes automatically above 8 physical cores per socket, which changes the node layout this check reads. The query above shows the layout SQL Server is actually using, which is the one that matters.

We set MAXDOP high on purpose for reporting. Then set it at the database level for that database and leave the instance at a sensible default. That is what database scoped configuration is for.