How to Analyze Wait Stats in SQL Server 2022

sys.dm_os_wait_stats returns every wait the server has recorded, and most of what comes back is noise. The first time I queried it I got hundreds of rows, sorted by wait_time_ms, and the top of the list was things like SLEEP_TASK and LAZYWRITER_SLEEP, which are the server sitting around doing nothing. Sorting by the biggest number doesn’t tell you much on its own.

I don’t want to memorize every wait type in SQL Server, so anything that filters out the noise is a win. By querying sys.dm_os_wait_stats and grouping hundreds of wait types into a handful of meaningful categories, we can focus on the waits that matter and ignore the benign chatter.

I’d been saying “900 or so” for a while without checking, so I counted the rows in the wait types table and got 952. The documentation doesn’t give a total, so that’s my count of their list and it’ll move with each version, but it’s the right order of magnitude for why you want a query like this instead of a reference sheet.

One thing worth knowing before you read any of it. The documentation says these statistics “aren’t persisted after the database engine restarts, and all data is cumulative since the last time the statistics were reset or the database engine started.” So a server that was bounced last night is showing you last night, not last quarter. sys.dm_os_sys_info.sqlserver_start_time tells you which window you’re actually looking at, and I check it before I read anything into the numbers.

If you’ve just inherited a server and need to understand its history, keep the existing wait statistics intact. Only clear away the old data when you’re troubleshooting a live incident and need a fresh baseline. For those live investigations, run:

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO

The Script

The script below classifies each wait into CPU, Memory, Database Blocking, I/O or Tempdb Contention (and filters out the noise), then uses a single GROUPING SETS query to show both detailed and aggregated results in one pass.

-- Wait types by category.
WITH WaitCategories AS (
    SELECT
        wait_type,
        wait_time_ms,
        signal_wait_time_ms,
        waiting_tasks_count,
        CASE 
            -- Benign Wait Types
            WHEN wait_type IN (
                'BROKER_EVENTHANDLER', 'BROKER_RECEIVE_WAITFOR', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
                'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE', 'CHKPT', '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', 'EXECSYNC', 'FSAGENT',
                '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',
                'MEMORY_ALLOCATION_EXT', 'ONDEMAND_TASK_QUEUE', 'PARALLEL_REDO_DRAIN_WORKER',
                'PARALLEL_REDO_LOG_CACHE', 'PARALLEL_REDO_TRAN_LIST', 'PARALLEL_REDO_WORKER_SYNC',
                'PARALLEL_REDO_WORKER_WAIT_WORK', 'PREEMPTIVE_HADR_LEASE_MECHANISM',
                'PREEMPTIVE_SP_SERVER_DIAGNOSTICS', 'PREEMPTIVE_OS_LIBRARYOPS', 'PREEMPTIVE_OS_COMOPS',
                'PREEMPTIVE_OS_CRYPTOPS', 'PREEMPTIVE_OS_PIPEOPS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
                'PREEMPTIVE_OS_GENERICOPS', 'PREEMPTIVE_OS_VERIFYTRUST', 'PREEMPTIVE_OS_DELETESECURITYCONTEXT',
                'PREEMPTIVE_OS_REPORTEVENT', 'PREEMPTIVE_OS_FILEOPS', 'PREEMPTIVE_OS_DEVICEOPS',
                'PREEMPTIVE_OS_QUERYREGISTRY', 'PREEMPTIVE_OS_WRITEFILE', 'PREEMPTIVE_OS_WRITEFILEGATHER',
                'PREEMPTIVE_XE_CALLBACKEXECUTE', 'PREEMPTIVE_XE_DISPATCHER', 'PREEMPTIVE_XE_GETTARGETSTATE',
                'PREEMPTIVE_XE_SESSIONCOMMIT', 'PREEMPTIVE_XE_TARGETINIT', 'PREEMPTIVE_XE_TARGETFINALIZE',
                'POPULATE_LOCK_ORDINALS', 'PWAIT_ALL_COMPONENTS_INITIALIZED',
                'PWAIT_DIRECTLOGCONSUMER_GETNEXT', 'PWAIT_EXTENSIBILITY_CLEANUP_TASK',
                'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', 'QDS_ASYNC_QUEUE',
                'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', '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', 'SOS_WORK_DISPATCHER', 'SP_SERVER_DIAGNOSTICS_SLEEP',
                'SOS_WORKER_MIGRATION', 'VDI_CLIENT_OTHER', 'SQLTRACE_BUFFER_FLUSH',
                'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'SQLTRACE_WAIT_ENTRIES', 'STARTUP_DEPENDENCY_MANAGER',
                'TRACEWRITE', 'WAIT_FOR_RESULTS', 'WAITFOR', 'WAITFOR_TASKSHUTDOWN', 'WAIT_XTP_HOST_WAIT',
                'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', 'WAIT_XTP_CKPT_CLOSE', 'WAIT_XTP_RECOVERY',
                'XE_BUFFERMGR_ALLPROCESSED_EVENT', 'XE_DISPATCHER_JOIN', 'XE_DISPATCHER_WAIT',
                'XE_LIVE_TARGET_TVF', 'XE_TIMER_EVENT')
                THEN 'Benign'
            -- CPU Wait Types
            WHEN wait_type IN (
                'CXPACKET', 'CXCONSUMER', 'SOS_SCHEDULER_YIELD', 'THREADPOOL')
                THEN 'CPU'
            -- Memory Wait Types
            WHEN wait_type IN (
                'MEMORY_GRANT_UPDATE', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE')
                THEN 'Memory'
            -- Database Blocking Wait Types
            WHEN wait_type LIKE 'LCK_M_%' THEN 'Database Blocking'
            -- I/O Wait Types
            WHEN wait_type IN (
                'WRITELOG', 'BACKUPIO', 'ASYNC_IO_COMPLETION', 'PAGEIOLATCH_SH',
                'PAGEIOLATCH_UP', 'PAGEIOLATCH_EX', 'IO_COMPLETION', 'WRITE_COMPLETION', 'LOGBUFFER',
                'LOGMGR', 'LOGMGR_RESERVE_APPEND', 'DISKIO_SUSPEND')
                THEN 'I/O'
            -- Tempdb Contention Wait Types
            WHEN wait_type IN (
                'PAGELATCH_EX', 'LATCH_EX', 'PAGELATCH_SH', 'LATCH_SH', 'PAGELATCH_UP')
                THEN 'Tempdb Contention'
            -- Other Wait Types
            ELSE 'Other'
        END AS WaitCategory
    FROM sys.dm_os_wait_stats
)
-- Combine detailed and aggregated results using GROUPING SETS
SELECT
    CASE
        WHEN GROUPING(wait_type) = 0 THEN 'Detailed'
        ELSE 'Aggregated'
    END AS ResultType,
    WaitCategory,
    wait_type,
    SUM(wait_time_ms) AS wait_time_ms,
    SUM(signal_wait_time_ms) AS signal_wait_time_ms,
    SUM(waiting_tasks_count) AS waiting_tasks_count
FROM WaitCategories
WHERE 1=1
    AND WaitCategory NOT IN ('Benign', 'Other') -- Exclude 'Benign' and 'Other' categories
    AND waiting_tasks_count > 0
GROUP BY
    GROUPING SETS (
        (WaitCategory, wait_type), -- Detailed results
        (WaitCategory)             -- Aggregated results
    )
ORDER BY
    CASE
        WHEN GROUPING(wait_type) = 1 THEN 1 -- Place aggregated results at the bottom
        ELSE 0
    END,
    wait_time_ms DESC;

How to Read the Results

Once you run this, the rows labeled Detailed show every wait type you’re tracking, and the Aggregated rows summarize each category. Use the detailed output to drill in, and rely on the aggregates at the bottom for a high-level view of where SQL Server is spending most of its time.

The reason I built the aggregate rows at all is that the single top wait type kept sending me the wrong way. One WRITELOG sitting at the top of the list looks like a disk problem, but if the four I/O waits below it add up to less than the blocking category underneath, the disk isn’t the thing to go fix. Reading the category sums instead of the peaks is what changed how I use this.

I’ve left Other filtered out along with Benign, which is a decision I go back and forth on. Anything I haven’t explicitly categorized lands in Other, so filtering it means a wait type I’ve never seen before is invisible to me here. If a server looks fine by this query and clearly isn’t, dropping AND WaitCategory NOT IN ('Benign', 'Other') down to just 'Benign' is the first thing I’d change.

In case you were wondering, I get the benign wait_types from Glenn Berry’s DMVs, query 42.

The category buckets are mine, and the one I’d argue with most is putting PAGELATCH_* under Tempdb Contention. That label is right for the classic case of allocation bitmap contention in tempdb, but the same wait shows up on an insert hotspot in a user database, where a lot of threads insert small rows onto the same page at the end of a clustered index on an identity column. Microsoft’s page on resolving PAGELATCH_EX contention is about that second case and it isn’t a tempdb problem at all. So if this query points you at Tempdb Contention, check which database the latches are actually in before you go add tempdb files.