How to Analyze Wait Stats in SQL Server 2022

A quick look at wait stats can reveal exactly where the server is struggling. SQL Server isn’t exactly what I would call “good” at describing it’s problems, nor is it self-healing.

I don’t want to memorize all 900+ wait types 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.

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.

Don’t fixate on the single top wait type. Total waits across categories often tell a very different story. Look at the sums, not just the peaks.

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