SQL Serverでデッドロックを調査するための方法を、自分のメモ程度に紹介しておこうと思います。
何個かあるみたいなので、いくつか確認していきたいと思います。
また、本記事ではデッドロックの調査をするために必要な情報を取得するための方法を紹介するものであり、具体的にどのようにデッドロックを解決するかまでは紹介しておりませんので、その点はご了承ください。
トレースフラグ「1204」を設定する
まずは、トレースフラグ「1204」を設定する方法を試してみます。これを設定することにより、デッドロックが発生した場合にログを出力してくれるようになります。実際に設定してみて、どのように役立つか試してみます。
以下のようにコマンドを実行して、トレースフラグ「1204」をONにします。こちらはドキュメントにも記載の通り、グローバルスコープで設定するトレースフラグのようですね。
適当に以下の通りデッドロックを発生させてみます。
デッドロックが発生すると、以下のSQL Serverのログに出力されるみたいです。SSMSの「管理」-「SQL Server ログ」から見ることができます。
以下のようにデッドロックのログを見ることができるようです。デッドロックが発生したときの実際のクエリや、ロックの種類などの情報を見ることができます。
トレースフラグを設定しないとデッドロックが発生したことくらいしかわからないので、上記の情報があれば調査の手がかりになると思います。
トレースフラグ「1222」を設定する
それでは、デッドロックの情報をログ出力するもう一つのトレースフラグ「1222」も試してみます。因みに、こちらは先ほどのトレースフラグ「1204」の後に提供された機能のようです。
以下のようにコマンドを実行して、トレースフラグ「1222」をONにします。こちらも先ほどと同様にグローバルスコープのようですね。
また同じようにデッドロックを発生させてみます。
こちらもデッドロックが発生すると、SQL Serverのログに出力されるみたいです。同様に中身を見てましょう。
字が小さくて見えないかもしれませんが、先ほどのトレースフラグ「1204」に比べると情報量がこちらの方が多いです。たとえば、ロックの原因となったクライアントの情報やインデックスの名前など、先ほどにはない情報も出力されています。
どちらかを設定するので迷うのであれば、迷わずこちらを選べば良さそうです。ただ、どちらも同時に設定することができるので通常であれば両方設定するみたいです。
拡張イベントの「system_health」で確認する
トレースフラグ「1204」「1222」をあらかじめ設定しておくことで、デッドロックの情報がログ出力されるようになりました。あともう一つだけ、紹介させていただきます。それが、拡張イベントの「system_health」で確認する方法です。
system_health セッションの使用 - SQL Server | Microsoft Learn
確認方法ですが、こちらは事前準備不要です。以下のように、SSMSで「管理」-「拡張イベント」-「セッション」に「system_health」があると思います。
上記の「system_health」-「package0.event_file」を右クリックし、「ターゲットデータの表示」を選択します。すると以下のような画面が開くと思います。
デッドロックとは関係ないたくさんの情報が出力されているので、以下のようにメニューの拡張イベントからフィルターを選択します。
フィルターで、先ほど表示されたログをデッドロックのものだけにします。以下のように、フィールドに「name」、演算子に「＝」、値に「xml_deadlock_report」を入力してOKを押下します。
すると、デッドロックが発生していれば以下のようにフィルターされると思います。
デッドロックタブを開くと以下のように、グラフを見ることができます。これは視覚的に、どのプロセスが何をロックして何をロック解放待ちしているかが分かるのでとても分かりやすい情報を取得することができます。
また、詳細タブのレコードをダブルクリックすると以下のようにXML形式でより細かい情報を取得することができます。
内容的には、トレースフラグの設定で取得するものがsystem_healthでも取得できそうですのでこちらだけ見ておけば良いかもしれません。結局ケースバイケースかとは思いますが。
