lock_deadlock 이벤트로 sql_text 찾기

lock_deadlock 이벤트에서 sql_text를 찾기 위해서는 SQL Server에서 확장 이벤트(Extended Events)를 활용하는 방법이 가장 정확하고 실용적입니다. 여기서는 SQL Server에서 Deadlock 발생 시 관련 SQL 텍스트(sql_text)를 추적하고 조회하는 방법을 설명합니다.

---

🔍 Deadlock 발생 시 SQL 텍스트 찾는 방법

방법 1: Extended Events 세션 사용

1. Deadlock 추적용 Extended Event 세션 생성

CREATE EVENT SESSION [Deadlock_Capture] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=5 SECONDS);
GO

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

2. Deadlock 발생 시 XML 보고서 확인

-- 가장 최근 deadlock xml 보고서 조회
SELECT 
    XEvent.value('(event/data/value)[1]', 'xml') AS DeadlockReport
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 = 'Deadlock_Capture'
       AND t.target_name = 'ring_buffer') AS Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData(XEvent)
WHERE XEvent.value('@name', 'varchar(100)') = 'xml_deadlock_report';

3. Deadlock XML에서 sql_text 추출

XML을 열어 보면 <process-list> 아래에 <inputbuf> 또는 <executionStack> 태그가 있으며, 여기에 해당 시점의 SQL 문장이 기록되어 있습니다. 예:

<process id="..." ...>
    <executionStack>
        <frame ...>SELECT * FROM Orders WHERE ...</frame>
    </executionStack>
    <inputbuf>SELECT * FROM Orders WHERE ...</inputbuf>
</process>

---

방법 2: System Health 세션에서 Deadlock 조회 (자동 수집)

SQL Server는 기본적으로 system_health 세션에서 deadlock 정보를 수집합니다. 이 정보를 활용할 수도 있습니다.

-- 최근 system_health에서 deadlock 정보 가져오기
SELECT 
    XEvent.value('(event/data/value)[1]', 'xml') AS DeadlockReport
FROM 
    (SELECT CAST(target_data AS XML) AS TargetData
     FROM sys.dm_xe_session_targets t
     JOIN sys.dm_xe_sessions s 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') AS XEventData(XEvent)
WHERE XEvent.value('@name', 'varchar(100)') = 'xml_deadlock_report';

---

🛠 추가 팁

• XML 결과를 SSMS에서 열고 XQuery로 파싱해서 inputbuf나 frame 노드를 정렬하거나 필터링할 수 있습니다.
• deadlock 관련 세션은 성능에 크게 영향을 주지 않으며, 운영 환경에서도 안전하게 사용할 수 있습니다.

---