SQL Server を実行している Amazon RDS DB インスタンスのデッドロックに関する情報をキャプチャするにはどうすればよいですか?

解決方法

デッドロックは、複数のセッションが、それぞれにロックされたリソースがある状態で、他のセッションのロックされたリソースにアクセスしようとしたときに発生します。その結果が「円形チェーン」です。この場合、セッションは次の時点まで実行されなくなります。

• セッションの 1 つがロックを解除します。
• これにより、他のセッションがロックされたリソースにアクセスできるようになります。

SQL Server のデッドロックディテクタは、リソースベースまたはコストベースのメカニズムを使用してセッションの 1 つを終了することで、この状況を解決します。その後、ディテクタはこのセッションに関連するすべての変更をロールバックします。セッションの 1 つが終了すると、セッションが保持しているロックが解放され、他のセッションは続行が許可されます。詳細については、デッドロックに関する Microsoft のドキュメントを参照してください。

次のいずれかの方法を使用して、DB インスタンスのデッドロックイベントに関する情報を取得できます。

トレースフラグを有効にする

デッドロックトレースフラグ (1204,1222) を有効にできます。トレースフラグは、SQL Server エラーログに含まれているデッドロック情報をキャプチャして、追加のモニタリングなど、SQL Server の動作をカスタマイズするために使用されます。

• トレースフラグ 1204 は、デッドロックに関係する各ノードに関するデッドロック情報を提供します。
• トレースフラグ 1222 は、XML 形式のトレースフラグ 1204 よりも詳細なデッドロック情報を提供します。

両方のトレースフラグを有効にして、デッドロックイベントごとに 2 つの異なる表現を取得できます。これをセットアップするには、Amazon RDS SQL Server DB インスタンスでデッドロックイベントが発生したときに通知を受け取るにはどうすればよいですか? を参照してください。 トレースフラグを有効にすると、SQL Server エラーログでデッドロックイベントの詳細を確認できます。

system_health セッションを使用する

拡張イベントは、SQL Server の問題をモニタリングおよびトラブルシューティングするためのデータを収集するのに役立つ軽量のパフォーマンスモニタリングシステムです。system_health 拡張イベントセッションは SQL Server に含まれており、デフォルトでアクティブ化されています。SQL Server データベースエンジンが起動し、基本的なサーバーヘルス情報を収集すると、セッションが自動的に開始されます。この情報を使用して、パフォーマンスの問題をトラブルシューティングし、データベースエンジン内のデッドロックをモニタリングできます。

system_health 拡張イベントセッションでは、イベントファイルとリングバッファという 2 つのターゲットを使用してデータを保存します。

デフォルトでは、個々のファイルのサイズは 5 MB で、最大ロールオーバーファイル数は 4 つです。これにより、最大 20 MB の system_health 拡張イベントデータが追加されます。SQL Server 2016、2017、および 2019 では、個々のファイルのサイズが 100 MB になり、ファイルの最大数が 10 個に増えました。これにより、最大で 1 GB のデータが追加されます。

リングバッファは、先入れ先出し (FIFO) ベースでデータを保存するメモリ内の特別なデータ構造です。Amazon RDS for SQL Server では、リングバッファのターゲットメモリが 4 MB を超えることはできません。したがって、ビジー状態のインスタンスでは、system_health セッションがイベントをローテーションすることがあります。

system_health セッションを使用し、Microsoft SQL Server Management Studio (SSMS) または Transact-SQL (T-SQL) のいずれかを使用して、DB インスタンスのデッドロックに関する情報を取得します。

SSMS を使用してデッドロック情報を取得するには、次の手順を実行します。

1. SSMS を開きます。
2. Object Explorerで、[Management] (管理) を選択し、[Extended Events] (拡張イベント) を選択します。
3. [Sessions] (セッション) を選択します。
4. system_health セッションを検索し、package0.event_file を選択 (ダブルクリック) して拡張イベントファイルを開きます。
5. ファイルの内容がロードされたら、SSMS のメニューの [Extended Events] (拡張イベント) を選択します。
6. [Filters] (フィルター) を選択します。
7. [Filter] (フィルター) ウィンドウで、次の操作を行います。
   [Field] (フィールド) で、[name] (名前) を選択します。
   [Operator] (演算子) で、[Contains] (含む) を選択します。
   [Value] (値) で、[deadlock] (デッドロック) を選択します。
8. [OK] を選択します。デッドロックでイベントを表示できます。
9. 表示するイベントを選択し、[Deadlock] (デッドロック) タブを選択してグラフを表示します。

• または -

