Oracle 経験者のための SQL Server HowTo #2: サーバートレースを使ってセッション・レベルのSQLトレースを取得する

早川 邦彦
SQL Engine Support Engineer

 

Oracle 経験者のための SQL Server HowTo 2回目の今回は、SQL Server のサーバートレースを使って Oracle でいうセッション・レベルのSQLトレースを取得する方法について紹介します。

1. セッション・レベルのSQLトレースについて

Oracle データベースでは以下のようなコマンドを実行することで、該当セッションで実行されるSQLの構文、実行統計、および実行プラン情報をトレースファイルに出力させることができます。

  ALTER SESSION SET EVENTS '10046 trace name context forever, level 1';

SQL Server ではサーバートレースを使用することで上記の Oracle のコマンドのように、特定のセッションが実行しているSQLの詳細情報をトレースファイルに出力させることができます。なお、 Oracle の上記コマンドとは違い、SQL Server のサーバートレースを使用すればどのセッションの、どのような情報を出力するかをより詳細に制御することが可能です。

2. トレース定義スクリプトの作成

トレース定義スクリプトを作成する基本的な手順は以下の弊社ブログ情報に記載されているのでこちらの手順をベースに SQL Server でのセッション・レベルのSQLトレースを取得するためのスクリプト生成方法を説明します。

  SQL トレーススクリプトの作成、実行 (SQL Server 2005, 2008, 2008 R2)
  https://blogs.msdn.com/b/jpsql/archive/2011/01/26/sql.aspx

  SQL トレーススクリプトの作成、実行 (SQL Server 2000)
  https://blogs.msdn.com/b/jpsql/archive/2011/01/26/sql-sql-server-2000.aspx

2.1. SQL Server イベントを選択する

上記ブログにある「トレース対象イベントの指定」手順で以下のイベントを選択します。

  Performance
ShowPlan Statistics Profile

  Stored Procedures
RPC:Completed
SP:Completed
SP:StmtCompleted
RPC:Starting
SP:Starting
SP:StmtStarting

  TSQL
SQL:BatchCompleted
SQL:StmtCompleted
SQL:BatchStarting
SQL:StmtStarting

なお、必要に応じて上記以外のイベントも追加することができます。 選択可能なイベントの詳細については以下の弊社技術情報をご確認ください。

  SQL Server イベント クラスの参照
  https://msdn.microsoft.com/ja-jp/library/ms175481(v=sql.105).aspx

2.2. セッションID(SPID)のフィルターを設定する

上記ブログにある手順の 「T-SQL スクリプトの修正」 ステップの作業に加え、トレース取得をセッションID(SPID)でフィルターするための記述をトレース定義スクリプトに追加します。 具体的にはトレース定義スクリプトの 「-- Set the Filters」の箇所に以下のようなコマンドを追記します。

  set @intfilter = 56
exec sp_trace_setfilter @TraceID, 12, 0, 0, @intfilter
上記は セッションID(SPID)=56 のセッションの情報のみを出力するためのフィルター設定です。@intfilter の値を編集することで 56 以外のSPIDでトレースをフィルターすることができます。
以下は上記フィルター設定を追記したトレース定義スクリプトの抜粋例です。 

  

なお、SQL Server で動作している各プロセスのセッションID(SPID) は以下のコマンドを実行し、出力される結果のSPID列から確認できます。

  select * from sys.sysprocesses

3. トレース取得と確認

上記ブログにある「トレースの開始」手順でトレース取得を開始し、情報を取得できたら「トレースの停止]手順でトレース取得を停止できます。そして、取得できましたトレースファイルは SQL Server Profiler で開くことで内容を確認することができます。以下は上記手順で取得したトレース内容の抜粋です。

 

以下は Oracle の SQL トレースに含まれている情報項目と上記方法で取得したサーバートレースの情報項目のマッピングです。

Oracle SQL トレース項目	該当するサーバートレース情報
PARSING IN CURSOR 行	*:*Starting 行
STAT 行	Showplan Statistics Profile 行
SESSION ID 行	各行の SPID 列
CLIENT ID 行	各行の ClientProcessID 列
CPU 時間 項目	*:*Completed 行の CPU 列
ディスクIO量 項目	*:*Completed 行の Reads/Writes 列
経過時間 項目	*:*Completed 行の Duration 列

4. 参考ページ

  SQL トレーススクリプトの作成、実行 (SQL Server 2005, 2008, 2008 R2)    
  https://blogs.msdn.com/b/jpsql/archive/2011/01/26/sql.aspx

  SQL トレーススクリプトの作成、実行 (SQL Server 2000)    
  https://blogs.msdn.com/b/jpsql/archive/2011/01/26/sql-sql-server-2000.aspx

  sp_trace_create (Transact-SQL)    
  https://msdn.microsoft.com/ja-jp/library/ms190362.aspx

  sp_trace_setfilter (Transact-SQL)    
  https://msdn.microsoft.com/ja-jp/library/ms174404(v=sql.105).aspx 

  SQL Server イベント クラスの参照
  https://msdn.microsoft.com/ja-jp/library/ms175481(v=sql.105).aspx