Wait Statistics Module

A task exhausted its 4 ms scheduling quantum and yielded the CPU voluntarily so other tasks on the same scheduler could run. It is not a task being denied CPU — it is a task that had plenty and used all of it.

Where to look next: Expect a huge count with a tiny average. The usual source is a query burning through pages that are already in memory: large scans, nested loops over too many rows, a missing index that turned a seek into a scan. Chase the query, not the CPU.

A thread in a parallel plan waited at an exchange operator. On SQL Server 2016 SP2 and 2017 onward the benign half of this — a consumer thread waiting for rows that have not been produced yet — was split out into CXCONSUMER, so what remains under CXPACKET is the more meaningful signal.

Where to look next: Parallelism is not a defect, and setting MAXDOP to 1 to make this number go away usually trades one problem for a slower one. Look at cost threshold for parallelism first, which still defaults to 5 and sends trivial queries parallel, then at row-distribution skew and stale statistics.

The consumer side of a parallel exchange, waiting on its producer. On its own this is normal behavior of any parallel plan and carries almost no diagnostic weight.

Where to look next: Most analyses filter it out alongside the idle waits. If you are on a version that predates the split, the same traffic is folded into CXPACKET.

A task could not start because no worker thread was free. This is a symptom rather than a cause, and it is a severe one: with every worker consumed, new connections — including yours — may not get one either.

Where to look next: Look for what is holding the workers rather than for a way to raise max worker threads. A long blocking chain is the usual answer, since each blocked session keeps its worker parked for the duration.

A task is holding a latch on a buffer-pool page while the page is read in from disk, because the data it needs is not in memory. The SH variant is a read; PAGEIOLATCH_EX is the same wait during a write path.

Where to look next: Average wait is the number to read here. Sustained double-digit milliseconds points at storage latency, but the more common and far cheaper fix is to read fewer pages — indexing, plan shape, or a buffer pool too small to hold the working set.

A commit is waiting for its log block to be hardened to the transaction log file. Nothing commits until this completes, so it sits directly in the path of write throughput.

Where to look next: Two independent causes. Log file latency is one. The other is transaction shape: ten thousand autocommit statements in a loop force ten thousand log flushes where one explicit transaction would force one.

I/O that is not a data-page read — reading the transaction log, and sort or hash operators spilling to tempdb when their memory grant proved too small.

Where to look next: When it climbs alongside memory-grant symptoms, treat it as evidence of spills rather than of a slow disk, and go look at cardinality estimates.

A backup task waiting on its backup device. Entirely expected while a backup is running, and meaningless outside that window.

Where to look next: Only worth investigating if backups are overrunning. Check the backup target throughput, buffer count, and whether compression is on.

Waiting to acquire a lock, with the suffix naming the mode: LCK_M_S shared, LCK_M_X exclusive, LCK_M_U update, LCK_M_IX intent exclusive, LCK_M_SCH_M a schema modification queued behind readers. Whatever the suffix, this is blocking.

Where to look next: Cumulative lock waits tell you blocking happened, never who caused it. That answer only exists live, in the blocking chain, which is why this module pairs the counters with a chain view.

Contention on a page that is already in memory — no disk is involved, despite how similar the name looks to PAGEIOLATCH. The UP mode on allocation pages is the classic tempdb signature: PFS, GAM and SGAM pages, addressed as 2:1:1, 2:1:2 and 2:1:3.

Where to look next: For tempdb allocation contention, the answer is multiple equally sized tempdb data files. SQL Server 2016 and later configure this at setup and enable uniform extent allocation by default.

Exclusive in-memory page contention. On a user table this is usually last-page insert contention: many sessions inserting into a clustered index on an ever-increasing key all target the same trailing page.

Where to look next: On SQL Server 2019 and later, OPTIMIZE_FOR_SEQUENTIAL_KEY addresses this directly. Otherwise the fix is an index design that spreads inserts across more pages.

A latch that is not on a buffer page — an internal structure of some kind. The wait type alone does not say which.

Where to look next: The meaning lives in latch_class in sys.dm_os_latch_stats. Without that breakdown this wait type is not actionable.

A query is waiting for a memory grant before it can begin executing. The grant is sized at compile time from estimated row counts, so this is usually a cardinality-estimation problem wearing a memory costume.

Where to look next: A handful of queries with wildly overestimated grants can starve an otherwise healthy server. Find the largest grants and check their estimates against actuals before concluding the server needs more RAM.

Waiting for memory in which to compile a plan, rather than to run one. It points at a workload that compiles constantly instead of reusing plans.

Where to look next: Look at ad-hoc statement volume, parameterisation, and whether optimize for ad hoc workloads is enabled.

Contention on a thread-safe internal memory object. It commonly accompanies heavy plan-cache churn on servers running large volumes of unparameterised ad-hoc SQL.

Where to look next: Usually resolves as a side effect of fixing the compilation volume rather than as a problem in its own right.

SQL Server has results ready and is waiting for the client to consume them. The name blames the network; the cause almost never is. It usually means an application is processing a result set row by row while holding it open, or running on a slow link.

Where to look next: This one is fixed in application code, not on the server. Consume the result set fully before processing it, and stop returning columns and rows nobody reads.

A commit on the primary waiting for a synchronous-commit availability group secondary to harden the log record. By design, this ties your commit latency to the secondary’s log disk and the network between them.

Where to look next: Measure the secondary the way you would measure the primary. A slow log disk on a replica nobody looks at will show up as slow writes on the server everybody looks at.

SQL Server called out through an OLE DB provider. Linked server queries are the obvious source; DBCC CHECKDB is the non-obvious one, and its appearance during a scheduled integrity check is expected.

Where to look next: Correlate against the maintenance window before treating it as a linked-server problem.

A worker left the SQL Server scheduler to make a Windows API call and ran preemptively while outside. Authentication, file system operations and extended stored procedures all produce these.

Where to look next: The suffix names the call. PREEMPTIVE_OS_AUTHENTICATIONOPS climbing, for instance, points at domain controller latency rather than anything inside the database engine.