DEAD LOCK을 모니터링할 수 있는 쿼리

MSSQL에서 Deadlock(교착 상태) 을 모니터링하거나 탐지하려면 다음과 같은 방법들을 사용할 수 있습니다:

---

✅ 1. Deadlock 모니터링용 기본 DMV 쿼리

-- 현재 블로킹 또는 교착 상태 의심 세션 조회
SELECT
    r.session_id,
    r.status,
    r.command,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS query_text,
    s.host_name,
    s.program_name,
    s.login_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;

🟡 이 쿼리는 현재 블로킹 상태를 보여주며, blocking_session_id를 통해 잠재적 deadlock을 추적할 수 있습니다.

---

✅ 2. 최근 Deadlock 이벤트 조회 (Extended Events 사용)

SQL Server 2012 이상에서는 Extended Events에서 deadlock 이벤트를 확인할 수 있습니다.

-- 최근 Deadlock 이벤트 보기 (system_health 세션 활용)
SELECT 
    XEvent.value('(event/@timestamp)[1]', 'datetime') AS [Deadlock Time],
    XEvent.value('(event/data/value)[1]', 'varchar(max)') AS [Deadlock Graph XML]
FROM (
    SELECT CAST(target_data AS XML) AS TargetData
    FROM sys.dm_xe_sessions AS s
    JOIN sys.dm_xe_session_targets AS t 
        ON s.address = t.event_session_address
    WHERE s.name = 'system_health'
      AND t.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
ORDER BY [Deadlock Time] DESC;

🛠️ 참고: 위 쿼리는 최근 발생한 Deadlock의 XML 형식 그래프를 반환합니다. SSMS에서 클릭하면 Deadlock Viewer로 확인할 수 있습니다.

---

✅ 3. Deadlock 알림 자동화 (이벤트 세션 생성)

더 명확한 모니터링을 위해 별도의 Extended Events 세션을 생성할 수도 있습니다.

-- Deadlock 전용 이벤트 세션 생성
CREATE EVENT SESSION [DeadlockMonitor] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE = ON);
GO

-- 세션 시작
ALTER EVENT SESSION [DeadlockMonitor] ON SERVER STATE = START;

Deadlock 발생 시 로그를 다음과 같이 조회할 수 있습니다:

-- DeadlockMonitor 세션에서 Deadlock 그래프 조회
SELECT 
    XEvent.value('(event/@timestamp)[1]', 'datetime') AS [DeadlockTime],
    XEvent.value('(event/data/value)[1]', 'varchar(max)') AS [DeadlockGraphXML]
FROM (
    SELECT CAST(target_data AS XML) AS TargetData
    FROM sys.dm_xe_sessions AS s
    JOIN sys.dm_xe_session_targets AS t 
        ON s.address = t.event_session_address
    WHERE s.name = 'DeadlockMonitor'
      AND t.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
ORDER BY [DeadlockTime] DESC;

---

✅ 4. Deadlock 발생 여부 빠르게 확인 (에러 로그)

EXEC xp_readerrorlog 0, 1, 'deadlock', NULL, NULL, NULL, 'DESC';

📌 SQL Server 에러 로그에 남은 Deadlock 메시지를 조회합니다.

---

📌 정리

목적	사용 쿼리/방법
현재 블로킹 상태 확인	sys.dm_exec_requests + blocking_session_id
과거 Deadlock 확인	Extended Events (system_health)
실시간 Deadlock 모니터링	사용자 정의 Extended Event 세션
로그로 Deadlock 추적	xp_readerrorlog 사용