SQL Server 2016 以降、クエリ ストア機能により、クエリの実行プランが変わったことに起因したクエリ パフォーマンスの違いがとても簡単に特定することができるようになり、また、SQL Server 2019 以降では、高速順方向カーソル、Transact-SQL および APIの静的カーソルに対するクエリ実行プランの強制がサポートされるなど、機能が拡張されています。
クエリストアについては、クエリのストアを使用した、パフォーマンスの監視 - SQL Server | Microsoft Docs を参照。
しかしながら、ブロッキングやデッドロックが発生するまでの詳細な流れを確認したい場合、エラーが発生するまでの詳細な流れを確認したい場合、また、分散トランザクションのトランザクションに関する詳細な流れを確認したい場合など、詳細なトラブルシューティング目的により、サーバートレースや拡張イベントを採取したい場面があるかと思います。
今回は、サーバートレースの採取手順について紹介したいと思います。
サーバートレースは、SQL Server 2005 以降で使用可能な機能となっていますが、SQL Server 2019 ではメンテンナンス モードになっており、代わりに拡張イベントを使用することが推奨されています。
サーバートレース 採取手順
1) SQL Server Profiler を起動します。
2) 「ファイル」-「新しいトレース」を選択します。
3) サーバートレースを採取したい SQL Server インスタンスに接続します。
4) 「イベントの選択」タブ を選択後、「すべてのイベントを表示する」、「すべての列を表示する」にチェックします。
5) 採取するトレースイベントを設定します。
[設定イベント例]
A. 問題となる処理が特定できていない状況で、問題の切り分けのための情報採取する場合
実行プラン情報、ステートメントレベルの情報を選択せず、問題となりそうな処理が特定できるレベルの情報採取を実施。
Errors and Warnings
※ すべてのイベント
Sessions
ExistingConnection
Stored Procedure
RPC:Starting
RPC:Completed
SP:Starting
SP:Completed
TSQL
SQL:BatchStarting
SQL:BatchCompleted
Locks
Lock:Escalation
Lock:Deadlock
Lock:Deadlock Chain
Lock:Timeout (timeout > 0)
B. 問題となる処理が特定できている状況で、問題の再現性、再現手順があり、より詳細な情報を把握する必要がある場合
実行プラン情報、ステートメントレベルの情報についても選択し、より詳細な状況を把握できるレベルの情報採取を実施。
※ 大量のデータがトレースされる可能性がある点に注意が必要
Errors and Warnings
※ すべてのイベント
Performance
Showplan XML Statistics Profile
Showplan Statistics Profile
Sessions
ExistingConnection
Stored Procedure
RPC:Starting
RPC:Completed
SP:Starting
SP:Completed
SP:StmtStarting
SP:StmtCompleted
TSQL
SQL:BatchStarting
SQL:BatchCompleted
SQL:StmtStarting
SQL:StmtCompleted
Locks
Lock:Escalation
Lock:Deadlock
Lock:Deadlock Chain
Lock:Timeout (timeout > 0)
Transactions
SQLTransaction
6) 「実行」を押下後、すぐにトレースを停止します。
7) 「ファイル」-「エクスポート」 - 「トレース定義のスクリプト」 - 「SQL Server 2005 - 2019 用」を押下し、設定したトレース定義情報をトレース定義スクリプトとして任意の場所に保存します。
8) 7) でエクスポートしたトレース定義スクリプトをテキストエディタやSQL Server Management Studio などで開き、以下のパラメータ値を変更後、保存します。
- @maxfilesize (トレースファイルの最大サイズ) を設定
※ 200 MB程度に指定すると良いかと思います。
- sp_trace_create の第 2 引数を「2」 (ロールオーバー) に設定
- sp_trace_create の第 3 引数にトレースファイルを作成するパス、および、トレースファイル名を設定
※ 今回の例では、C:\temp の配下に「Trace.trc」ファイルが作成され、ロールオーバー設定が有効になっている場合、@maxfilesizeを超えると「Trace_*.trc」ファイルが作成されていきます。
9) 8) で修正したトレース定義スクリプトをSQL Server Management Studio などから実行します。
※ 本スクリプト実行時に表示される「TraceID」は、トレース停止時に使用するため控えておきます。
10) 以下のクエリを実行し、サーバートレースを停止します。
sp_trace_setstatus <(9)で控えたTraceID>, 0
go
sp_trace_setstatus <(9)で控えたTraceID>, 2
go
[コマンド実行例] TraceIDが「7」の場合
[補足]
サーバートレース開始時のTraceIDを失念した場合は、以下のコマンドを実行することで、TraceID を確認することが可能です。
select * from ::fn_trace_getinfo(NULL)
[コマンド実行結果例]
※ トレースファイル名などをヒントにTraceIDを確認します。
また、SQL Server サービスを再起動することでもサーバートレースを停止することが可能です。
