Should You Change the Cost Threshold for Parallelism?

🎛️ Rethinking the Default: Why SQL Server’s Cost Threshold of 5 Might Be Hurting You

If you’re running SQL Server with the default cost threshold for parallelism still set to 5, it’s probably time to revisit that setting.

This value determines when SQL Server decides to use parallel execution plans. At a cost estimate of just 5, SQL Server may choose to split a query across multiple CPUs — even when the query isn’t complex enough to benefit from it.

---

⚠️ Why the Default Value Is a Problem

The default threshold of 5 is extremely low by modern standards. Many simple queries — like scans or small joins — end up generating parallel plans that:

• Increase CPU context switching
• Use more worker threads
• Add overhead without any real performance benefit

In other words: SQL Server is trying too hard, too soon.

---

✅ What the Experts Recommend

Performance tuning experts often suggest raising the cost threshold to somewhere between 20 and 25 — or even higher, depending on your workload.

This helps SQL Server reserve parallelism for truly heavy queries, where multiple threads can actually speed things up.

---

🔧 How to Check Your Current Setting

Run this to see what you’re using:

EXEC sp_configure 'cost threshold for parallelism';

If it returns 5, you're still on the default.

🔁 How to Update It

You can safely raise the threshold using this command:

EXEC sp_configure 'show advanced options', 1;  
RECONFIGURE;

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

✅ Note: You can adjust the value to suit your workload — 20 to 40 is a common range to experiment with.

📊 What to Watch For

After updating the threshold:

• Monitor query performance
• Check the plan cache for fewer unnecessary parallel plans
• Keep an eye on CPU usage, especially on multi-core servers

Final Thoughts

SQL Server isn’t one-size-fits-all. The default cost threshold of 5 is a legacy setting that made more sense 20 years ago — not in today’s environments where CPU cores are faster and queries are more complex.

Tuning this single setting can make a noticeable difference in how efficiently SQL Server uses its resources. Give it a try — your CPUs might thank you.

Use these queries to check your current plans

-- =============================================
-- Check for Cached Plans with No Parallelism (Single-threaded)
-- =============================================
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
    COUNT(*) AS SingleThreadedPlanCount
    -- Uncomment below to view details:
    -- , query_plan AS CompleteQueryPlan
    -- , n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText
    -- , n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS OptimizationLevel
    -- , n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS SubTreeCost
    -- , ecp.usecounts
    -- , ecp.size_in_bytes
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;


-- =============================================
-- Check for Cached Plans That Use Parallelism
-- =============================================
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
    n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText,
    n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS OptimizationLevel,
    n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS SubTreeCost,
    ecp.usecounts,
    ecp.size_in_bytes,
    query_plan AS CompleteQueryPlan,
    n.query('.') AS ParallelSubTreeXML
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;

⚠️ What If the Cost Threshold Is Set Too High?

While raising the cost threshold for parallelism can help reduce unnecessary CPU usage, setting it too high can swing the pendulum in the other direction.

If your threshold is set to something like 50 or 100, you may be preventing queries that would actually benefit from parallelism from using it. This can cause:

• Longer query runtimes, especially for complex joins, aggregations, and large scans
• Underutilized CPU cores, especially on multi-core servers
• Increased wait stats like CXCONSUMER or CXPACKET without seeing the performance gains you expect

---

🧪 How to Tell It’s Too High

Here are a few signs:

Complex queries with high estimated cost are running slower than expected Your server has plenty of CPU available but isn’t using it effectively Execution plans for large queries are single-threaded, even though they’re heavy

You can spot this by checking execution plans or using this DMV:

SELECT * 
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE qp.query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementSubTreeCost)[1]', 'float') > 20
AND qp.query_plan.exist('//RelOp[@PhysicalOp="Parallelism"]') = 0;

🎯 The Sweet Spot

Most environments find success with a threshold between 20 and 40 — high enough to avoid over-parallelizing simple queries, but low enough to allow real workloads to benefit from multiple CPUs.

🧠 Tip

Monitor and test. Like most SQL Server settings, the ideal threshold depends on your workload. Use Query Store, plan analysis, and wait stats to find what works best for your environment.