Ask ten DBAs what MAXDOP should be and you get eleven answers, four of them citing a blog post from 2009 and one of them saying "set it to 1, CXPACKET waits are bad." That last answer has caused more misery than any other single setting I know, because it treats a symptom by amputating a feature. I inherited a server where someone had done exactly that: every report ran single-threaded, the nightly batch window doubled, and the CXPACKET waits were indeed gone, replaced by users.
The two parallelism knobs, max degree of parallelism and cost threshold for parallelism, are not superstition candidates. They have knowable, defensible values for your hardware and your workload, and the evidence for what they should be is readable in about five minutes before you touch anything.
What do CXPACKET and CXCONSUMER actually tell you?
Since SQL Server 2016 SP2 and 2017 CU3, the old CXPACKET wait was split in two, and the split is the entire diagnosis. CXCONSUMER is the benign side: a consumer thread in a parallel plan waiting for rows from producers, which happens constantly in healthy parallel execution and means nothing by itself. CXPACKET is the actionable side: producer threads waiting to push rows into an exchange whose consumer side is behind — sometimes that is skewed distribution, one thread chewing through most of the rows while the rest idle, and sometimes it is a plain slow downstream operator. Microsoft kept the alarming name on the actionable wait and moved the noise to the new one, and SQL Server 2022 moved exchange synchronization waits further out still, to CXSYNC_PORT and CXSYNC_CONSUMER. Any monitoring that predates the split adds them together and scares you with a meaningless total.
So before touching a knob, read the wait profile:
SELECT wait_type,
waiting_tasks_count,
wait_time_ms,
wait_time_ms * 1.0 / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('CXPACKET', 'CXCONSUMER')
ORDER BY wait_time_ms DESC;If CXCONSUMER dwarfs CXPACKET and nothing else is wrong, your parallelism is fine and your job is to fix the monitoring, not the server. If CXPACKET sits near the top with real waiting_tasks_count behind it, the usual cause is a skewed estimate: one branch of the plan got a wildly wrong row count, the exchanges distributed work by a hash of that wrongness, and the fix is statistics and indexes rather than MAXDOP. The full wait-reading method is in my wait statistics triage notes. The point here is narrower: CXPACKET is a messenger, and folklore shoots messengers.
What should MAXDOP actually be on modern hardware?
The guidance that has survived contact with reality is NUMA-aware: keep parallel plans inside a NUMA node when possible, which means MAXDOP at most the number of logical processors per node, and no higher than eight as a starting point for mixed OLTP. Eight is not magic; it is roughly where exchange coordination overhead starts eating the gains on typical workloads. SQL Server 2019's installer finally began suggesting a NUMA-aware value at setup time instead of leaving the unlimited zero, and Azure SQL Database defaults new databases to MAXDOP 8, which tells you what Microsoft's own operations teams consider a sane ceiling.
MAXDOP zero — all available processors, up to sixty-four — is a loaded gun on wide machines. A single query at DOP 64 does not run sixty-four times faster. It burns dozens of scheduler contexts, inflates its memory grant with per-thread buffers, which is how a parallelism setting quietly becomes a memory incident, and starves every concurrent query. The exceptions are real: a dedicated analytics instance, a maintenance-window index rebuild, and batch mode over columnstore all scale past eight before diminishing returns, which is why my columnstore and batch mode notes treat DOP as its own conversation. For a mixed OLTP box, eight per NUMA node is the boring, correct answer.
Why is cost threshold for parallelism still set to 5?
Because five was a defensible number on the hardware the default was chosen for, an era when a gigabyte of RAM was a server specification. The unit is not seconds, despite decades of informal reading; it is the optimizer's abstract cost unit, and on modern hardware a cost of five is a query that finishes in milliseconds. With the threshold at 5, SQL Server parallelizes trivial queries constantly, and each pays exchange setup, extra worker threads, and the DOP-multiplied grant for work that would have finished sooner serially. That is where much of the CXPACKET noise and grant pressure on a default-configured instance comes from: queries that never needed parallelism at all.
Raising the threshold is one of the safest changes in the product. It only stops cheap plans from going parallel, and the plans it stops paying for are by definition the cheap ones. I start at 50 and adjust by watching which expensive plans sit just above the line; shops I trust run anywhere from 25 to 100, and nobody defends 5 on merit. The change is two lines:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;Where should the settings live: server, database, or query?
Server-level maxdop is the default, not the hard ceiling — it stays conservative, and everything below it can override it. Database-scoped configuration, available since SQL Server 2016, sets MAXDOP per database: the reporting database gets 4 while the OLTP database keeps 8. Scoped configuration overrides the server value for that database, travels with the database through an AG failover, and on Azure SQL Database it is the only lever short of per-query hints. This is where most of the real tuning happens in shops with mixed workloads, because "the workload" usually turns out to be a database boundary wearing a trench coat.
Per query, OPTION (MAXDOP n) is the surgical tool. The month-end procedure that genuinely wants sixteen cores gets its hint in the code, where it is reviewed and versioned, instead of the whole instance paying for it. And Resource Governor's MAX_DOP per workload group is the fence for logins you do not trust, the ad-hoc reporting tools whose queries nobody reviews. The layering is the point: conservative server default, per-database reality, per-query exceptions, and Resource Governor's MAX_DOP as the one true hard cap around the untrusted.
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
GO
-- the surgical version, inside the one procedure that earned it
SELECT o.order_id, o.order_date, c.customer_name
FROM Sales.Orders AS o
JOIN Sales.Customers AS c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-07-01'
OPTION (MAXDOP 16);What you never do is set the server to 1 because a wait type frightened you. If a specific query parallelizes badly, fix that query's estimates or cap that query. Amputating parallelism server-wide to silence CXPACKET is how a reporting problem becomes a batch-window problem becomes an unnecessary hardware purchase.
What does a parallel plan actually cost?
Three things people forget. Worker threads: a plan at DOP 8 can run up to eight threads per parallel branch, and a plan with several concurrent branches can hold multiples of that — a hundred concurrent queries at DOP 8 can push the pool into THREADPOOL waits, which make CXPACKET look friendly. Memory: parallelism adds per-thread costs on top of the serial grant — every thread of a parallel sort wants its own buffer — so the parallel request can run several times the serial estimate, and enough of those at once is a RESOURCE_SEMAPHORE queue, the grant-throttling wait that deserves its own field notes. And skew amplification: exchanges distribute rows by hash, so one hot join key can route most of the work to a single thread, which is the CXPACKET story again wearing a different hat.
None of this argues against parallelism. It argues for paying for it deliberately: reading the waits first, sizing MAXDOP to the NUMA layout, pricing the threshold for modern hardware, and spending DOP where it returns something. Parallel plans are one of SQL Server's genuine strengths. Folklore is the bug.
Watching parallelism with MonPG when SQL Server support ships
The signals worth trending are boring ones: the CXPACKET-to-CXCONSUMER split over time, average DOP of expensive plans from Query Store, and parallel worker consumption against the pool limit, because parallelism problems announce themselves in counters before they announce themselves in tickets. MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development. Current status is on the SQL Server monitoring (coming soon) page, and until it ships, the wait-stats query above is the five-minute version of the same discipline.