실시간 대기 이벤트, 특정 세션의 I/O 정보, 병목 현상 분석 모니터링 쿼리

✅ 1. 실시간 세션 대기 및 병목 확인 (현재 실행 중인 요청)

SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.cpu_time,
    r.total_elapsed_time,
    r.logical_reads,
    r.reads AS physical_reads,
    r.writes,
    r.blocking_session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    st.text AS sql_text
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

💡 이 쿼리는 현재 실행 중인 요청 중 대기 중인 요청, 실행 시간 오래 걸리는 쿼리, I/O가 많은 쿼리, 블로킹 여부를 파악할 수 있습니다.

---

✅ 2. 대기 유형별 실시간 통계 (Waits 분석)

SELECT
    wait_type,
    wait_time_ms / 1000.0 AS wait_time_sec,
    waiting_tasks_count,
    wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',
    'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE',
    'CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT',
    'BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT','CLR_AUTO_EVENT',
    'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT',
    'XE_DISPATCHER_JOIN','WAIT_XACT_OWN_TRANSACTION'
)
ORDER BY wait_time_ms DESC;

🔍 병목 유형(예: PAGEIOLATCH, CXPACKET, WRITELOG)을 파악하여 성능 저하 원인 분석에 사용됩니다.

---

✅ 3. I/O가 많은 세션 Top 10

SELECT TOP 10
    r.session_id,
    r.status,
    r.cpu_time,
    r.reads AS physical_reads,
    r.writes,
    r.logical_reads,
    r.total_elapsed_time,
    s.login_name,
    st.text AS sql_text
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id <> @@SPID
ORDER BY r.logical_reads DESC;

---

✅ 4. Blocking 세션 모니터링

SELECT
    blocking_session_id AS blocker,
    session_id AS blocked,
    wait_type,
    wait_time,
    wait_resource,
    st.text AS blocked_query
FROM sys.dm_exec_requests
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE blocking_session_id <> 0
ORDER BY wait_time DESC;

🧱 블로킹 트리 분석 시 유용.

---

✅ 5. 디스크 관련 병목: PAGEIOLATCH 대기 확인

SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGEIOLATCH%'
ORDER BY wait_time_ms DESC;

🧠 PAGEIOLATCH_SH, PAGEIOLATCH_EX는 디스크 I/O 병목을 나타냅니다.

---

✅ 6. 실시간 스핀락/잠금 병목 정보 (고급)

SELECT *
FROM sys.dm_os_spinlock_stats
WHERE spins > 0
ORDER BY spins DESC;

---

✅ 7. 활성 트랜잭션 및 잠금 확인

SELECT
    request_session_id AS session_id,
    resource_type,
    resource_database_id,
    resource_associated_entity_id,
    request_mode,
    request_status
FROM sys.dm_tran_locks
WHERE request_status = 'WAIT';

---

🔚 정리: 목적별 추천

목적	사용 쿼리
실시간 요청, 세션 분석	1번 쿼리
전체 대기 시간 분석	2번 쿼리
블로킹, 잠금	4번, 7번
I/O 성능 문제 분석	3번, 5번
시스템 병목 (대기 vs CPU 등)	2번, 6번