【SQL server】デッドロックの調査方法

デッドロック発生に際し、分析時の確認の仕方について紹介します。

エラーログやSQL Plofilerなどで調査可能ですが、個人的には拡張イベントがお勧めです。デフォルト設定であれば、拡張イベントの「system_health」でデッドロック情報は取得しているので、事後で確認する際に便利です。

拡張イベントで採取されたデータはデフォルトであれば以下のようにログファイル格納先と同様の場所にありますので、ダブルクリックして開きます。
尚、ファイル形式での拡張イベント「system_health」は、5MB：4世代しかデフォルトで残らないため、事後で確認可能とはいえデッドロック発生後は速やかに本ファイルを退避しておく方がよいでしょう。

上部のメニューで「フィルター」を選択し、以下のようにフィルタします。

フィールド	演算子	値
name	=	xml_deadlock_report

表示されたイベントのデッドロックタブを開いてデッドロックの詳細を確認します。ここではデッドロックとなったページ情報等(※1)が出力され、原因となったインデックスやクエリが表示できます。
※1 ページに格納されている情報を確認したい場合は、【SQL server】ロックの種類とページ情報 - 小物SEのメモ帳を参照

尚、クエリの表示はデッドロックグラフにマウスを当てると表示されます。

また、詳細タブに出力されているレコードをダブルクリックすることで、デッドロック時の詳細情報を確認することができます。
こちらにはsql_handleも残っているので、キャッシュ上にデータが残っていればこのsql_handleからデッドロック時の実行プランを確認できるかと思います。

拡張イベント「system_health」のring buffer上に残っているものは以下クエリで確認することも可能です。

SELECT
		XEventData.XEvent.query('.')						AS [XEvent]
	FROM (SELECT
						CAST(target_data AS XML)		AS [TargetData]
			FROM			[sys].[dm_xe_session_targets]		AS [targets]	WITH(NOLOCK)
			INNER JOIN 	[sys].[dm_xe_sessions]				AS [sessions]   WITH(NOLOCK)
			ON 			[sessions].[address] = [targets].[event_session_address]
			WHERE		[sessions].[name]		= N'system_health'
			AND			[targets].[target_name] = N'ring_buffer'
		) AS [system_health]
	CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)

デッドロックの発生箇所や原因となるクエリが特定できたら、次のアクションとしては対象クエリの実行プランや処理順序を見直してデッドロックの発生率を減少をしていきましょう。
デッドロックあるあるとしては、実行プランを確認したら暗黙型変換(CONVERT_IMPLICIT)が発生しておりインデックスがきいていなかったため、不要にページロック獲得をしていた等があるかと思います。decimal/numeric、varchar/nvarchar等は基本的なことですが、注意しましょう。

◆参考情報
・system_health セッションの使用
https://msdn.microsoft.com/ja-jp/library/ff877955.aspx

・暗黙型変換について
https://blogs.msdn.microsoft.com/jpsql/2011/02/01/dosdonts-2/