How to Analyze Wait Stats in SQL Server 2022

Analyzing wait statistics quickly reveals where the server is experiencing performance issues. SQL Server is not particularly effective at communicating its problems, nor does it self-correct.

Memorizing all 900+ wait types in SQL Server is impractical, so filtering out irrelevant noise is valuable. By querying sys.dm_os_wait_stats and categorizing hundreds of wait types into meaningful groups, we can focus on significant waits and ignore benign background activity.

When inheriting a server, preserve existing wait statistics to understand historical performance patterns. Clear old data only when troubleshooting active incidents and requiring a fresh baseline. For live investigations, execute:

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

The Script

The script below categorizes each wait into CPU, Memory, Database Blocking, I/O, or Tempdb Contention groups while filtering noise, then uses a single GROUPING SETS query to display both detailed and aggregated results efficiently.

-- 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

After execution, rows labeled `Detailed` display individual wait types being tracked, while `Aggregated` rows provide category summaries. Use detailed output for investigation and aggregated results for a high-level overview of where SQL Server spends most time.

Avoid focusing solely on the top wait type. Total waits across categories often provide different insights than individual peaks. Analyze the aggregates, not just the maximum values.

For reference, the benign wait_types list is sourced from [Glenn Berry's DMVs](https://www.dropbox.com/scl/fi/lpo8vfr7qext2lflkqa3j/SQL-Server-2022-Diagnostic-Information-Queries.sql?rlkey=57bn3xd1sxw9sq2m17woiqmj4&e=1&dl=0), query 42.