Should You Change the Cost Threshold for Parallelism?

The Cost Threshold for Parallelism is a SQL Server instance-level setting that controls when the optimizer considers using a parallel execution plan. When a query’s estimated cost exceeds the threshold, SQL Server evaluates whether to split the query across multiple CPU threads. The default value of 5 has not changed since SQL Server was built for hardware from the late 1990s. On modern multi-core servers it is almost universally too low.

1 Why the Default of 5 Is a Problem Beginner

A cost threshold of 5 means that any query with an optimizer-estimated cost above 5 is eligible for a parallel plan. On modern hardware with fast CPUs and large amounts of RAM, the optimizer regularly produces cost estimates above 5 for queries that are actually simple and fast. A small index scan, a narrow join against a lookup table, or a simple aggregation on a modest dataset can all trigger parallel execution at a threshold of 5.

The problem with unnecessary parallelism is not that parallel plans are always wrong. For large scans, hash joins over millions of rows, and complex aggregations, parallelism genuinely helps. The problem is that parallelism on simple queries adds overhead: worker threads are allocated and synchronized, CXPACKET and CXCONSUMER waits accumulate, and CPU context switching increases. The overhead often exceeds the benefit when the query could have run in a fraction of a second on a single thread.

The result is a server that is busier than it needs to be, with CPU utilization that looks high but is not doing proportionally more useful work, and wait statistics dominated by parallelism-related waits on queries that do not benefit from it.

2 What the Community Recommends Beginner

The SQL Server community has debated this setting extensively. The consensus has shifted over time as hardware has evolved. The current guidance from well-regarded SQL Server experts is:

3 Check and Update the Setting Beginner

-- Check the current setting
EXEC sp_configure 'cost threshold for parallelism';
-- value_in_use = 5 means still on the default

-- Check both MAXDOP and cost threshold together
SELECT name, value_in_use, description
FROM sys.configurations
WHERE name IN ('cost threshold for parallelism', 'max degree of parallelism')
ORDER BY name;

-- Update the cost threshold
-- Start at 50 for OLTP workloads, adjust based on monitoring
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

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

-- Verify the change took effect immediately (no restart required)
SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'cost threshold for parallelism';

4 Identify Plans Affected by the Current Setting Intermediate

These queries read the plan cache to show which queries are currently using parallel plans and which are not. Running them before and after the threshold change shows the impact.

-- Count single-threaded vs parallel plans in cache
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    'SingleThreaded'                AS PlanType,
    COUNT(*)                        AS PlanCount
FROM sys.dm_exec_cached_plans       AS ecp
CROSS APPLY sys.dm_exec_query_plan(ecp.plan_handle) AS eqp
CROSS APPLY eqp.query_plan.nodes(
    '/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 0

UNION ALL

SELECT
    'Parallel'                      AS PlanType,
    COUNT(*)                        AS PlanCount
FROM sys.dm_exec_cached_plans       AS ecp
CROSS APPLY sys.dm_exec_query_plan(ecp.plan_handle) AS eqp
CROSS APPLY eqp.query_plan.nodes(
    '/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1;

-- Detail: see parallel plans with cost and reuse count
-- Useful for identifying low-cost queries using parallelism unnecessarily
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP 25
    n.value('(@StatementText)[1]',         'VARCHAR(4000)')  AS StatementText,
    n.value('(@StatementOptmLevel)[1]',    'VARCHAR(25)')    AS OptimizationLevel,
    CAST(n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS DECIMAL(18,4)) AS SubTreeCost,
    ecp.usecounts,
    ecp.size_in_bytes / 1024                                  AS SizeKB
FROM sys.dm_exec_cached_plans                   AS ecp
CROSS APPLY sys.dm_exec_query_plan(ecp.plan_handle) AS eqp
CROSS APPLY eqp.query_plan.nodes(
    '/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1
ORDER BY SubTreeCost ASC;   -- low SubTreeCost + parallel plan = unnecessary parallelism

5 What to Monitor After Changing Beginner

After raising the cost threshold, monitor these three things over the following week to confirm the change helped without introducing new problems.

• CXPACKET and CXCONSUMER wait time. These should decrease. If they were dominated by low-cost queries going parallel, raising the threshold removes those cases. If they remain high, the remaining parallelism is coming from genuinely heavy queries where it may be appropriate.
• CPU utilization. Overall CPU should decrease on a server where unnecessary parallelism was the primary driver of high CPU. If CPU increases after the change, queries that previously benefited from parallelism may now be running serially when they should not be, and the threshold may need to come down slightly.
• Specific critical query runtimes. Check the queries most important to the business before and after. Use Query Store to compare plan shape and runtime statistics. A few important queries should be individually validated to confirm they are not negatively affected.

-- Compare parallelism wait stats before and after the change
-- Run this before changing the setting and save the output
SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('CXPACKET', 'CXCONSUMER', 'CXSYNC_PORT', 'SOS_SCHEDULER_YIELD')
ORDER BY wait_time_ms DESC;

References

• Microsoft Docs: Configure the Cost Threshold for Parallelism
• Microsoft Docs: Configure the max degree of parallelism
• Brent Ozar: Five SQL Server Settings to Change on Every Server
• SQLYARD: SQL Server MAXDOP Guide
• SQLYARD: SQL Server Wait Statistics Guide
• SQLYARD: Investigating Serial vs Parallel Executions with Query Store
• SQLYARD: When SQL Server Is Slow: Triage Playbook