SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

System Health のデッドロック情報からデッドロックグラフを作成する

leave a comment

意外とマイナーな情報なのかなと思ったので少しまとめておきたいと思います。

SQL Server 2008 以降は拡張イベントでシステムヘルス (System Health) の情報が取得されており、その中にはデッドロックンの情報も取得されています。

取得されている情報は system_health セッションの使用 が参考になるかと。

システムヘルスの情報からデッドロックを取得するための情報として、How To Monitor Deadlocks in SQL Server があります。

ここで紹介されている以下のクエリを実行するとデッドロックの情報をシステムヘルスから取得することができます。

WITH SystemHealth
AS (
SELECT CAST(target_data as xml) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s
ON s.address = st.event_session_address
WHERE name = ‘system_health’
AND st.target_name = ‘ring_buffer’)

SELECT XEventData.XEvent.query(‘(data/value/deadlock)[1]’) AS DeadLockGraph
FROM SystemHealth
CROSS APPLY TargetData.nodes(‘/RingBufferTarget/event’) AS XEventData (XEvent)
WHERE XEventData.XEvent.value(‘@name’,’varchar(4000)’) = ‘xml_deadlock_report’

 

実行すると以下のように XML 形式のデータを取得することができます。
image

XML なのでクリックすると以下のように表示することも。
image

ここから情報を追うことも可能なのですが、これだと少し解析がしづらいですよね。

そんな時はこの XML をファイルに保存して、拡張子を xdl に変更します。
image

これをダブルクリックすると、SSMS で開くことができるのですが、この状態では以下のようなエラーとなります。
image

ここでもう一つだけ手を加えます。
XML のルートを

<deadlock-list>

</deadlock-list>

で囲むようにします。

image
image

このように編集したファイルをダブルクリックして開くと、以下のようにプロファイラで取得することができるデッドロックグラフを作ることができます。
# 拡張イベントでも取得できますが。
image

デッドロックの原因が複雑化した際などは XML データの情報が大きくなることがありますので、その場合は SSMS  [ツール] → [オプション] をクックし、
image

[クエリ結果] → [SQL Server] → [結果をグリッドに表示] の [XML データ] のサイズを変更すると途中で切れることなく情報を取得することができるようになります。
# 設定を変更した後は一度クエリエディタを開きなおす必要があります。

image

ちょっとした小ネタでした。
# もっとさくっとデッドロックグラフがシステムヘルスから作れた気がするのですが忘れました…。

追記

拡張イベントをファイルに出力している場合はデッドロックタブから見れました。
というか自分で書いていました…。
ブロッキングとデッドロックを後追い確認する方法

SQL Server 2008 R2 まではリングバッファとして System Health をメモリに格納していると思いますので、この場合は本投稿の情報で確認をするとよいのかもしれないですね。

image

Written by masayuki.ozawa

6月 21st, 2013 at 10:47 pm

Posted in SQL Server

Tagged with

Leave a Reply

*