SQL Server で実行されている Amazon RDS DB インスタンスのデッドロックに関する情報をキャプチャする方法を教えてください。

簡単な説明

ロックされたリソースを持つ複数のセッションがあり、他のセッションのロックされたリソースにアクセスしようとすると、データベースインスタンスでデッドロックが発生します。デッドロックが発生すると、いずれかのセッションがロックを解放するまで、どのセッションも実行を継続できません。

デッドロックを解決するために、SQL Server のデッドロック検出器はリソースベースまたはコストベースのメカニズムを使用してセッションを 1 つ終了します。その後、検出器はこのセッションに関連するすべての変更をロールバックします。デフォルトでは、SQL Server データベースエンジンは、最も負荷の低いトランザクションを実行するセッションをデッドロックの対象としてロールバックします。セッションの 1 つが終了すると、そのセッションで保持されていたロックが解放され、残りのセッションが続行できるようになります。詳細については、Microsoft の ウェブサイトで「デッドロック」参照してください。

解決策

トレースフラグ、system_health セッション、xml_deadlock_report 拡張イベントセッションを使用することで、DB インスタンスでのデッドロックイベントに関する情報をキャプチャできます。

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

デッドロックトレースフラグ (1204、1222) を有効にします。トレースフラグを使用して SQL Server の動作をカスタマイズし、SQL Server エラーログにデッドロックの情報をキャプチャします。

注: デッドロックが発生している高パフォーマンスのワークロードインスタンスでは、パフォーマンス上の問題が発生する可能性があるため、これらのトレースフラグの使用は避けるのがベストプラクティスです。代わりに、拡張イベントセッションを作成し、デッドロックイベントの情報をキャプチャします。

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

詳細については、Microsoft のウェブサイトで「トレース フラグ 1204 とトレース フラグ 1222」を参照してください。

両方のトレースフラグをアクティブにすると、RDS for SQL Server のカスタムパラメータグループを使用して、デッドロックイベントごとに 2 つの別々の表記を取得できます。設定方法については、「Amazon RDS for SQL Server DB インスタンスでデッドロックイベントが発生したときに通知を受け取る方法を教えてください」を参照してください。トレースフラグを有効にすると、SQL Server エラーログでデッドロックイベントの詳細を確認できます。

system_health セッションを使用する

Amazon RDS for SQL Server で拡張イベントを使用することで、データを収集し、SQL Server の問題を監視してトラブルシューティングすることができます。system_health 拡張イベントセッションは SQL Server に含まれており、デフォルトでアクティブになっています。SQL Server データベースエンジンが起動して基本的なサーバーの正常性に関する情報を収集するときに、セッションは自動的に開始されます。この情報を参考に、データベースエンジン内のパフォーマンス問題をトラブルシューティングしたり、デッドロックを監視したりすることができます。詳細については、Microsoft のウェブサイトで「system_health セッションを使用する」を参照してください。

system_health 拡張イベントセッションは、イベントファイルとリングバッファの 2 つのターゲットを使用してデータを保存します。リングバッファは、先入れ先出し (FIFO) 方式でデータを格納します。Amazon RDS for SQL Server では、リングバッファのターゲットメモリには 4 MB の上限があります。したがって、ビジー状態のインスタンスでは、system_health セッションによりイベントのローテーションが発生する場合があります。

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

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

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

1. SMS を開きます。
2. Object Explorer で [管理] を選択し、[拡張イベント] を選択します。
3. [セッション] を選択します。
4. system_health セッションを検索し、package0.event_file をダブルクリックして拡張イベントファイルを開きます。
5. ファイルの内容が読み込まれた後、SSMS メニューで [拡張イベント] を選択します。
6. [フィルター] を選択します。
7. [フィルター] ウィンドウで、次の操作を行います。
   [フィールド] で [name] を選択します。
   [演算子] で [次を含む] を選択します。
   [値] で [deadlock] を選択します。
8. [OK] を選択します。デッドロックのあるイベントを表示することができます。
9. 確認するイベントを選択し、[デッドロック] タブを選択するとグラフが表示されます。

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

1. 次のようなクエリを実行すると、デッドロックのリストが表示されます。

   SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraphFROM (
       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 を使用して拡張イベントセッションを作成することができます。

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

1. SMS を開きます。
2. Object Explorer で [管理] を選択し、[拡張イベント] を選択します。
3. [セッション] を右クリックした後、[新規セッションウィザード] を選択します。
4. [セッション名] にセッションの名前を入力し、[次へ] を選択します。
5. [テンプレートの選択] ページで [テンプレートを使用しない] を選択します。
6. [次へ] を選択して [新規セッションウィザード] ページを開きます。
7. [イベントライブラリ] から xml_deadlock_report を選択し、[次へ] を選択します。
8. [グローバルフィールドのキャプチャ] ページで、すべてのイベントに共通の値を選択します。
   注: sql_text フィールドを選択すると、デッドロックの原因となったクエリが表示されます。
9. [次へ] を選択します。
10. [セッションイベントフィルターの設定] ページで、キャプチャするデータを制限するイベントフィルターを作成します。
11. [次へ] を選択します。
12. [セッションデータストレージの指定] ページで、[後で分析できるようにデータをファイルに保存する] と [最新のデータのみを使用する] を選択します。
13. [完了] を選択します。

すると、SSMS の Sessions フォルダに新しいセッションが表示されるようになります。セッションを右クリックし、[セッションを開始] を選択します。

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. SMS を開きます。
2. Object Explorer で [管理] を選択し、[拡張イベント] を選択します。
3. [セッション] を選択します。
4. 前に作成した拡張イベントセッションを見つけた後、package0.event_file をダブルクリックして拡張イベントファイルを開きます。
5. ファイルの内容が読み込まれたら、確認するイベントを選択し、[デッドロック] タブを選択してグラフを表示します。

T-SQL を使用してデッドロックのリストを表示するために実行するクエリの例を次に示します。

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

関連情報

デッドロック ガイド (Microsoft のウェブサイト)

KB4541132 - 改善点: SQL Server 2016、2017、2019 で、既定の XEvent トレース system_health のサイズおよび保持ポリシーが増加しました (Microsoft のウェブサイト)

Amazon CloudWatch を使用して Amazon RDS for SQL Server のデッドロックを監視し、通知を設定する