SQL Server MAXDOP and Cost Threshold for Parallelism: Recommended Settings

SQL Server can split a large query across several CPU cores. For the right query, that turns minutes into seconds. With the wrong settings, small queries also go parallel for no reason, cores wait on each other, CPU usage climbs and fewer queries can run at the same time. Two server settings govern parallelism: max degree of parallelism (MAXDOP) and cost threshold for parallelism (CTFP).

This guide explains what the two settings do, Microsoft’s MAXDOP recommendations, how to find a reasonable starting value for CTFP, what CXPACKET waits mean, and at which levels other than the server you can change these settings.

Two Settings, Two Questions

• Cost threshold for parallelism answers “is this query worth running in parallel?”. A parallel plan is considered for queries whose estimated cost, as calculated by the optimizer, is above this value. The default is 5.
• Max degree of parallelism answers “if it runs in parallel, how many cores can it use at most?”. The default is 0, meaning SQL Server can use all available processors (up to 64).

The two settings have to be considered together. If CTFP is too low, small queries that would finish in milliseconds on one core switch to parallel plans. If MAXDOP is too high, a single parallel query keeps every core on the server busy and other users wait.

MAXDOP: Microsoft’s Recommendations

For SQL Server 2016 and later, Microsoft’s recommendation aims to keep all the workers of a parallel query within the same NUMA node:

The NUMA node here means the soft-NUMA nodes SQL Server 2016 and later create automatically. From SQL Server 2019, setup recommends a MAXDOP value based on the processor count; on most installations, accepting it is a good start. In Azure SQL Database and Azure SQL Managed Instance, the default for new databases is already 8.

To see your server’s NUMA layout:

SELECT node_id, memory_node_id, online_scheduler_count, cpu_count
FROM sys.dm_os_nodes
WHERE node_state_desc = N'ONLINE';

Cost Threshold for Parallelism: Why the Default of 5 Is Too Low

CTFP’s default of 5 was set for hardware from decades ago, and on today’s servers it makes many small queries go parallel needlessly. The value isn’t seconds; it’s the optimizer’s own cost unit.

Microsoft doesn’t recommend a single value. A common approach in the field is to start somewhere between 30 and 50 and then tune for the workload. For a better-informed start, look at the cost distribution of parallel plans in the plan cache:

-- Estimated cost distribution of parallel plans in the plan cache
WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (50)
       n.value(N'@StatementSubTreeCost', N'float') AS estimated_cost,
       qs.execution_count,
       qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,
       SUBSTRING(st.text, 1, 200) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY qp.query_plan.nodes(N'//StmtSimple') AS s(n)
WHERE qp.query_plan.exist(N'//RelOp[@Parallel = "1"]') = 1
ORDER BY qs.execution_count DESC;

Queries that run very often, have a low cost and use a parallel plan are a sign that CTFP should go up. Because this query scans the plan cache, run it at a quiet time on large, busy servers.

Changing the Settings

EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;

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

SELECT name, value_in_use
FROM sys.configurations
WHERE name IN (N'max degree of parallelism', N'cost threshold for parallelism');

Neither setting needs a restart; both take effect immediately. The change does clear the plan cache, though, so queries recompile for a while. Make the change outside peak hours and watch CPU and wait statistics afterwards.

CXPACKET and CXCONSUMER Waits

When a parallel query runs, threads wait on each other, and these waits show up as CXPACKET and CXCONSUMER. Their presence alone isn’t a problem; it’s a natural consequence of parallelism. CXCONSUMER is usually harmless. The situation to watch is CXPACKET making up a large share of total waits together with high CPU and low concurrency; that usually points to unevenly distributed work, missing indexes or out-of-date statistics.

Levels Other Than the Server

MAXDOP isn’t only set at server level; narrower settings override the server setting:

• Database level: ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4; Useful when OLTP and reporting databases on the same server have different needs.
• Query level: the OPTION (MAXDOP 1) query hint, or Query Store hints without changing the code.
• Workload level: the MAX_DOP option of Resource Governor workload groups; with SQL Server 2025, available in Standard edition too.
• Index operations: ALTER INDEX ... REBUILD WITH (MAXDOP = 4); the value applies to that operation only.

Parallelism Checklist

• MAXDOP matches Microsoft’s recommendations for the NUMA layout; it isn’t left at 0.
• CTFP is raised from the default 5 and validated against plan cache costs.
• MAXDOP 1 isn’t used server-wide; database or query level settings are used where needed.
• CPU and wait statistics were compared before and after the change.
• Index and statistics state was reviewed for queries with high CXPACKET.