Comparing Two Wait Stats Snapshots to See What Changed

A server that has been running for months can bury this morning's slowdown under old wait totals. Wait stats snapshots show what accumulated during the interval you actually care about.

Remember What the Counter Means

sys.dm_os_wait_stats holds accumulated waits since the instance started or the statistics were last cleared. It is not a live list of blocked requests. A large number says little without an interval and a workload context. I take one snapshot before the period of interest and another afterward. I avoid clearing production counters because another diagnostic process can rely on them. If the server restarts between snapshots, the delta is invalid and the comparison must start again.

Wait time includes signal time, which is the time spent ready to run after a resource became available. The remainder is resource wait time. Separate them when CPU pressure is suspected. Some waits are background housekeeping and should be filtered for the question at hand, but keep the raw capture.

Store Wait Stats Snapshots in a Table

Create a small administrative table with a capture time and the three counters from the DMV. Keep capture time in UTC when you compare servers or correlate with application logs. The sample uses a temporary table for a single session. A permanent history table needs retention and a capture job. The temporary form is useful for learning the subtraction before scheduling it.

CREATE TABLE #WaitCapture
(
    capture_time datetime2(3) NOT NULL,
    wait_type nvarchar(60) NOT NULL,
    waiting_tasks_count bigint NOT NULL,
    wait_time_ms bigint NOT NULL,
    signal_wait_time_ms bigint NOT NULL
);
INSERT #WaitCapture
SELECT SYSUTCDATETIME(), wait_type, waiting_tasks_count,
       wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats;

Space Wait Stats Snapshots Across Real Work

Wait through a representative workload interval, then insert the same columns again. Do not compare a quiet night with a busy morning and treat the rate as a universal baseline. Record what happened during the window: a batch, a report run, a failover, or a user complaint. If you schedule captures, use a stable interval and retain the raw timestamps. A job that skips an execution changes the denominator. The calculation should use actual elapsed time, not an assumed number of minutes.

I check the SQL Server start time in sys.dm_os_sys_info beside both captures. If it changed, I discard the pair. A negative delta is another warning that counters reset or a capture is inconsistent. Neither should become a negative wait rate on a dashboard.

INSERT #WaitCapture
SELECT SYSUTCDATETIME(), wait_type, waiting_tasks_count,
       wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats;
SELECT sqlserver_start_time
FROM sys.dm_os_sys_info;

Subtract the Counters Between Wait Stats Snapshots

Pair the earlier and later row for each wait type. Subtract counts and milliseconds, then divide by the measured interval in seconds. Average wait is delta wait milliseconds divided by delta tasks, with a zero guard. Total wait seconds per elapsed second can exceed one because many sessions wait concurrently. That is normal. It is not a percentage of a single CPU. The example below uses the earliest and latest capture. For repeated scheduled captures, assign capture IDs and join adjacent IDs instead.

WITH samples AS
(
    SELECT wait_type, capture_time, waiting_tasks_count,
           wait_time_ms, signal_wait_time_ms,
           ROW_NUMBER() OVER (PARTITION BY wait_type ORDER BY capture_time) AS rn_first,
           ROW_NUMBER() OVER (PARTITION BY wait_type ORDER BY capture_time DESC) AS rn_last
    FROM #WaitCapture
),
delta AS
(
    SELECT b.wait_type,
           a.wait_time_ms - b.wait_time_ms AS wait_ms,
           a.waiting_tasks_count - b.waiting_tasks_count AS tasks,
           DATEDIFF_BIG(millisecond, b.capture_time, a.capture_time) / 1000.0 AS seconds_elapsed
    FROM samples AS a
    JOIN samples AS b ON a.wait_type = b.wait_type
    WHERE a.rn_last = 1 AND b.rn_first = 1
)
SELECT wait_type, wait_ms / 1000.0 / NULLIF(seconds_elapsed, 0) AS wait_seconds_per_second,
       wait_ms * 1.0 / NULLIF(tasks, 0) AS average_wait_ms
FROM delta
WHERE wait_ms >= 0 AND tasks > 0
  AND wait_type NOT IN (N'SLEEP_TASK', N'LAZYWRITER_SLEEP')
ORDER BY wait_ms DESC;

Filter Background Noise Deliberately

Build an exclusion list for known idle and background waits such as SLEEP_TASK and LAZYWRITER_SLEEP. Keep that list visible and reviewed. Do not paste a giant list from an old blog without checking current behavior and your workload. A wait that is usually harmless can still deserve attention in a specific scenario. I examine both the raw ranking and the filtered ranking. This prevents a filter from hiding a new signal simply because its name resembles something familiar.

Focus on meaningful changes, not only the top number. Compare to a healthy interval with similar traffic. A high PAGEIOLATCH_SH delta calls for file latency and query read checks. A high LCK_M_* delta calls for blockers, transaction scope, and isolation checks.

Add a Comparison Baseline

One interval tells you what waited. A second interval from a healthy period tells you what changed. Capture at the same cadence and comparable workload level. Group by wait type and compare both absolute wait seconds and wait seconds per elapsed second. A small wait can move sharply in relative terms while remaining operationally irrelevant. A large background wait can remain flat and need no action. I prefer a short table with current, baseline, and difference over a colorful chart that hides the denominator.

Save the server start time, SQL Server build, and workload note with each pair of wait stats snapshots. If the instance restarted, treat the new capture as a fresh series. If traffic doubled, normalize against request volume where possible. A wait rate per wall-clock second is useful, but it does not say how many user requests were served. I have seen a rising wait total accompany a healthy increase in work. Without throughput, the chart invites the wrong conclusion.

Keep Signal and Resource Time Separate

Signal wait is the portion after a worker has been notified that its resource is available but before it runs. Resource wait is total wait minus signal wait. A rising signal fraction alongside high CPU can point toward scheduler pressure. It does not by itself prove a CPU shortage. Check runnable tasks, CPU utilization, and active requests in the same interval. When resource time dominates, investigate the named resource: locks, IO, log, or memory grant. I calculate both from the captured columns instead of relying on one cumulative total.

Use the wait type as a branch in the investigation. PAGEIOLATCH_SH leads to file latency and read-volume checks. LCK_M_X leads to blockers and transaction scope. WRITELOG leads to log flush latency and write patterns. The branch matters because changing MAXDOP or adding memory to every wait problem is a quick way to create a second problem. I keep an action note beside each top wait: what evidence confirmed the cause, what change was made, and whether the next interval improved. That makes the snapshot table a useful diagnostic record rather than a pile of counters.

Tie Waits to Requests

Instance waits describe symptoms across the server, not the query that caused them. During the incident, query sys.dm_exec_requests for active wait_type, blocking_session_id, database_id, and the running text. A historical wait delta cannot identify one culprit after the fact. Query Store and application telemetry can add context, but they record different things. Do not turn a wait type into a one-line fix without checking that context.

What changed during your chosen window? If the answer is a report rollout or a surge in writes, test that hypothesis against plans, reads, blocking chains, and file latency. I use waits to decide where to look next, then gather the evidence that justifies a change.

Related reading on this blog: Wait Stats Collection Scripts : Updated March 2021 and AI Built SQL Server Wait Statistics Dashboard in Minutes.

A wait total is not a diagnosis, it is a clue tied to a time window.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.