Parallelism and CXPACKET

In one line: a parallel plan trades CPU for elapsed time. It never does less work. It’s why a big report finishes in a quarter of the time, why a server full of small queries runs out of CPU, and why CXPACKET tops so many wait lists.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/parallelism on GitHub.

What you’ll see

  • CXPACKET and CXCONSUMER at the top of the wait stats.
  • A parallel query barely faster than serial. In the demo: 1,214 ms at DOP 4 against 1,294 ms serial, with 5.8 seconds of parallelism waits per run.
  • High CPU from many small queries that each run on every core.
  • A plan that stays serial although you expected it to go parallel.

Why it happens

  • Parallel is faster, not cheaper. A 2-million-row scan went from 225 ms to 60 ms on four threads, and CPU rose from 225 to 233 ms.
  • Skew. A row-mode parallel plan often hashes rows to threads by a key. If 90% of rows share one key value, one thread gets 90% of the work and the others wait. Those waits are the CXPACKET.
  • The cost threshold of 5 dates from the 1990s. In the demo, a 20 ms query went to 8 threads and used 25% more CPU to save 13 ms.

What to do

  • Check rows per thread before touching MAXDOP. The actual plan shows them per operator. If one thread has nearly all the rows, the fix is in the data or the query: a different partitioning, a pre-aggregation, or batch mode.
  • Batch mode doesn’t repartition rows by the key. The same skewed query ran in 205 ms instead of 1,214.
  • Raise the cost threshold for parallelism from 5. Most guidance starts at 50 and adjusts from there. This changes which queries can go parallel, not how the big ones run.
  • Read NonParallelPlanReason in a plan that stayed serial. It names non-inlinable scalar UDFs, table variable inserts and MAXDOP 1. A plan too cheap to be considered gives no reason at all, so compare its cost with the threshold.

What doesn’t help

  • Setting server-wide MAXDOP 1 because CXPACKET is high. The wait is a symptom of threads waiting for the slowest one. It isn’t the problem.
  • Assuming parallel means efficient. It always costs at least as much CPU as serial.

Check your server

-- The two settings that decide which queries go parallel, and how wide.
SELECT name, value_in_use
FROM sys.configurations
WHERE name IN ('cost threshold for parallelism', 'max degree of parallelism');

-- Database-level MAXDOP overrides (SQL Server 2016+).
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'MAXDOP';

See it happen

The parallelism demo measures serial against parallel, makes skew put 92.6% of rows on one thread, fixes it with batch mode, prices a query just over the cost threshold, and lists why four plans stayed serial. It changes no instance setting.

Related: wait stats triage · scalar UDF inlining · tempdb and #temp tables

Measured on SQL Server 2022 CU25 (16.0.4255.1), 8 schedulers. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights