Wait Statistics Methodology, Signal vs Resource Waits, Baselines, and Anti-Patterns

Learning outcomes

The morning after a latency incident, ServiceHub’s instance-level wait statistics show a large amount of one wait type. Declaring that wait “the root cause” is tempting because the table looks authoritative. But wait statistics are accounting: they say where workers spent time waiting, aggregated over a scope and time window. They become diagnostic evidence only when you know when the counters started, what workload ran, which waits are benign for that workload, and what CPU, I/O, locks, plans, and Query Store show during the same interval.

1. Wait time is scheduled work that could not run immediately

SQL Server workers alternate among running, waiting for a resource, and waiting on a runnable queue after the resource becomes available. sys.dm_os_wait_stats.wait_time_ms is cumulative total wait time and includes signal_wait_time_ms. A useful derived value is therefore resource_wait_ms = wait_time_ms - signal_wait_time_ms. Signal wait is time after a worker is signaled until a scheduler gives it CPU; resource wait is the rest. That split is a clue, not an automatic CPU diagnosis.

SELECT    SYSDATETIMEOFFSET() AS observed_at,    wait_type,    waiting_tasks_count,    wait_time_ms,    signal_wait_time_ms,    wait_time_ms - signal_wait_time_ms AS resource_wait_ms,    max_wait_time_msFROM sys.dm_os_wait_statsWHERE wait_time_ms > 0ORDER BY wait_time_ms DESC;

These counters accumulate since instance start or since an explicit clear. Many background/idle waits are expected. Filtering them can improve readability, but a hard-coded “ignore list” is version- and workload-dependent. Preserve raw data somewhere if you build a long-lived collector, then apply presentation filters separately.

2. Measure an interval instead of reading a lifetime total

A before/after snapshot makes the time window explicit and avoids clearing global evidence. The example below captures a small set of rows in a temp table, performs a representative read workload, and calculates deltas. It does not promise a particular wait will dominate on your laptop; VM scheduling, cache warmth, storage and concurrent work determine the result.

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_msINTO #wait_beforeFROM sys.dm_os_wait_stats;USE ServiceHubObservabilityLab;GOSELECT status, COUNT_BIG(*) AS orders, SUM(CONVERT(bigint, priority)) AS priority_sumFROM lab22.WorkOrderGROUP BY statusOPTION (MAXDOP 1);GOSELECT    a.wait_type,    a.waiting_tasks_count - b.waiting_tasks_count AS delta_waits,    a.wait_time_ms - b.wait_time_ms AS delta_wait_ms,    a.signal_wait_time_ms - b.signal_wait_time_ms AS delta_signal_ms,    (a.wait_time_ms - b.wait_time_ms) -      (a.signal_wait_time_ms - b.signal_wait_time_ms) AS delta_resource_msFROM sys.dm_os_wait_stats AS aJOIN #wait_before AS b ON b.wait_type = a.wait_typeWHERE a.wait_time_ms > b.wait_time_msORDER BY delta_wait_ms DESC;DROP TABLE #wait_before;

The wrong approach is clearing counters on a shared production instance merely to make them easier to read. DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) resets shared diagnostic context. Keep that command out of routine troubleshooting; if an isolated disposable test instance needs a controlled experiment, record the reset timestamp and why it was acceptable.

3. Corroborate cumulative waits with tasks that are waiting now

sys.dm_os_waiting_tasks exposes currently waiting tasks. Pair it with active requests and sessions to ask “who is waiting on what now?” The blocking exercise from Lesson 1 is ideal: while Window B is blocked, the request-level evidence should show a lock wait, and instance wait deltas may accumulate a matching LCK_M_... type. That cross-layer agreement is stronger than either source alone.

SELECT    wt.session_id,    wt.wait_duration_ms,    wt.wait_type,    wt.resource_description,    r.blocking_session_id,    r.status,    r.command,    s.login_name,    s.program_nameFROM sys.dm_os_waiting_tasks AS wtLEFT JOIN sys.dm_exec_requests AS r  ON r.session_id = wt.session_idLEFT JOIN sys.dm_exec_sessions AS s  ON s.session_id = wt.session_idWHERE wt.session_id > 50ORDER BY wt.wait_duration_ms DESC;

Even here, correlation is not automatically causation. A request can wait on locks because a slow client held a transaction open, because a plan touched too many rows, or because business logic intentionally serialized an operation. Move from the wait to the transaction, plan, query text, and application behavior before selecting a remedy.

4. Query Store turns waits into persisted per-query categories

Starting with SQL Server 2017, Query Store can persist wait information by query plan and runtime interval. That is valuable after an incident because the active request and waiting-task rows are gone. Query Store groups raw wait types into categories such as CPU, Lock, Buffer IO, Network IO, and Memory rather than retaining every raw wait type. The active interval can contain more than one row for a plan/category because some data is persisted while other data is still in memory, so aggregate the metrics across the grouping keys.

USE ServiceHubObservabilityLab;GOSELECT actual_state_desc, desired_state_desc, wait_stats_capture_mode_desc,       current_storage_size_mb, readonly_reasonFROM sys.database_query_store_options;GOSELECT TOP (30)    p.query_id,    ws.plan_id,    ws.wait_category_desc,    SUM(ws.total_query_wait_time_ms) AS total_wait_ms,    MAX(ws.max_query_wait_time_ms) AS max_wait_ms,    MIN(i.start_time) AS first_interval,    MAX(i.end_time) AS last_intervalFROM sys.query_store_wait_stats AS wsJOIN sys.query_store_plan AS p  ON p.plan_id = ws.plan_idJOIN sys.query_store_runtime_stats_interval AS i  ON i.runtime_stats_interval_id = ws.runtime_stats_interval_idWHERE i.end_time >= DATEADD(hour, -2, SYSUTCDATETIME())GROUP BY p.query_id, ws.plan_id, ws.wait_category_descORDER BY total_wait_ms DESC;

Query Store is persisted but still aggregated. It does not preserve a packet-by-packet timeline or every individual wait occurrence. Combine it with Extended Events when you need event-level evidence, and with host metrics when the hypothesis crosses the SQL/OS boundary.

5. Build a baseline that has reset metadata

A useful wait baseline records the observation start/end, instance start time, build, workload window, replica role, CPU count, memory limits, Query Store state, and any counter reset. Compare like with like: a business-day peak should not be compared blindly with an overnight backup window. Focus on deltas and changes in workload shape, then corroborate with resource evidence.

When you automate wait collection, sample at a cadence that can capture the incidents you care about without generating excessive telemetry. Store both the raw cumulative counters and the derived deltas. If a service restart or explicit reset occurs between samples, mark the interval invalid instead of calculating a negative or nonsensical delta. This is the same principle used with network and OS monotonic counters: reset detection is part of the data model.

Also distinguish wait volume from impact. A background component can accumulate large benign wait time because it exists for the full uptime, while a smaller burst of lock or worker-thread waits can dominate user-visible latency for a ten-minute business window. Weight the analysis by the affected workload and time period, not by lifetime milliseconds alone.

Authoritative references

• sys.dm_os_wait_stats
• sys.dm_os_waiting_tasks
• Monitor performance by using Query Store
• sys.query_store_wait_stats
• SQL Server 2025 build versions