Cost Threshold for Parallelism and MAXDOP: Setting Both Together

Parallelism tuning involves both the decision to consider parallel work and the width of that work. Review cost threshold for parallelism together with MAXDOP so one setting does not hide the consequences of the other.

Give Cost Threshold for Parallelism and MAXDOP Separate Jobs

The threshold influences when the optimizer considers a parallel plan using an estimated serial-plan cost. It is not a duration limit and does not mean that a query has run for that many seconds. The estimate uses optimizer costing rules, so the same numeric threshold needs interpretation through the actual workload.

MAXDOP limits the degree of parallelism for relevant parallel execution work. It does not guarantee that every eligible query uses that degree. It is also not a simple cap on all workers a request can own across every execution branch. A query can execute serially despite permission to go parallel.

I inspect both values when investigating excessive small parallel requests or expensive serial requests. Setting the width to one removes ordinary parallel query execution, so raising the threshold at the same time cannot demonstrate which adjustment solved the problem. Treat each proposed change as a specific experiment with a stated workload objective.

Read the Active Configuration

Inspect both configured and active values. Retain them with the baseline evidence before changing anything. Also inspect database-scoped MAXDOP where the application runs, because an instance-level review alone can miss a database override. Record any relevant query hints or workload-group limits as separate controls.

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

A difference between a setting's stored and active values needs interpretation before experimentation. Do not assume a copied configuration report represents today's state. Include engine build, database compatibility level, and capture time in the working evidence, since compilation behavior depends on more than these two numbers.

Treat the Default Cost Threshold for Parallelism as a Starting Point

The historical default of five is low for many modern transactional workloads. It is a starting value rather than a universal recommendation. If many CPU-light statements acquire parallel plans, a reviewed increase can keep more small requests serial and leave workers available for work that benefits from parallel execution.

Use cost threshold for parallelism as one part of that workload decision. Do not replace five with another supposedly universal number and declare the server tuned. Raise it in controlled increments and observe a representative business cycle. Include reporting and maintenance periods if those share the instance.

Parallel plans can appear with displayed costs below the threshold because the decision uses an earlier optimization estimate. A final plan's displayed cost therefore does not always explain the decision by itself. Review the actual compilation and workload pattern instead of treating one below-threshold plan as proof that the setting is ignored.

Choose Width From the Visible NUMA Layout

Check the scheduler population SQL Server can actually use. The following view groups visible online schedulers by their parent node. Affinity, virtualization, and soft-NUMA can make the usable layout differ from a simple hardware brochure count. Interpret the groups alongside the accepted server topology.

SELECT parent_node_id,COUNT(*) AS VisibleOnlineSchedulers
FROM sys.dm_os_schedulers
WHERE status=N'VISIBLE ONLINE'
GROUP BY parent_node_id
ORDER BY parent_node_id;
SELECT node_id,node_state_desc,online_scheduler_count
FROM sys.dm_os_nodes
WHERE node_state_desc LIKE N'ONLINE%' AND node_id<64;

From SQL Server 2016 on, a single NUMA node with up to eight logical processors should keep the degree at or below that count. Above eight processors on one node, eight is the recommended starting boundary. With multiple nodes and up to sixteen logical processors per node, keep it at or below the per-node count. Above sixteen per node, use half the per-node count, capped at sixteen, as the documented starting guidance.

The node guidance includes soft-NUMA where configured. It is a starting framework to test, not a promise that the widest permitted degree performs best. Retain capacity for concurrency and compare the application's latency and CPU consumption before accepting the value.

Apply a Reviewed Pair of Example Values

This example changes the instance degree to four and the threshold to twenty-five. Those are demonstration inputs, not a recommendation for every server. Run it only after the topology and workload review accepts an experiment, and retain the printed previous values for the reversal plan.

