skip to content

In SQL Server, how do you use sys.dm_os_wait_stats to diagnose a slowdown, and what do PAGEIOLATCH_SH, CXPACKET and LCK_M_X waits point to?

level: seniorimportance: must knowfreq 60%

answer

  1. what workers spent time waiting on
  2. cumulative since startup, so take deltas
  3. filter the idle queue waits
  4. signal wait means waiting for CPU

basics

~10 s

sys.dm_os_wait_stats totals completed waits per wait type since startup, so diff two snapshots and drop idle waits. PAGEIOLATCH_SH means reading pages from disk, CXPACKET parallel-exchange waiting, LCK_M_X waiting for an exclusive lock.

solid answer

~40 s

`sys.dm_os_wait_stats` is instance-wide and cumulative since the engine started or since `DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)`, so I snapshot it twice across the slow period and rank the difference in `wait_time_ms`, after removing idle queue waits such as `SLEEP_TASK`, `LAZYWRITER_SLEEP` and `XE_TIMER_EVENT`. `signal_wait_time_ms` is the part spent runnable but waiting for a CPU. `PAGEIOLATCH_SH` means tasks waiting for data pages to be read from disk: slow storage, or queries reading far more than they should. `CXPACKET` is the producer side of parallel exchanges, while `CXCONSUMER` is normal; tune the query before touching parallelism settings. `LCK_M_X` means sessions queued for an exclusive lock, so I go to blocking chains in `sys.dm_exec_requests`. For per-query attribution I use `sys.query_store_wait_stats`.

code

sql · 10 lines
sql
SELECT TOP (10)
       wait_type,
       waiting_tasks_count,
       wait_time_ms,
       signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (N'SLEEP_TASK', N'LAZYWRITER_SLEEP', N'XE_TIMER_EVENT',
                        N'REQUEST_FOR_DEADLOCK_SEARCH', N'CHECKPOINT_QUEUE',
                        N'SQLTRACE_BUFFER_FLUSH', N'WAITFOR')
ORDER BY wait_time_ms DESC;

go deeper

for a junior

Recall that SQL Server records what queries wait on, and that lock, disk-read and parallelism waits each mean something different.

for a middle

Explain cumulative counters, signal versus resource wait time, and why you diff snapshots and filter idle waits.

for a senior

Turn the top waits into a checked hypothesis: file latency against read volume, blocking chains for lock waits, plans for parallelism waits.

for a principal

Decide what wait baseline the team captures continuously, so an incident can be compared with normal rather than with cumulative noise.

