대기 통계: 서버가 무엇을 기다리는가

명령어

WITH w AS (
    SELECT wait_type, waiting_tasks_count,
           wait_time_ms / 1000.0                         AS wait_s,
           (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_s,
           signal_wait_time_ms / 1000.0                  AS signal_s
    FROM   sys.dm_os_wait_stats
    WHERE  waiting_tasks_count > 0
      AND  wait_type NOT LIKE N'SLEEP[_]%'
      AND  wait_type NOT IN (
           N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
           N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT',
           N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'FT_IFTS_SCHEDULER_IDLE_WAIT',
           N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_WORK_QUEUE', N'LAZYWRITER_SLEEP',
           N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PVS_PREALLOCATE', N'REQUEST_FOR_DEADLOCK_SEARCH',
           N'SNI_HTTP_ACCEPT', N'SOS_WORK_DISPATCHER', N'SP_SERVER_DIAGNOSTICS_SLEEP',
           N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'WAITFOR',
           N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT', N'XE_LIVE_TARGET_TVF', N'PREEMPTIVE_XE_DISPATCHER',
           N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE', N'QDS_SHUTDOWN_QUEUE',
           N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'PWAIT_ALL_COMPONENTS_INITIALIZED',
           N'PWAIT_EXTENSIBILITY_CLEANUP_TASK', N'STARTUP_DEPENDENCY_MANAGER', N'WAIT_XTP_CKPT_CLOSE')
)
SELECT TOP (20) wait_type, waiting_tasks_count,
       CAST(wait_s AS DECIMAL(18,1))                                AS wait_s,
       CAST(100.0 * wait_s / SUM(wait_s) OVER () AS DECIMAL(5,1))   AS pct,
       CAST(resource_s AS DECIMAL(18,1))                            AS resource_s,
       CAST(signal_s AS DECIMAL(18,1))                              AS signal_s,
       CAST(1000.0 * wait_s / waiting_tasks_count AS DECIMAL(18,2)) AS avg_wait_ms
FROM   w
ORDER BY wait_s DESC;

-- Values accumulate since:
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

-- User requests waiting right now:
SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms, wt.resource_description, wt.blocking_session_id
FROM   sys.dm_os_waiting_tasks wt
  JOIN sys.dm_exec_sessions s ON s.session_id = wt.session_id
WHERE  s.is_user_process = 1;

하는 일

무해한 백그라운드 대기를 제외하고 서버 전체 대기 시간을 대기 유형별로 비율 및 resource 대 signal(CPU 큐) 비중과 함께 분해한다. 지금 대기 중인 사용자 요청도 보여 준다.

사용 시점

병목(I/O, CPU, 락, 로그, 메모리, 병렬 처리)을 분류하는 성능 조사의 첫 단계로, 그리고 전후 baseline 비교를 위해.

주의

값은 재시작 이후 누적값이다. 현재 문제는 스냅샷을 두 번 떠서 차이를 비교한다. DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)는 서버 전체 카운터를 초기화하고 모니터링 도구를 망가뜨리므로 피한다. 해석: PAGEIOLATCH_* = 디스크 읽기, WRITELOG = 로그 디스크, LCK_M_* = 블로킹, CXPACKET/CXCONSUMER = 병렬 처리, RESOURCE_SEMAPHORE = memory grant, SOS_SCHEDULER_YIELD + 높은 signal = CPU 압박. 읽기 전용 쿼리이며 안전함.