system_health 세션에서 과거 데드락 읽기 (deadlock graph)

명령어

SET QUOTED_IDENTIFIER ON;  -- required for XML methods (sqlcmd/Agent default is OFF)
WITH dl AS (
    SELECT CAST(event_data AS XML) AS x
    FROM   sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
    WHERE  object_name = N'xml_deadlock_report'
)
SELECT TOP (20)
       x.value('(event/@timestamp)[1]', 'datetime2')                                          AS utc_time,
       x.value('(event/data/value/deadlock/victim-list/victimProcess/@id)[1]', 'varchar(50)')  AS victim,
       x.value('count(event/data/value/deadlock/process-list/process)', 'int')                 AS process_count,
       x.value('(event/data/value/deadlock/process-list/process/@currentdbname)[1]', 'sysname') AS db,
       x.query('event/data/value/deadlock')                                                    AS deadlock_graph
FROM   dl
ORDER BY utc_time DESC;

하는 일

system_health Extended Events 세션(기본 활성화)의 event_file 대상에서 xml_deadlock_report 이벤트를 읽는다. 각 데드락에 대해 시각(UTC), 희생 프로세스, 참여자 수, 데이터베이스, 전체 deadlock graph XML을 반환한다.

사용 시점

애플리케이션이 1205 오류를 보고할 때, 데드락 빈도와 관련 객체를 분석할 때, trace flag 1222가 없는 서버에서.

주의

system_health 파일은 순환되므로 바쁜 서버에서는 오래된 데드락이 사라질 수 있다. 영구 보관에는 전용 XE 세션을 설정한다. 많은 파일을 읽으면 CPU 비용이 들므로 업무 시간에는 TOP을 유지한다. 이벤트는 지연되어 기록되므로 새 데드락은 몇 초 후에 나타난다. 읽기 전용이며 안전함.