Wait stats triage

In one line: a request’s elapsed time is its CPU time plus its waits. Wait statistics tell you what the waits were for (a lock, the log, the disk, a CPU), but only if you measure the right session, as a difference, and remember that some time never shows up as a wait at all.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/wait-stats-triage on GitHub.

What you’ll see

  • “The server is slow” and an instance-wide wait list topped by names like SOS_WORK_DISPATCHER, SLEEP_TASK and XE_TIMER_EVENT. In the demo, all ten of the raw top 10 were idle background waits.
  • LCK_M_*: blocked by another session’s lock.
  • WRITELOG, or on Linux PREEMPTIVE_OS_FLUSHFILEBUFFERS: waiting for commits to reach the log file.
  • PAGEIOLATCH_SH: waiting for a page to be read from disk.
  • SOS_SCHEDULER_YIELD: a busy query handing back the CPU, high in count but not necessarily in time.

Why it happens

Every task that can’t proceed records what it’s waiting on. sys.dm_os_wait_stats adds those up for the whole instance since it started, including background tasks that spend their lives waiting for work. That makes the raw totals mostly noise, and they say nothing about one slow query.

What to do

  • Start with elapsed = CPU + waits for the request you care about. If CPU is most of it, wait names won’t help.
  • Measure per session with sys.dm_exec_session_wait_stats, taking a snapshot before and after and subtracting.
  • Watch it live with sys.dm_os_waiting_tasks. It shows the wait type, how long so far, the blocking session and the exact resource.
  • Filter the instance-wide view to drop background waits, and compare two snapshots instead of reading totals since startup.
  • Many single-row commits each cost a log flush. In the demo, 2,000 separate commits took 1,291 ms. The same inserts in one transaction took 16 ms.

What doesn’t help

  • Trusting a missing wait. Several things hid real time in the demo:
    • A blocked background session waited 2 seconds and recorded 0 ms of lock waits.
    • On Linux the commit wait appeared as PREEMPTIVE_OS_FLUSHFILEBUFFERS, not WRITELOG.
    • On fast storage, 1,692 real disk reads produced no PAGEIOLATCH waits at all.
  • DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR). It resets the numbers for the whole instance, and every other baseline with them.

Check your server

-- What is waiting right now, on what, and behind whom.
SELECT wt.session_id,
       wt.wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.resource_description,
       r.command,
       DB_NAME(r.database_id) AS database_name
FROM sys.dm_os_waiting_tasks AS wt
JOIN sys.dm_exec_requests AS r ON r.session_id = wt.session_id
WHERE wt.session_id <> @@SPID
ORDER BY wt.wait_duration_ms DESC;

See it happen

The wait-stats-triage demo causes a lock wait, commit waits, cold reads and a CPU-bound query on purpose, in about 30 seconds. It measures each one per session and checks elapsed = CPU + waits for every run. It never clears the instance’s wait statistics.

Related: blocking chains · parallelism · RCSI and snapshot isolation · why is my transaction log full?

Measured on SQL Server 2022 CU25 (16.0.4255.1), Linux container. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights