Wait Stats Summary: Every Wait on One Page

This wait stats summary puts every wait from the series on one page: what it means and the first fix. It also holds the one lesson I’d keep from all 28 nights: know what normal looks like on your server.

This post is part of my wait stats series, told as one story at the Clipboard Diner. Every post is listed in the series guide.

Night 28 at the Clipboard Diner

The chart was still on the fridge, a little crooked, next to the postcard. It was a Tuesday. A light rain came and went, and the highway smelled of wet asphalt and coffee.

Last night’s Monday bus had gone without a hitch. Dee had split the party evenly across the four cooks, and nobody got stuck with all the blueberry pancakes. Tonight the party in booth 7 paid at 7:30 and left. Pat’s pen kept up with every sale, and Quinn carried every plate while it was still hot.

At 7:52 a stranger in a gray raincoat took the last stool at the counter. There was no notebook in sight. The stranger ordered the Garden Hash and black coffee, and Casey didn’t need to be told.

Casey clicked the stopwatch out of habit. The potato bin on the prep counter was full. The ticket sat on the rail for one minute, because the quilting circle’s twelve plates were still going out. Casey watched that minute pass and didn’t move. One minute on the rail on a Tuesday at eight was normal, and four weeks of clipboard pages said so.

The jukebox played its one song. The critic ate slowly, had a slice of cherry pie, and paid Pat in cash. Under the coffee cup was a folded napkin.

Pat handed it to Casey without a word. It held one line in blue ink: Nothing at the Clipboard Diner waits longer than it should. Casey read it twice, the way Casey had read the postcard four weeks before. Casey pinned the postcard back above the register, with the napkin beside it. Then Casey wrote one last line: Night 28. Cooking: 4. Waiting: 1. A normal Tuesday.

What 28 Nights Taught Casey

That’s what knowing your normal looks like. The critic didn’t find a kitchen with no waiting, only waits with a reason that never ran long. A busy SQL Server waits all day too. The trouble is a wait that grows past the usual size you know.

Every Wait on One Page

Know Your Normal

In my health checks, I start with last month’s top wait, not today’s. A server with CXPACKET on top every week for two years is telling you about its workload. That’s the diner’s jukebox, playing the same song every night. A server where WRITELOG jumped to the top on Monday is telling you something changed.

Normal or a Problem?

See It on Your Server

This query gives a rough first view of normal: seconds per hour of uptime for each real wait, dated. It skips the 87 harmless waits from Harmless Wait Stats.

-- The harmless list: background housekeeping and deliberate pauses, not user work.
-- why: sleep = sleeps until needed, idle = waits for background work, startup = only at startup,
-- waitfor = asked to wait, broker/ag/trace/qstore/fulltext/xtp/clr = feature housekeeping,
-- internal = internal background task.
DECLARE @harmless TABLE (wait_type nvarchar(60) PRIMARY KEY, why varchar(10) NOT NULL);
INSERT @harmless (wait_type, why) VALUES
    (N'LAZYWRITER_SLEEP', 'sleep'), (N'SLEEP_BPOOL_FLUSH', 'sleep'), (N'SLEEP_TASK', 'sleep'),
    (N'SP_SERVER_DIAGNOSTICS_SLEEP', 'sleep'), (N'CHECKPOINT_QUEUE', 'idle'),
    (N'DIRTY_PAGE_POLL', 'idle'), (N'DISPATCHER_QUEUE_SEMAPHORE', 'idle'),
    (N'KSOURCE_WAKEUP', 'idle'), (N'LOGMGR_QUEUE', 'idle'), (N'ONDEMAND_TASK_QUEUE', 'idle'),
    (N'PREEMPTIVE_SP_SERVER_DIAGNOSTICS', 'idle'), (N'REQUEST_FOR_DEADLOCK_SEARCH', 'idle'),
    (N'RESOURCE_QUEUE', 'idle'), (N'SERVER_IDLE_CHECK', 'idle'), (N'SNI_HTTP_ACCEPT', 'idle'),
    (N'SOS_WORK_DISPATCHER', 'idle'), (N'UCS_SESSION_REGISTRATION', 'idle'),
    (N'VDI_CLIENT_OTHER', 'idle'), (N'CHKPT', 'startup'),
    (N'PWAIT_ALL_COMPONENTS_INITIALIZED', 'startup'), (N'SLEEP_DBSTARTUP', 'startup'),
    (N'SLEEP_DCOMSTARTUP', 'startup'), (N'SLEEP_MASTERDBREADY', 'startup'),
    (N'SLEEP_MASTERMDREADY', 'startup'), (N'SLEEP_MASTERUPGRADED', 'startup'),
    (N'SLEEP_MSDBSTARTUP', 'startup'), (N'SLEEP_PHYSMASTERDBREADY', 'startup'),
    (N'SLEEP_SYSTEMTASK', 'startup'), (N'SLEEP_TEMPDBSTARTUP', 'startup'),
    (N'STARTUP_DEPENDENCY_MANAGER', 'startup'), (N'WAITFOR', 'waitfor'),
    (N'WAITFOR_TASKSHUTDOWN', 'waitfor'), (N'WAIT_FOR_RESULTS', 'waitfor'),
    (N'BROKER_EVENTHANDLER', 'broker'), (N'BROKER_TASK_STOP', 'broker'),
    (N'BROKER_TO_FLUSH', 'broker'), (N'BROKER_TRANSMITTER', 'broker'), (N'DBMIRRORING_CMD', 'ag'),
    (N'DBMIRROR_DBM_EVENT', 'ag'), (N'DBMIRROR_DBM_MUTEX', 'ag'), (N'DBMIRROR_EVENTS_QUEUE', 'ag'),
    (N'DBMIRROR_WORKER_QUEUE', 'ag'), (N'HADR_CLUSAPI_CALL', 'ag'),
    (N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'ag'), (N'HADR_LOGCAPTURE_WAIT', 'ag'),
    (N'HADR_NOTIFICATION_DEQUEUE', 'ag'), (N'HADR_TIMER_TASK', 'ag'), (N'HADR_WORK_QUEUE', 'ag'),
    (N'PARALLEL_REDO_DRAIN_WORKER', 'ag'), (N'PARALLEL_REDO_LOG_CACHE', 'ag'),
    (N'PARALLEL_REDO_TRAN_LIST', 'ag'), (N'PARALLEL_REDO_WORKER_SYNC', 'ag'),
    (N'PARALLEL_REDO_WORKER_WAIT_WORK', 'ag'), (N'PREEMPTIVE_HADR_LEASE_MECHANISM', 'ag'),
    (N'REDO_THREAD_PENDING_WORK', 'ag'), (N'PREEMPTIVE_XE_CALLBACKEXECUTE', 'trace'),
    (N'PREEMPTIVE_XE_DISPATCHER', 'trace'), (N'PREEMPTIVE_XE_GETTARGETSTATE', 'trace'),
    (N'PREEMPTIVE_XE_SESSIONCOMMIT', 'trace'), (N'PREEMPTIVE_XE_TARGETFINALIZE', 'trace'),
    (N'PREEMPTIVE_XE_TARGETINIT', 'trace'), (N'SQLTRACE_BUFFER_FLUSH', 'trace'),
    (N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'trace'), (N'SQLTRACE_WAIT_ENTRIES', 'trace'),
    (N'XE_BUFFERMGR_ALLPROCESSED_EVENT', 'trace'), (N'XE_DISPATCHER_JOIN', 'trace'),
    (N'XE_DISPATCHER_WAIT', 'trace'), (N'XE_LIVE_TARGET_TVF', 'trace'),
    (N'XE_TIMER_EVENT', 'trace'), (N'QDS_ASYNC_QUEUE', 'qstore'),
    (N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', 'qstore'),
    (N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', 'qstore'), (N'QDS_SHUTDOWN_QUEUE', 'qstore'),
    (N'FT_IFTSHC_MUTEX', 'fulltext'), (N'FT_IFTSISM_MUTEX', 'fulltext'),
    (N'FT_IFTS_SCHEDULER_IDLE_WAIT', 'fulltext'), (N'WAIT_XTP_CKPT_CLOSE', 'xtp'),
    (N'WAIT_XTP_HOST_WAIT', 'xtp'), (N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', 'xtp'),
    (N'WAIT_XTP_RECOVERY', 'xtp'), (N'CLR_AUTO_EVENT', 'clr'),
    (N'AZURE_IMDS_VERSIONS', 'internal'), (N'POPULATE_LOCK_ORDINALS', 'internal'),
    (N'PVS_PREALLOCATE', 'internal'), (N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', 'internal'),
    (N'PWAIT_EXTENSIBILITY_CLEANUP_TASK', 'internal'), (N'SOS_WORKER_MIGRATION', 'internal');

-- Your normal in one dated snapshot: real waits per hour of uptime.
-- Run it in the same batch as the list above, and keep every week's copy.
SELECT TOP (10)
       CAST(SYSDATETIME() AS date) AS captured_on,
       up.started_at,
       w.wait_type,
       CAST(w.wait_time_ms / 1000.0 / up.hours_up AS decimal(18,1)) AS sec_per_hour,
       CAST(1.0 * w.wait_time_ms / NULLIF(w.waiting_tasks_count, 0) AS decimal(18,1)) AS avg_wait_ms,
       CAST(100.0 * w.signal_wait_time_ms / NULLIF(w.wait_time_ms, 0) AS decimal(5,1)) AS signal_pct,
       CAST(up.hours_up AS decimal(18,1)) AS hours_up
FROM sys.dm_os_wait_stats AS w
CROSS APPLY (SELECT i.sqlserver_start_time AS started_at,
                    NULLIF(DATEDIFF(MINUTE, i.sqlserver_start_time, SYSDATETIME()) / 60.0, 0) AS hours_up
             FROM sys.dm_os_sys_info AS i) AS up
WHERE w.waiting_tasks_count > 0
  AND NOT EXISTS (SELECT 1 FROM @harmless AS h WHERE h.wait_type = w.wait_type COLLATE DATABASE_DEFAULT)
ORDER BY w.wait_time_ms DESC;

A wait whose sec_per_hour jumped since last week’s copy comes first. The shortcut assumes nobody cleared the wait stats since started_at, because a clear makes sec_per_hour read too low. After a clear, or for more detail, measure one busy hour with two snapshots, as Wait Stats Over Time shows.

Fix It

You could say a monitoring tool already keeps a baseline. Fair point, but plenty of shops have the tool and have never looked at a normal week.

1. Capture a baseline today, before anything is wrong (see capture a wait stats baseline before changing anything).
2. Capture it again every week, at the same busy hour, with the same filter.
3. When users complain, compare against the baseline before you change anything.
4. Take the wait that moved to the Wait Stats Troubleshooting chart: one question, one fix, then measure again.

New in SQL Server 2022 and 2025

SQL Server 2025 brought optimized locking and its XACT waits, DOP feedback on by default, and ZSTD backup compression. It also added Resource Governor tempdb space limits and optimized sp_executesql.

SQL Server 2022 brought faster log growth, less tempdb latch contention and Query Store on by default. It also improved memory grant feedback, and both feedback features are Enterprise only. None of it changes the question you ask.

The Last Page

At 4 AM the night baker woke up, right on time, and the smell of bread filled the diner. Out front, the neon sign still flickered on one letter. Casey had meant to fix it for four weeks, and finally decided it was part of the place. Some waits are like that. You learn their size, you write them down, and you leave them alone.

Casey turned the clipboard to a clean page. The critic was gone, but Wednesday was coming, and Wednesday has its own normal. Thank you for spending these nights at the diner with me. When the next slow morning comes, ask the question Casey learned to ask: what are we waiting for?

Related Reading

• SQL Server 2025 Cheat Sheet: Every Query Tested

A healthy server is not one with no waits, it is one whose waits you know by heart.

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