Quick Scan Report – Cost Threshold For Parallelism

What this check looks for

sys.configurations where cost threshold for parallelism has a value_in_use of 5, which is the installed default, on a server with more than one processor.

Why it matters

5 is not a small number chosen for modern hardware. It is the original default from SQL Server 7.0, and it meant something quite different then.

The cost threshold is the estimated cost at which SQL Server will consider a parallel plan. Query cost is a unitless number derived from a model originally calibrated against the machine a Microsoft developer had in the late 1990s. A query costing 5 units was a substantial piece of work on that hardware. On a current server it is trivial.

The result is that almost everything is considered for a parallel plan, including queries that return a handful of rows. And parallelism has fixed overheads:

• Splitting the work across threads.
• Coordinating them, which is where CXPACKET waits come from.
• Recombining the results.
• One worker thread per branch per query, which is how the worker pool gets consumed.

For a query that would have run in 40 milliseconds on one thread, that overhead is most of the runtime. Small queries genuinely run slower in parallel, and on a busy OLTP instance there are thousands of them a minute.

The visible symptoms:

• CXPACKET at or near the top of the wait statistics.
• Worker thread pressure, because thousands of small queries each take several workers instead of one.
• Erratic timings on simple queries, depending on what else is running.

This setting is more often the fix than MAXDOP is. Lowering MAXDOP makes every parallel query narrower, including the large ones that benefit. Raising the cost threshold stops the small ones going parallel at all while leaving the large ones alone, which is what you actually want. The two are usually wrong together and should be fixed together.

How to confirm it yourself

SELECT [name], [value], [value_in_use]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] IN ('cost threshold for parallelism', 'max degree of parallelism');

Whether it is costing you:

SELECT [wait_type], [waiting_tasks_count], [wait_time_ms] / 1000 AS [wait_seconds]
  FROM sys.dm_os_wait_stats WITH (NOLOCK)
 WHERE [wait_type] IN ('CXPACKET', 'CXCONSUMER')
 ORDER BY [wait_time_ms] DESC;

The most useful query here looks at what is actually going parallel, from the plan cache, so the number you choose is based on your workload rather than on a rule of thumb:

SELECT TOP (50)
       qs.[execution_count],
       CAST(qp.[query_plan].value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
             (//p:StmtSimple/@StatementSubTreeCost)[1]', 'float') AS DECIMAL(12,2)) AS [estimated_cost],
       qs.[total_worker_time] / qs.[execution_count] AS [avg_cpu_us],
       qs.[total_elapsed_time] / qs.[execution_count] AS [avg_elapsed_us],
       SUBSTRING(t., (qs.[statement_start_offset] / 2) + 1,
                 ((CASE qs.[statement_end_offset] WHEN -1 THEN DATALENGTH(t.)
                        ELSE qs.[statement_end_offset] END - qs.[statement_start_offset]) / 2) + 1) AS [statement]
  FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
 CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS t
 CROSS APPLY sys.dm_exec_query_plan(qs.[plan_handle]) AS 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;

Read the top rows. A frequently executed query with an estimated cost of 6 or 8 going parallel is exactly what raising the threshold removes.

How to fix it

Raise it. The change is dynamic and immediate, with no restart:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'cost threshold for parallelism', 30;
RECONFIGURE;

Where to start:

Then tune it against your own plan cache rather than leaving it at whatever you picked. The query above gives you the distribution of costs that are currently going parallel; a good threshold sits above the bulk of the small ones and below the genuinely large ones.

Change one thing at a time. Raising the cost threshold and lowering MAXDOP together makes it impossible to tell which helped. Raise the threshold first, because it is usually the larger effect, then look at MAXDOP.

Measure properly. Clear the wait statistics before the change and compare after a representative period:

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

Do not aim for zero CXPACKET. It is not an error. It appears whenever a parallel query runs, including when parallelism is doing exactly what it should. From SQL Server 2017 the benign coordinator waits are reported separately as CXCONSUMER, which makes the distinction easier to see.

How long it takes

About two hours, mostly measuring before and after. The statement itself is instant.

Related reports

Related checks

Frequently asked questions

What is a query cost, exactly? A unitless estimate from the optimizer’s model, calibrated against hardware from the 1990s. It is not seconds and it does not correspond to anything measurable on your server. It is only useful for comparing queries with each other.

Is there a correct value? No, and anybody offering one has not looked at your workload. 30 is a reasonable place to start and the plan cache query above is how you refine it.

Should I set this or MAXDOP first? This one. It usually has the larger effect and it does not penalize the large queries that benefit from parallelism.

Our CXPACKET waits are huge. Is that the proof? Not on its own. CXPACKET accumulates whenever parallel queries run, including healthy ones. The proof is small, frequently executed queries appearing in the parallel plan list.