Home / Databases / SQL Server

DatabasesHow-toIntermediate

Optimization: tuning MAXDOP and cost threshold for parallelism on SQL Server

Default parallelism settings let small queries go parallel and big queries take every core, causing CXPACKET/CXCONSUMER waits and unpredictable performance. Set MAXDOP from the NUMA layout, raise cost threshold for parallelism from the default, use database-scoped and query-level overrides where needed, and confirm with wait stats.

Deepak Bhardwaj2 min read

Applies toSQL Server 2016 and later, Azure SQL Managed Instance; Azure SQL Database uses database-scoped MAXDOP

Defaults and why they hurt

Setting Old default Problem
max degree of parallelism 0 (all cores) on older versions One query can use every core
cost threshold for parallelism 5 Many cheap queries go parallel unnecessarily

SQL Server 2019+ setup suggests a MAXDOP based on cores, but cost threshold stays 5.

Server layout MAXDOP starting point
Single NUMA node, ≤ 8 logical processors ≤ number of logical processors
Single NUMA node, > 8 logical processors 8
Multiple NUMA nodes, ≤ 16 logical processors per node ≤ logical processors per node
Multiple NUMA nodes, > 16 per node Half of per-node processors, max 16

Check current Microsoft guidance for your version. For cost threshold, a common starting point is 30–50, then adjust by evidence.

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

Evidence before and after

-- Parallelism-related waits since restart (or snapshot and diff)
SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('CXPACKET','CXCONSUMER','CXSYNC_PORT','CXSYNC_CONSUMER','THREADPOOL')
ORDER BY wait_time_ms DESC;

-- Plan cost distribution of cached plans that went parallel
SELECT TOP 50 qs.execution_count, qs.total_worker_time / qs.execution_count AS avg_cpu,
       qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
         (//p:StmtSimple/@StatementSubTreeCost)[1]', 'float') AS est_cost
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qp.query_plan.exist('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
         //p:RelOp[@Parallel="1"]') = 1
ORDER BY qs.execution_count DESC;

Many frequently executed parallel plans with low estimated cost suggest raising the cost threshold.

Finer control

Level How
Database ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
Workload Resource Governor workload groups with MAX_DOP (Enterprise)
Query OPTION (MAXDOP 1) for specific OLTP queries; prefer fixing the plan

Watch for side effects

  • THREADPOOL waits mean worker threads ran out; high parallelism plus many sessions can cause this.
  • Reports and maintenance (index rebuilds, CHECKDB) may need their own MAXDOP settings.

Tip

Change one setting at a time and compare a week of wait stats and Query Store top-CPU queries. Parallelism tuning is workload-specific; numbers from blog posts are starting points only.

Checklist

  • NUMA layout and core counts checked
  • MAXDOP set per guidance
  • Cost threshold raised from 5 and validated
  • Wait stats compared before/after
  • Database/workload/query overrides used only where needed

Related fixes