DECLARE @OldAdvanced int,@OldDegree int,@OldThreshold int;
SELECT @OldAdvanced=CONVERT(int,value_in_use)
FROM sys.configurations WHERE name=N'show advanced options';
SELECT @OldDegree=CONVERT(int,value_in_use)
FROM sys.configurations WHERE name=N'max degree of parallelism';
SELECT @OldThreshold=CONVERT(int,value_in_use)
FROM sys.configurations WHERE name=N'cost threshold for parallelism';
SELECT @OldDegree AS PreviousDegree,@OldThreshold AS PreviousThreshold;
IF @OldAdvanced=0
BEGIN
    EXEC sys.sp_configure N'show advanced options',1;
    RECONFIGURE;
END;
EXEC sys.sp_configure N'max degree of parallelism',4;
EXEC sys.sp_configure N'cost threshold for parallelism',25;
RECONFIGURE;
IF @OldAdvanced=0
BEGIN
    EXEC sys.sp_configure N'show advanced options',0;
    RECONFIGURE;
END;

Server settings are cooperative: they accept a number without asking whether it was a good idea. Changing these settings requires appropriate ALTER SETTINGS permission. Reconfiguration does not rewrite every cached plan instantly. Observe naturally compiled representative work or a reviewed targeted recompilation plan. Do not clear the entire plan cache simply to make the experiment's clock start neatly.

Understand Database and Query Overrides

Database-scoped MAXDOP is available from SQL Server 2016 and can give one database a specific degree without changing every database. A value of zero uses the instance setting. A query MAXDOP hint and workload-group limits also affect the effective policy, so record their interaction before interpreting an apparently unexpected degree.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP=4;
SELECT name,value FROM sys.database_scoped_configurations WHERE name=N'MAXDOP';

Use this only as an alternative approved database-specific experiment, rather than stacking every example change automatically. Keep the old scoped value for restoration. The narrowest appropriate control can make the evidence easier to interpret, but it still needs representative workload validation.

Watch Parallelism Waits in an Interval

Capture a baseline and calculate later deltas instead of ranking lifetime waits after a change. CXPACKET and CXCONSUMER provide parallel-execution context; neither means that every parallel plan is defective. Correlate them with CPU pressure, worker availability, execution plans, and the user operation's duration.

SELECT wait_type,waiting_tasks_count,wait_time_ms
INTO #ParallelWaitBefore
FROM sys.dm_os_wait_stats
WHERE wait_type IN (N'CXPACKET',N'CXCONSUMER',N'THREADPOOL',N'SOS_SCHEDULER_YIELD');

Run the next statement after an accepted observation interval without resetting counters. Reject comparisons across a restart or a known wait-stat reset, even if the new values have already caught up numerically. The decreasing-counter filter below removes obvious resets but cannot identify every reset independently.

SELECT a.wait_type,
       b.waiting_tasks_count-a.waiting_tasks_count AS WaitCountDelta,
       b.wait_time_ms-a.wait_time_ms AS WaitTimeDeltaMS
FROM #ParallelWaitBefore AS a
JOIN sys.dm_os_wait_stats AS b ON b.wait_type=a.wait_type
WHERE b.waiting_tasks_count>=a.waiting_tasks_count
  AND b.wait_time_ms>=a.wait_time_ms;

I compare both heavy queries and concurrent small requests before accepting a pair of values. Which part of the workload improved, and which part paid for that improvement? A lower parallelism wait total can accompany slower serial queries, so the wait report alone cannot answer that question.

Keep the New Cost Threshold for Parallelism After Verification

Retain the new cost threshold for parallelism and degree only when the representative cycle meets the accepted latency, throughput, and resource objectives. Check regressions in reports and scheduled operations, and keep a tested route back to the prior values. Changing a setting is quick; establishing that it helped requires evidence.

Avoid treating one successful query as estate-wide validation. The useful outcome is an appropriate balance between useful parallel work and available concurrency, with the exact configuration and observation interval recorded.

Related reading on this blog: SQL SERVER Performance: When MAXDOP = 1 Slowed Down the Entire Business and Do Queries Always Respect Cost Threshold of Parallelism? Interview Question of the Week #216.

Parallelism tuning is not choosing one magic number, it is balancing eligibility and execution width against the measured workload.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.