## What the DMV measures Every SQL Server worker that cannot proceed records a **wait** of some type: a lock it cannot get, a page that is still being read, a log flush, a free CPU. `sys.dm_os_wait_stats` aggregates the waits that have **completed**, one row per wait type, across the whole instance. It shows where time went, not what is stuck right now; current waits are in `sys.dm_os_waiting_tasks` and in the `wait_type` and `wait_resource` columns of `sys.dm_exec_requests`. Its columns: - `waiting_tasks_count`: how many waits of this type started. - `wait_time_ms`: total time spent, **including** the signal wait. - `max_wait_time_ms`: the longest single wait. - `signal_wait_time_ms`: time between the thread being signalled that its resource is ready and actually running. A large share here means tasks queue for CPU. The time spent on the resource itself is therefore `wait_time_ms - signal_wait_time_ms`, and the ratio of the two is the quickest first split between "waiting for something" and "waiting for a CPU". Waits fall into three groups: **resource waits** (locks, latches, I/O, network), **queue waits** (idle background tasks waiting for work) and **external waits** (extended procedures, linked servers). ## Turning totals into a diagnosis 1. Remember the numbers are **cumulative** since the engine started or since `DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)`. They are not persisted across restarts. A total since last month's restart hides today's incident. 2. Take a snapshot, wait through the slow period, take another, and rank the **difference**. 3. Exclude idle and background queue waits that grow whether or not anything is wrong, for example `SLEEP_TASK`, `LAZYWRITER_SLEEP`, `XE_TIMER_EVENT`, `REQUEST_FOR_DEADLOCK_SEARCH`, `CHECKPOINT_QUEUE`, `SQLTRACE_BUFFER_FLUSH` and `WAITFOR`. 4. Narrow down: `sys.dm_exec_session_wait_stats` for one session, and Query Store's `sys.query_store_wait_stats` (captured per query by default) for which statements own the waits. Query Store groups wait types into categories such as **Buffer IO** for `PAGEIOLATCH_%`, **Lock** for `LCK_M_%` and **Parallelism** for `CXPACKET` and `CXCONSUMER`. ## Reading the common wait types | Wait type | What the task waited for | First thing to check | | --- | --- | --- | | `PAGEIOLATCH_SH` | A buffer latch on a page being read from disk | Read latency per file, then which queries scan | | `PAGELATCH_UP`, `PAGELATCH_EX` | A latch on a page already in memory, no I/O | Allocation pages in `tempdb`, or a hot last page | | `CXPACKET` | Parallel plan, producer side of an exchange | The parallel query's plan and its skew | | `CXCONSUMER` | Consumer thread waiting for rows from producers | Usually nothing: normal parallel execution | | `LCK_M_X`, `LCK_M_U`, `LCK_M_S` | Exclusive, update or shared lock held by another session | The blocking chain and the head blocker | | `WRITELOG` | A transaction log flush, usually at commit | Log disk latency and commit rate | | `SOS_SCHEDULER_YIELD` | A task yielded the CPU and waits in the runnable queue | CPU-heavy scans and plan regressions | | `ASYNC_NETWORK_IO` | The client acknowledging rows already sent | The application reading results slowly | | `RESOURCE_SEMAPHORE` | A query memory grant | Oversized grants and concurrency | ## A worked reading Suppose a fifteen-minute delta during a slow afternoon shows, in order, `PAGEIOLATCH_SH`, `LCK_M_X` and `CXPACKET`, with signal waits under a tenth of the total. CPU is not the bottleneck. The reads deserve the first look: if file latency is normal, the next step is the statements with the most logical reads in that window. The lock waits are checked separately for a head blocker. The parallelism waits are read from the plans of the parallel queries, not fixed with a server setting. ## The traps - **A wait is a symptom, not a cause.** High `PAGEIOLATCH_SH` can mean slow disks, but just as often a missing index or a regressed plan reading far more pages than it needs. Compare file latency from `sys.dm_io_virtual_file_stats` with how much is being read before buying storage. - **Do not answer `CXPACKET` with `MAXDOP 1` everywhere.** The documentation's order is to tune the query first (for example with an index), and only then consider the cost threshold for parallelism or a lower degree of parallelism. `CXCONSUMER` on its own is normal. - **`PAGELATCH` is not `PAGEIOLATCH`.** The first is in-memory contention on a hot page; the second involves I/O. Mixing them sends you to the storage team for a `tempdb` allocation problem. - **Lock waits name the victim, not the culprit.** `LCK_M_X` time tells you sessions were blocked; the head blocker is found in `sys.dm_exec_requests` through `blocking_session_id`. Which locks SQL Server takes and why is the subject of the locking material.

  • The top wait is PAGEIOLATCH_SH. How do you tell slow storage from queries that read too much?
    Check per-file read latency in `sys.dm_io_virtual_file_stats` (stall time divided by reads). Healthy latency with huge read volume points at queries: find the top logical-read statements in Query Store or `sys.dm_exec_query_stats` and look for scans and regressed plans. High latency on modest volume points at the storage path.
  • Wait totals look normal but users still complain. What could the DMV be hiding?
    It only counts completed waits, so a few sessions stuck for an hour may not have finished their waits yet; look at `sys.dm_exec_requests` and `sys.dm_os_waiting_tasks` for current waits. It is also instance-wide, so one database's problem can be diluted. Per-query wait statistics in Query Store attribute waits to statements.

  • Answers high CXPACKET by setting max degree of parallelism to 1 server-wide.
  • Says PAGEIOLATCH waits always mean the disks are too slow.
  • Treats PAGELATCH_EX waits as disk I/O waits.
  • Thinks sys.dm_os_wait_stats shows what is waiting right now.
  • Compares wait totals since last month's restart against today's incident.