The worst page I ever took was a 3 a.m. "everything is slow" where CPU sat at 30 percent, the storage latency dashboards were green, and the error log was clean. Everything looked fine except the users. What actually told the story was a five-minute snapshot of sys.dm_os_wait_stats: WRITELOG had climbed from background noise to the top of the board, and the log file was sitting on a volume that a backup agent had started hammering at exactly 2:55. Waits are the front door of every SQL Server incident. Every time a thread cannot run, the engine records why, and that running tally is the closest thing SQL Server has to a confession.
The catch is that most people open that confession wrong. They query sys.dm_os_wait_stats once, see since-restart totals, and either panic at the wrong wait type or shrug at a pile of numbers that mean nothing. After fifteen years of triaging SQL Server 2016 through 2022 and a fair amount of Azure SQL, this is the workflow I trust.
Why are since-restart wait totals worse than useless?
Because they smear every quiet night, every batch window, and every past incident into a single pile, and the biggest number in that pile is usually whatever went wrong three weeks ago. The columns in sys.dm_os_wait_stats, waiting_tasks_count and wait_time_ms, are monotonic counters that have been accumulating since the service started or since someone last cleared them. An instance with 40 days of uptime is showing you 40 days of weather. You asked about today.
The fix is to diff two snapshots over an interval that matches the complaint. If users say the system dragged between 14:00 and 14:30, capture a baseline, wait, capture again, and subtract. That turns the DMV into an answering machine for the exact window that hurt:
-- capture a baseline
SELECT wait_type, waiting_tasks_count, wait_time_ms,
max_wait_time_ms, signal_wait_time_ms,
SYSDATETIME() AS captured_at
INTO #wait_baseline
FROM sys.dm_os_wait_stats;
WAITFOR DELAY '00:05:00';
-- diff, dropping the classic benign list
SELECT s.wait_type,
s.waiting_tasks_count - b.waiting_tasks_count AS waiting_tasks,
s.wait_time_ms - b.wait_time_ms AS wait_ms,
s.signal_wait_time_ms - b.signal_wait_time_ms AS signal_wait_ms,
(s.wait_time_ms - b.wait_time_ms) * 1.0 /
NULLIF(s.waiting_tasks_count - b.waiting_tasks_count, 0) AS ms_per_wait
FROM sys.dm_os_wait_stats AS s
JOIN #wait_baseline AS b ON b.wait_type = s.wait_type
WHERE s.wait_time_ms - b.wait_time_ms > 0
AND s.wait_type NOT IN (
'BROKER_EVENTHANDLER','BROKER_RECEIVE_WAITFOR','BROKER_TASK_STOP',
'BROKER_TO_FLUSH','BROKER_TRANSMITTER','CHECKPOINT_QUEUE',
'CLR_AUTO_EVENT','CLR_MANUAL_EVENT','CLR_SEMAPHORE',
'DBMIRROR_DBM_EVENT','DBMIRROR_EVENTS_QUEUE','DBMIRROR_WORKER_QUEUE',
'DBMIRRORING_CMD','DIRTY_PAGE_POLL','DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT','FT_IFTSHC_MUTEX',
'HADR_CLUSAPI_CALL','HADR_FILESTREAM_IOMGR_IOCOMPLETION',
'HADR_LOGCAPTURE_WAIT','HADR_NOTIFICATION_DEQUEUE',
'HADR_TIMER_TASK','HADR_WORK_QUEUE',
'KSOURCE_WAKEUP','LAZYWRITER_SLEEP','LOGMGR_QUEUE',
'ONDEMAND_TASK_QUEUE','PREEMPTIVE_XE_GETTARGETSTATE',
'PWAIT_ALL_COMPONENTS_INITIALIZED','QDS_ASYNC_QUEUE',
'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','QDS_SHUTDOWN_QUEUE',
'REDO_THREAD_PENDING_WORK','REQUEST_FOR_DEADLOCK_SEARCH',
'RESOURCE_QUEUE','SERVER_IDLE_CHECK','SLEEP_BPOOL_FLUSH',
'SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MASTERDBREADY',
'SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP',
'SLEEP_SYSTEMTASK','SLEEP_TASK','SLEEP_TEMPDBSTARTUP',
'SNI_HTTP_ACCEPT','SP_SERVER_DIAGNOSTICS_SLEEP',
'SQLTRACE_BUFFER_FLUSH','SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'SQLTRACE_WAIT_ENTRIES','WAIT_XTP_RECOVERY','WAITFOR',
'WAIT_FOR_RESULTS','XE_DISPATCHER_JOIN','XE_DISPATCHER_WAIT',
'XE_LIVE_TARGET_TVF','XE_TIMER_EVENT')
ORDER BY wait_ms DESC;One column deserves special respect: signal_wait_time_ms. A wait has two phases. The resource wait is the time suspended on the thing itself, the page read or the lock. The signal wait is the time spent sitting in the runnable queue afterwards, waiting for a scheduler to pick the thread back up. If signal wait time is a large fraction of total wait time across your diff, threads are queuing for CPU itself, and no index or storage upgrade fixes that. You have a CPU pressure problem wearing a costume.
Which waits are just background noise?
Anything a background thread generates while it idles by design. The engine keeps dozens of system threads alive that mostly sleep: the lazywriter naps between sweeps, the checkpoint queue waits for work, the deadlock monitor wakes every five seconds whether or not there is a deadlock to find, Query Store runs its own persistence loop, and an availability group secondary spends its life waiting for log records. On an idle instance these threads still accumulate wait time, so an unfiltered top-20 list is wallpaper.
The list in the query above is the classic filter the community has maintained for years, and I keep my copy in the repo next to my runbooks. Filter it aggressively. I would rather occasionally re-add a wait type that turned out to matter on my system, like an HADR wait on a latency-sensitive synchronous replica, than read through forty rows of SLEEP_TASK and XE_TIMER_EVENT on every incident. What survives the filter is what the workload itself waited on, and that list is usually short enough to hold in your head.
What do CXPACKET and CXCONSUMER actually mean?
They mean parallelism is happening, and since SQL Server 2016 SP2 they split one old bucket into two so you can tell the boring half from the interesting half. In a parallel plan, threads hand rows to each other through exchange operators. Before 2016 SP2, both sides of that handoff recorded CXPACKET, so the wait type mixed "consumer thread idle because producers are slow" with "producer thread stalled because the exchange is full." Now the idle consumer side records CXCONSUMER, which is mostly benign, and the producer side keeps CXPACKET. When CXPACKET tops a diff on 2016 SP2 or later, it usually means producers are starving: a skewed distribution where one thread does all the work while fifteen wait, or a plan that went parallel over a bad estimate. SQL Server 2022 splits the family further: producer threads stalled on a full exchange port record CXSYNC_PORT, and the remaining benign consumer-side synchronization lands in CXSYNC_CONSUMER, so on 2022 the actionable bucket is CXPACKET plus CXSYNC_PORT.
What it does not mean is "set MAXDOP to 1." I watched a team do exactly that to a 16-core reporting box. The CXPACKET waits vanished and every report got four times slower, because the plan shape never changed, it just ran serial. The fix that worked was boring: cost threshold for parallelism was still the default 5, so plans with an estimated cost of 6 were spinning up sixteen threads for two seconds of work. We set cost threshold to 50, set MAXDOP to 8 per the core-count guidance, and then fixed the one query that was scanning 40 million rows for want of an index. CXPACKET dropped from 60 percent of wait time to under 5. Tune the knob that controls who goes parallel, not the one that bans parallelism.
How do I branch on PAGEIOLATCH, WRITELOG, and LCK_M_*?
Each of those wait families points at a different subsystem, and treating them as interchangeable is how people buy storage they did not need. PAGEIOLATCH_* means a session is waiting for a data page to be read from storage into the buffer pool. That is either slow storage, insufficient memory forcing constant re-reads, or queries reading far more pages than they should. My first move is sys.dm_io_virtual_file_stats for per-file latency, my second is the plan cache looking for scans that an index would turn into seeks. WRITELOG is different in kind: it is the wait between generating a log record and that record hitting stable storage, and you pay it at commit time. High WRITELOG means the log device is slow or the commit pattern is pathological, and the classic pathology is a loop that commits every row instead of batching a thousand rows per transaction. I once cut WRITELOG by 90 percent without touching storage, just by making a purge job commit in batches.
LCK_M_* waits mean old-fashioned blocking: a session is waiting on a lock another session holds. The wait stats tell you blocking is the pain, but not who is holding the knife. That is sys.dm_tran_locks and the blocked process report, and if blocking between readers and writers is a chronic condition rather than a spike, read-committed snapshot isolation changes the rules entirely; I wrote up the migration in turning on READ_COMMITTED_SNAPSHOT safely. The triage point is that these three branches rarely share a fix, so the first job of the wait-stats diff is routing you down exactly one of them.
How do I see the waits for a single session?
Use sys.dm_exec_session_wait_stats, which has existed since SQL Server 2016 and has the same shape as the instance-wide DMV but scoped to one session. It accumulates while the session is open, so the workflow for "why is this one job slow" is to grab the job's session_id, snapshot its waits before a step runs, snapshot after, and diff. That isolates the job from the rest of the instance's noise, which matters on a busy server where the instance-wide diff is dominated by other people's queries.
SELECT session_id, wait_type, waiting_tasks_count,
wait_time_ms, max_wait_time_ms, signal_wait_time_ms
FROM sys.dm_exec_session_wait_stats
WHERE session_id = 57
ORDER BY wait_time_ms DESC;Two honest caveats. The numbers vanish when the session closes, so capture before the job ends, not after it fails. And the counters live and die with the session's current incarnation: on a pooled connection the stats are reset every time the connection is checked back out and reset, so a pooled workload shows you only the current borrow, never the hundred requests before it. For per-query wait data on 2017 and later you want the actual execution plan's wait info or Query Store's wait categories, but for a dedicated job session this DMV is the fastest answer on the box.
When is it right to clear wait stats?
Almost never on production, and even then with your eyes open. DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) zeroes the counters instance-wide. That is a reasonable move in a controlled repro on a test box where you want a clean before and after. It is a rude move on a shared production instance at noon, because you just deleted everyone else's baseline to make your own screenshot prettier, and any teammate mid-incident now has nothing to compare against.
My default is to never clear and always diff. The diff pattern gives you a clean interval without destroying history, it works against every monitoring tool that also reads those counters, and it keeps you honest about what changed. If you must clear on a production box, snapshot to a table first and tell the team. Clearing is a scalpel, not a morning ritual.
Watching waits with MonPG, when SQL Server support lands
Everything above is diff-based because the counters are cumulative, and that is exactly the model a monitoring tool should apply for you: sample on an interval, subtract, drop the benign list, and trend waits per second split by resource versus signal time, per wait family. MonPG does that today for PostgreSQL, where I wrote up the equivalent workflow around pg_stat_activity and lock waits in deadlock diagnosis across MySQL and PostgreSQL. SQL Server support is in active development and on the roadmap, and the wait-stats model described here is the shape of what we intend to graph. Progress lives on the SQL Server monitoring (coming soon) page, and until it ships we will not pretend otherwise. In the meantime, the diff query above plus a scheduled table is ninety percent of the value, and it costs you one job and one temp table.