Setting MAXDOP and Cost Threshold for Parallelism

Two instance settings have an outsized effect on how SQL Server uses your CPUs: max degree of parallelism (MAXDOP) and cost threshold for parallelism. Both ship with defaults that were chosen many years ago for very different hardware. MAXDOP defaults to 0 (use all schedulers), and the cost threshold defaults to 5, meaning any query the optimiser estimates at more than five "cost units" is eligible to go parallel. On a modern server with 16 or more cores, that combination means trivial queries spin up 16 threads, and you see CXPACKET waits dominating everything.

Every time I take over a new instance, these are the first two things I check. Below is what I set, how to set it at instance and database level on SQL Server 2016, and how to tell whether it helped. You need sysadmin or ALTER SETTINGS, and you should know how many sockets, NUMA nodes and logical processors the instance sees.

Look at the NUMA layout

SELECT cpu_count, hyperthread_ratio, softnuma_configuration_desc
FROM sys.dm_os_sys_info;

SELECT parent_node_id, COUNT(*) AS schedulers
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
GROUP BY parent_node_id;

The second query shows how many schedulers each NUMA node has. Microsoft's guidance in KB 2806535 is: with a single NUMA node and 8 or fewer logical processors, MAXDOP = number of processors; with more than 8, MAXDOP = 8; with multiple NUMA nodes, MAXDOP = number of processors per node, capped at 8. A server with two sockets of 12 cores each would get MAXDOP 8.

Current values

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism';
EXEC sp_configure 'cost threshold for parallelism';

The run_value column is what is in effect. If both are 0 and 5, you have the defaults.

Setting the instance-wide values

EXEC sp_configure 'max degree of parallelism', 8;
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

Both take effect immediately without a restart; existing cached plans that were compiled with a different MAXDOP are invalidated, so expect a brief burst of compilations.

Why 50 for the cost threshold? There is no formula. The cost unit is an abstract number from the optimiser calibrated on hardware from the 1990s. In my experience, 50 is a reasonable starting point for OLTP workloads: it keeps short lookups and small joins serial while still allowing genuinely heavy reports to use several cores. Data-warehouse instances often work better at 25 or lower; pure OLTP sometimes goes higher.

Per-database override

Since SQL Server 2016 you can set MAXDOP per database with a database scoped configuration, which is useful when one database hosts a reporting workload and another hosts a chatty application:

USE ReportingDB;
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;

A value of 0 here means "inherit from the instance". Check it with:

SELECT name, value FROM sys.database_scoped_configurations WHERE name = 'MAXDOP';

There is no database-scoped cost threshold; that remains instance-wide.

Query hints for the exceptions

Individual queries can still override with OPTION (MAXDOP 1) or OPTION (MAXDOP 16). I use MAXDOP 1 on procedures that update a few rows in a tight loop, and higher values on the one nightly aggregation that really benefits. Index rebuilds accept WITH (MAXDOP = 8) directly and are the most common place where a higher-than-instance value pays off.

Did it help?

Note the wait-stats baseline (or clear it), then compare after a day:

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('CXPACKET', 'SOS_SCHEDULER_YIELD', 'THREADPOOL')
ORDER BY wait_time_ms DESC;

Falling CXPACKET as a share of total waits, with no rise in overall query duration, is the goal. Don't chase CXPACKET to zero. Some of it is normal for parallel plans; it just means the coordinator thread waited for workers.

You can also see which plans went parallel after the change by looking for Parallelism operators in the plan cache:

SELECT TOP 20 qs.total_worker_time / qs.execution_count AS avg_cpu,
       qs.total_elapsed_time / qs.execution_count AS avg_elapsed,
       SUBSTRING(st.text, 1, 200) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qp.query_plan.exist('//*[local-name()="Parallelism"]') = 1
ORDER BY avg_cpu DESC;

If it doesn't behave

Everything went parallel anyway. Check SELECT * FROM sys.resource_governor_workload_groups;; the default workload group has its own max_dop of 0 but a custom one may cap or override it. Also check the database-scoped setting, which wins over the instance value.

"Msg 5808: Ad hoc update to system catalogs is not supported." You tried to update sys.configurations directly. Use sp_configure plus RECONFIGURE.

Performance got worse after MAXDOP 1. Someone set MAXDOP to 1 instance-wide because a SharePoint or vendor document said so. That recommendation applies to that one product's database, not the whole instance. Use a database scoped configuration for that database instead.

THREADPOOL waits appear. Very high MAXDOP with many concurrent parallel queries exhausts worker threads. Lower MAXDOP or raise the cost threshold; do not raise max worker threads as a first response.

Honestly, these two changes are the best five minutes of configuration work on almost any new instance.