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?
answer
- what workers spent time waiting on
- cumulative since startup, so take deltas
- filter the idle queue waits
- signal wait means waiting for CPU
basics
~10 ssys.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 linesSELECT 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
Recall that SQL Server records what queries wait on, and that lock, disk-read and parallelism waits each mean something different.
Explain cumulative counters, signal versus resource wait time, and why you diff snapshots and filter idle waits.
Turn the top waits into a checked hypothesis: file latency against read volume, blocking chains for lock waits, plans for parallelism waits.
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.