デッドロック調査用の情報採取
■初めに
デッドロックについては以前ポストしましたが、やはり多くのお客様が悩まれる問題であると認識しています。SQL Server の Books Online でも下記のような情報を公開しており、以前に比べると必要な情報を確認いただける状態にはなっていると考えています。
しかしながら、実際に我々サポート部門にお問い合わせを頂戴し、デッドロック調査用の情報採取手順を回答差し上げることも多いのは事実です。そこで、このポストではお問い合わせいただいた際に採取情報として回答している内容を、一般化してまとめたものを紹介したいと思います。このポストが、お客様でのデッドロックの調査の一助となれば幸いです。
なお、ここで提示した情報を用いた方法のみが調査手法ではありませんので、あくまで1つの方法論としてお読みください。
また、対象はSQL Server 2005以降となりますが、トレースフラグ 1222 以外は SQL Server 2000 でも使用可能な情報ですので、SQL Server 2000 のデッドロック調査もこの方法で行えると考えております。
■ 採取情報
一般的にデッドロックを調査する場合、以下の情報を採取いただくことをお願いしています。
a. トレースフラグの出力結果
b. サーバトレースの出力結果
c. スキーマ情報
a. のトレースフラグの出力結果は、Books Online でも紹介しております、トレースフラグ1204および1222の出力結果となります。トレースフラグを有効とすることで、SQL Server の ERRORLOG に出力が行われます。基本的に、デッドロックの調査においてベースとなる情報と考えていただいて問題ありません。どちらかだけでも、基本的な情報は得ることができますが、このポストでは両方を有効とする手順を紹介します。
b.のサーバトレースは、「SQL トレーススクリプトの作成、実行 (SQL Server 2005, 2008, 2008 R2)」で紹介している機能です。このサーバトレースを使用して、"関連するステートメント"、"実行プラン"、"トランザクション単位"を確認します。後述しますが、一般的に大量のデータが出力されるため、長時間の採取には向かない情報となります。
c.は、b.で確認した関連するステートメントが参照しているテーブルについて、インデックスを含めた作成スクリプトをご提供いただくことをお願いしています。これは、インデックス構成を確認し、必要なインデックスを作ることで回避可能かを検討するためとなります。
■ 採取の流れ
上記「採取情報」で説明した情報を採取することになりますが、サーバトレースは大量の情報が出力されるため、長時間の採取には適さない情報となります。そのため、調査対象のデッドロックについて、どこまで切り分けを行っていただけているかによって、段階を踏んで調査を行うことを提案しています。
デッドロックが再現できない状況 デッドロックが発生したことがあるが、どのような処理が関わっていたかわからず、再現ができない状況や発生頻度が低くタイミングが不明な状況である場合、a.のトレースフラグを設定し、まずデッドロックに関わっている処理を特定することを提案しています。この情報を元にデッドロックが再現できれば、「デッドロックが再現するか頻発する状況」の作業を行い、調査が行えるようになります。
なお、発生頻度が極めて低いデッドロックであれば、クライアントサイドでのリトライが現実的な対応となります。調査した結果、同じタイミングで同一レコードへの更新、参照によって発生するような回避不可能なデッドロックであった場合は、結局リトライでの対応となります。
デッドロックが再現するか頻発する状況 デッドロックが再現可能か、もしくは特定時間帯に頻発することが判っている場合、一気に情報を取得するのが解決への最短コースとなります。そのため、以下の流れで情報の採取をしていただいています。
1) トレースフラグの有効化
2) サーバトレースの開始
3) デッドロックの再現確認
4) サーバトレースの停止
5) トレースフラグの無効化
この作業の後、我々サポート部門での解析をご依頼いただく際は、以下の情報をご提供いただいています。
- SQL Server の Log フォルダ内のすべてのファイル
- サーバトレースの出力結果
- テーブルおよびインデックスの作成スクリプト(*1)
*1 通常、サーバトレースの解析後にご提供をお願いしています。
■ トレースフラグの設定方法
トレースフラグの有効化、無効化の方法を紹介いたします。トレーフラグを設定する方法には、以下の2つの方法があります。
a. DBCC TRACEON/TRACEOFF の使用
b. 起動オプションへの設定
DBCC TRACEON で設定した場合、コマンド実行後から直ぐにトレーフラグが有効となります。ただし、SQL Server を再起動した場合、再びコマンドを実行し直す必要があります。その為、起動オプションへの設定を行い、次回再起動で有効になるようにした上で、再起動まではDBCC TRACEONで有効化しておくという方法も取れます。
a-1. DBCC TRACEON での有効化
以下のコマンドを実行いただくことで、有効化が行えます。
DBCC TRACEON (1204,1222,-1)
go
a-2. DBCC TRACEOFF での無効化
以下のコマンドを実行いただくことで、無効化が行えます。
DBCC TRACEOFF (1204,1222,-1)
go
b.起動オプションへの設定方法
以下の手順で設定が行えます。無効とする際は、追加した文字列を削除いただき、SQL Server の再起動を行います。
1) SQL Server Configuration Manager (構成マネージャ)を起動します
2) SQL Serverのサービス -> SQL Server (MSSQLSERVER/もしくはインスタンス名) を右クリックし、プロパティを開きます
3) 詳細設定タブ -> 起動時のパラメータに、次の文字列を追加します
;-T1204;-T1222
* セミコロンで区切ってパラメータを追加するイメージです
■ サーバトレースの使用方法
サーバトレースの実際の使用方法は、以前のポストである「SQL トレーススクリプトの作成、実行 (SQL Server 2005 ～ 2016)」を参照してください。ここでは、選択すべきイベントについて紹介いたします。
とりあえず、ベースとして以下のイベントが入っていれば問題ないと考えています。ただし、イベントとしては多いため、大量の情報が出力されます。このベースを元に、不要なイベントを削除して情報採取をしていくことになります。例えば、ストアドプロシージャが関連していないことが判っているのであれば Stored Procedures イベントを外すことができます。イベントについてはどのような情報が取れるか、実際にテスト環境などで確認した上で、選択してください。
Errors and Warnings
※ すべてのイベント
Locks
Deadlock Graph
Lock: Deadlock
Lock: Escalation
Performance
Showplan All (SQL Server 2000)
Showplan XML (SQL Server 2005 以降)
Security Audit
Audit Login
Audit Logout
Sessions
ExistingConnection
Stored Procedures
RPC: Completed
PRC: Starting
SP: Completed
SP: Starting
SP: StmtStarting
TSQL
SQL: BatchCompleted
SQL: BatchStarting
SQL: StmtStarting
Transactions
※ TransactionLog 以外すべて