Quick Scan Report – Max Degree Of Parallelism

What this check looks for

sys.configurations where max degree of parallelism has a value_in_use of 0, on a server with more than one processor. The message includes the core count, so the number you need is in front of you.

This page also covers issue 54, Max degree of parallelism set to 1, which is the opposite mistake: parallelism switched off entirely. Both are reported against this page because the decision is the same one, made in different directions.

Why it matters

0 means “use everything”. On a server with 48 processors, one query can take all 48.

When SQL Server decides a query is expensive enough to parallelize, it splits the work across threads. With MAXDOP at 0 there is no ceiling on how many, so a single large report query on a busy OLTP instance can occupy every scheduler, and everything else queues behind it. The symptoms are:

• High CXPACKET or CXCONSUMER waits, often the top wait on the instance.
• Erratic performance. The same query takes two seconds when the server is quiet and two minutes when another parallel query is running.
• Worker thread pressure, because each parallel branch consumes a worker, and a handful of wide parallel queries can exhaust the pool. That has its own check, and it is how an instance stops accepting connections.

Parallelism is not free even when it helps. Splitting the work, coordinating the threads and recombining the results all cost, and for a query that returns 50 rows the coordination costs more than the work. That is what the cost threshold setting is for, and the two settings work together: the cost threshold decides which queries go parallel, MAXDOP decides how wide they go. Setting one without the other is half the job.

The opposite mistake, MAXDOP 1, disables parallelism entirely. That is right for a small number of workloads, notably SharePoint and Microsoft Dynamics, which Microsoft explicitly requires it for. It is wrong for a data warehouse or any reporting workload, where large queries genuinely benefit, and it is often found on servers where somebody set it to stop CXPACKET waits without addressing why the queries were going parallel in the first place.

How to confirm it yourself

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

The hardware the setting should be based on:

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

The NUMA node layout, which is the real constraint:

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];

And whether parallelism is actually costing you:

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

CXPACKET on its own is not a problem, and this is the most common misreading of wait statistics. It appears whenever a parallel query runs, including when parallelism is working well. From SQL Server 2017, CXPACKET and CXCONSUMER are separated so that the benign coordinator waits are distinguished from the ones that indicate genuine skew.

How to fix it

Set it to the smaller of eight and the number of logical processors in one NUMA node. The change is dynamic and takes effect immediately, with no restart:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism', 8;
RECONFIGURE;

The Quick Scan offers this directly. Right click the finding and choose to set it to 4, to 8, or to match the core count capped at 8.

Set the cost threshold at the same time. The default of 5 is from hardware that no longer exists and means almost every query is considered for a parallel plan:

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

A value between 25 and 50 is a far better starting point. Raising the cost threshold usually does more for CXPACKET waits than lowering MAXDOP does, because it stops trivial queries going parallel at all rather than making every parallel query narrower.

Override per database where a workload genuinely differs:

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;

That is the right way to give a reporting database wider parallelism than an OLTP instance, rather than compromising at the server level.

And per query where one statement needs an exception:

SELECT ... OPTION (MAXDOP 1);

Measure before and after. DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) resets the wait statistics so the comparison starts from a known point.

How long it takes

About two hours, nearly all of it choosing the number and watching the effect. The change itself is one statement.

Related reports

Related checks

Frequently asked questions

Is MAXDOP 1 ever right? For SharePoint and Microsoft Dynamics, yes, and they document it. Otherwise it disables a useful capability, and it is usually a workaround for a cost threshold that was never raised.

We have 4 cores. Does this matter? Much less. The check skips single processor servers, and on a small machine 0 and 4 amount to nearly the same thing. Setting it explicitly is still worth doing so it is a decision.

Does hyper-threading count? Yes. The counts here are logical processors, so a node with 8 physical cores and hyper-threading shows 16.

Do I need a restart? No. Both settings are dynamic.