system_health セッションから過去のデッドロックを読み取る (デッドロックグラフ)

コマンド

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)、犠牲プロセス、参加者数、データベース、完全なデッドロックグラフ XML を返す。

使用する場面

アプリケーションがエラー 1205 を報告した場合、デッドロックの頻度と関与するオブジェクトを分析する場合、トレースフラグ 1222 のないサーバーで。

注意

system_health ファイルはロールオーバーするため、高負荷サーバーでは古いデッドロックが消えている場合がある。恒久的なアーカイブには専用の XE セッションを設定する。多数のファイルの読み取りは CPU を消費するため、業務時間中は TOP を維持する。イベントは遅延して書き込まれるため、新しいデッドロックは数秒後に表示される。読み取り専用のため安全。