SQL Serve のクエリ情報を取得する際には、次のイベントカテゴリの情報を使用することがあるのではないでしょうか?
これらのイベントカテゴリの情報には、Batch / Statement / RPC / SP というようなイベントがあり、クエリの情報を取得する場合には、これらの情報を拡張イベントや SQL トレース (SQL Server Profiler / サーバーサイドトレース を使用して取得を行いますが、これらのイベントカテゴリは、どのようなクエリを実行した際に、どの部分の情報を取得することができる野でしょうか？
本投稿では、これらのイベントカテゴリで取得されている情報の基本的な考え方をまとめてみたいと思います。
イベントの考え方に関しては、拡張イベントと SQL Server Profiler で同様の考え方となりますが、蘇rぞれの機能によってイベントの名称が異なっています。
使用する機能に応じたイベントの対応については SQL トレースのイベント クラスと等価な拡張イベントを確認する のドキュメントで確認できますので、こちらを参照してください。
Contents
sql_batch_completed / SQL:BatchCompleted
次のようなクエリを実行した場合を考えてみます。
DECLARE @N_NATIONKEY int = (SELECT TOP 1 N_NATIONKEY FROM NATION WHERE N_NAME='JAPAN') SELECT COUNT(*) FROM CUSTOMER WHERE C_NATIONKEY = @N_NATIONKEY
このクエリは 2 つのステートメントで構成されています。
/* ① */ DECLARE @N_NATIONKEY int = (SELECT TOP 1 N_NATIONKEY FROM NATION WHERE N_NAME='JAPAN') /* ② */ SELECT COUNT(*) FROM CUSTOMER WHERE C_NATIONKEY = @N_NATIONKEY
SQL Server でバッチという実行単位で考えた場合は「ステートメントの集合」を表した単位となります。
古いドキュメントになりますが、バッチについては バッチ で解説が行われています。
上記のようなクエリを一つの処理として実行した場合には、両方のステートメントの実行結果を合算した情報が Batch Completed の情報として出力が行われます。
この情報は上記のクエリの ①/② のステートメントの実行に関しても時間 / リソースの使用量を合算したものが出力されています。
複数のステートメントを一つのクエリとして実行している場合、「バッチは各ステートメントの処理状態を合算したもの」となります。
また、Batch Completed のようなバッチとして出力されるクエリについては「RPC (リモートプロシージャコール) により実行されていないクエリ」が対象となります。
SSMS から「usp_01」という形で、ストアドプロシージャやプリペアード (パラメーター化) ステートメントを実行した場合は、RPC ではなく、バッチとしての情報として取得されます。
そのため、ストアドやプリペアードステートメントであれば、必ずしも RPC としてイベントが発生するわけではなく、「実行方法 (ツールやデータアクセスに使用しているコンポーネント)」によって、どのように出力されるかが変わってきます。
sql_statement_completed / SQL:StmtCompleted
先ほどは「バッチ」という単位で情報の取得を行いました。
処理の構成によっては「バッチ内の各クエリレベル」で情報を取得したいケースもあるかと思います。
そのような場合は「ステートメント」という単位で情報の取得を行います。
/* ① */ DECLARE @N_NATIONKEY int = (SELECT TOP 1 N_NATIONKEY FROM NATION WHERE N_NAME='JAPAN') /* ② */ SELECT COUNT(*) FROM CUSTOMER WHERE C_NATIONKEY = @N_NATIONKEY
このようなクエリを実行した場合、① / ② のそれぞれが「ステートメント」として認識されます。
バッチで取得した場合は ① / ② の合算値で情報を取得していましたが、それぞれのステートメントについての処理の情報が必要な場合は、ステートメントレベルでの情報取得を実施します。
ステートメント単位で取得した場合には、① / ② の情報がそれぞれ個別に取得され、各ステートメントについての情報の解析を行うことが可能となります。
module_end / SP:Completed
クエリを実行する場合には、アドホックなクエリだけではなく、ストアドプロシージャーを実行するケースもあります。
ストアドプロシージャに関してのイベントを取得したい場合には、対応したイベントクラスによる情報の取得を検討します。
(拡張イベントと SQL トレースでは、イベント名が大きく異なります)
モジュールには、トリガーや関数の実行についても含まれることになりますので、必要に応じて object_type でフィルターを行いましょう。
ストアドプロシージャの実行にについては、Batch Completed または、RPC Completed といったイベントでも情報の取得は行われますので、汎用的な情報であれば、モジュールのイベントを取得しなくても、他のイベントで代替をすることも可能です
次のストアドプロシージャがあった場合、本イベントクラスでは、① / ② の実行時間等を合算したものが情報として取得されることになります。
CREATE PROCEDURE usp_01 AS /* ① */ DECLARE @N_NATIONKEY int = (SELECT TOP 1 N_NATIONKEY FROM NATION WHERE N_NAME='JAPAN') /* ② */ SELECT COUNT(*) FROM CUSTOMER WHERE C_NATIONKEY = @N_NATIONKEY GO
sp_statement_completed / SP:StmtCompleted
module_end / SP:Completed では、ストアドプロシージャのようなモジュール全体の情報の取得が行われました。
次のストアドプロシージャは複数のステートメントで構成されています。
CREATE PROCEDURE usp_01 AS /* ① */ DECLARE @N_NATIONKEY int = (SELECT TOP 1 N_NATIONKEY FROM NATION WHERE N_NAME='JAPAN') /* ② */ SELECT COUNT(*) FROM CUSTOMER WHERE C_NATIONKEY = @N_NATIONKEY GO
ストアドプロシージャ内のステートメントの情報を取得する場合には、ステートメント用のイベントの取得を行います。
このイベントを取得することで、ストアドプロシージャ内のステートメント毎の情報を取得することができます。
sql_statement_completed / SQL:StmtCompleted のストアドプロシージャ版ですね。
ストアドプロシージャ内の処理単位で情報を取得する場合は、ストアドプロシージャのステートメント単位の情報取得を行います。
rpc_completed / RPC:Completed
ここが少しわかりずらいところではあるのですが。
一般的には、ストアドプロシージャやパラメーター化クエリを実行した際のイベントとして認識されていると思います。
それでは、次のクエリを SSMS から実行すると、どのようなイベントとして情報が取得されるでしょうか？
/* ① ストアドプロシージャの実行 */ usp_01 GO /* ② パラメーター化クエリの実行 */ sp_executesql N'EXECUTE (@sql)', N'@sql nvarchar(max)', @sql = N'SELECT @@VERSION' GO
この時のイベントをトレースした結果がこちらになります。
RPC についてのイベントは発生しておらず、アドホッククエリのような Batch Completed の情報として取得されていますね。
それでは、次の Power Shell でクエリを実行するとどうでしょうか？
Clear-Host
$con = New-Object System.Data.SqlClient.SqlConnection("server=localhost;integrated security=SSPI;database=tpch;application name=querytest")
$con.Open()
$cmd = $con.CreateCommand()
# $cmd.CommandType = [System.Data.CommandType]::StoredProcedure
$cmd.CommandText = "usp_01"
$reader = $cmd.ExecuteReader()
$con.Close()
$con.Dispose()
現時点では、CommandType はコメント化した状態となっています。
イベントの発生状況については先ほどと同じですね。
アプリケーションからストアドプロシージャを実行した場合でも、適切なコマンドタイプが指定されていない場合には、RPC ではなく通常のバッチとして実行されています。
それでは、「$cmd.CommandType = [System.Data.CommandType]::StoredProcedure」のコメント化を解除し、ストアドプロシージャとして明示的に指定して実行します。
Clear-Host
$con = New-Object System.Data.SqlClient.SqlConnection("server=localhost;integrated security=SSPI;database=tpch;application name=querytest")
$con.Open()
$cmd = $con.CreateCommand()
$cmd.CommandType = [System.Data.CommandType]::StoredProcedure
$cmd.CommandText = "usp_01"
$reader = $cmd.ExecuteReader()
$con.Close()
$con.Dispose()
Batch Completed ではなく、RPC Completed としてイベントが発生していますね。
RPC で実行されているかどうかについては実行方法も影響を与える要素の一つとなっています。
ストアド プロシージャの呼び出し には次のように記載されています。
また、SQL Server Native Client ODBC ドライバーは、SQL Server を実行するコンピューター間のリモート プロシージャ コール (RPC) の送信向けに開発されているプロトコルを使用するように最適化されます。 この RPC プロトコルでは、サーバー側で実行されるパラメーター処理やステートメントの解析作業の多くを排除することで、パフォーマンスを向上しています。
ドライバー側でどのような実装になっているかがも重要です。
実行の方法によってはストアドプロシージャでも RPC Completed ではなく、Batch Completed として出力が行われます。
今回は .NET Framework からクエリを実行している場合の動作ですので、他の TDS のドライバーを使用した場合は、発生するイベントも異なる可能性があります。
まとめ
|
SQL を「バッチとして実行」した際の情報
バッチ内には複数のステートメントが含まれる ストアドプロシージャやプリペアードステートメントを RPC で実行していない場合はバッチとして認識される |
|
「バッチ内のステートメント単位」の情報
バッチが複数のステートメントで構成される場合は各ステートメントについて情報が出力される ストアドプロシージャ内のステートメントについては本イベントではなく、ストアドプロシージャ用のイベントで取得 |
|
「ストアドプロシージャやトリガー等が実行」した際の情報
これらの実行については、実行方法によって RPC Completed ではなく Batch Completed としても出力される |
|
「ストアドプロシージャやトリガー等のステートメント単位」の情報
これらのオブジェクトのステートメントについては、SQL Statement Completed ではなく本イベントで取得を行う |
|
「RPC で実行された複数のステートメントを統合」した情報
ストアドプロシージャやパラメーター化クエリを RPC で実行した場合は、Batch Completed ではなく、本情報として出力が行われる。 (ストアドプロシージャを RPC で実行していない場合は、Batch Completed としてイベントが出力される) |
sql_batch_completed / SQL:BatchCompleted と rpc_completed / RPC:Completed を取得することで、クエリの全体の情報を取得することができます。
ストアドプロシージャやトリガーのようなオブジェクトの情報を取得する場合は、module_end / SP:Completed の情報を取得します。
バッチやストアドプロシージャのステートメント単位の情報を取得したい場合は、sql_statement_completed / SQL:StmtCompleted と sp_statement_completed / SP:StmtCompleted の情報を取得します。
SSMS から実行した場合とアプリケーションから実行した場合では発生するイベントが異なる可能性がありますので、可能であれば取得したいクエリをアプリケーションから実行した場合の、どのイベントが発生するかを確認しましょう。
取捨選択が難しい場合は上記の情報をすべて取得しましょう。