T-SQL を使用してデッドロック情報を取得するには、次の手順を実行します。

1.    次のようなクエリを実行して、デッドロックのリストを表示します。

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM (
    SELECT XEvent.query('.') AS XEvent
    FROM (
        SELECT CAST(target_data AS XML) AS TargetData
        FROM sys.dm_xe_session_targets st
        INNER JOIN sys.dm_xe_sessions s 
        ON s.address = st.event_session_address
        WHERE s.NAME = 'system_health'
        AND st.target_name = 'ring_buffer'
        ) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
) AS source;

2.    デッドロック XML 出力を選択して、XML ファイルを新しいウィンドウで開きます。

3.    .xdl ファイル拡張子を使用して XML を保存します。これにより、XML がグラフィカル形式に変換されます。

4.    ファイルの場所に移動し、SSMS で .xdl ファイルを開き、デッドロックグラフを表示します。

T-SQL クエリを実行して、ring_buffer からデッドロック情報を取得できます。ring_buffer ターゲットは、イベントデータをメモリ内に保持します。この情報は、インスタンスが再起動されていない場合にのみ使用できます。再起動すると、この情報は消去されます。

xml_deadlock_report 拡張イベントセッションを使用する

xml_deadlock_report イベントを選択してデッドロックをキャプチャすることで、拡張イベントセッションを作成できます。ターゲットとしてイベントファイルを選択すると、イベントがファイルに保存されます。このファイルは将来分析できます。拡張イベントセッションを作成するには、SSMS または T-SQL を使用できます。セッションが作成されたら、DB インスタンスのデッドロックに関する情報を取得できます。SSMS または T-SQL のいずれかを使用してこれを行うことができます。

SSMS を使用して拡張イベントセッションを作成するには、次の手順を実行します。

1. SSMS を開きます。
2. Object Explorerで、[Management] (管理) を選択し、[Extended Events] (拡張イベント) を選択します。
3. [Sessions] (セッション) を選択 (右クリック) し、[New Session Wizard] (新規セッションウィザード) を選択します。
4. [Session name] (セッション名) で、セッションの名前を入力し、[Next] (次へ) を選択します。
5. [Choose Template] (テンプレートを選択) ページで、[Do not use a template] (テンプレートを使用しない) を選択します。
6. [Next] (次へ) を選択して、[New Session Wizard] (新規セッションウィザード) ページを開きます。
7. [Event library] (イベントライブラリ) から [xml_deadlock_report] を選択し、[Next] (次へ) を選択します。
8. [Capture Global Fields] (グローバルフィールドをキャプチャ) ページで、すべてのイベントに共通する値を選択します。
   注: デッドロックの原因となったクエリを表示するには、sql_text フィールドを選択します。
9. [Next] (次へ) を選択します。
10. [Set Session Event Filters] (セッションイベントフィルターを設定) ページで、イベントフィルターを作成して、キャプチャするデータを制限します。
11. [Next] (次へ) を選択します。
12. [Specify Session Data Storage] (セッションデータストレージを指定) ページで、[Save data to a file for later analysis] (後で分析するためにデータをファイルに保存)、[Work with only the most recent data] (最新のデータのみを使用) を選択します。
13. [Finish] (完了) を選択します。

これで、SSMS の [Sessions] (セッション) フォルダに新しいセッションが表示されます。セッションを選択 (右クリック) し、[Start session] (セッションを開始) を選択します。

• または -

T-SQL を使用して拡張イベントセッションを作成するには、次のようなクエリを実行します。

CREATE EVENT SESSION [Deadlock_detection] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'D:\rdsdbdata\Log\Deadlock',max_file_size=(100))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO     
-- Start the event session  
ALTER EVENT SESSION Deadlock_detection ON SERVER  
STATE = start;  
GO

SSMS を使用してデッドロック情報を取得するには、次の手順を実行します。

1. SSMS を開きます。
2. Object Explorerで、[Management] (管理) を選択し、[Extended Events] (拡張イベント) を選択します。
3. [Sessions] (セッション) を選択します。
4. 前に作成した拡張イベントセッションを検索し、package0.event_file を選択 (ダブルクリック) して拡張イベントファイルを開きます。
5. ファイルの内容がロードされたら、表示するイベントを選択し、[Deadlock] (デッドロック) タブを選択してグラフを表示します。

• または -

T-SQL で次のようなクエリを実行して、デッドロックのリストを表示します。

SELECT * FROM sys.fn_xe_file_target_read_file('d:\rdsdbdata\log\deadlock*.xel', null, null, null)

---

関連情報

デッドロック情報ツールに関する Microsoft のドキュメント