SQL Server でパラメーター化クエリ (パラメータークエリ) を使用した場合、「クエリコンパイル時のパラメーター」を意識することがあるかと思います。（パラメーター化クエリだけでなく、ストアドプロシージャも同様ですが)
これは、「パラメーター スニッフィング」という、クエリのコンパイルが発生した際に、コンパイル時に使用されたクエリのパラメーターを傍受し、オプティマイザーがクエリの最適化を行うためです。
次のクエリを実行したタイミングでコンパイルが発生したとします。
sp_executesql N'SELECT * FROM LINEITEM WHERE L_ORDERKEY >= @orderkey', N'@orderkey int', @orderkey = 300000000
この場合、パラメーター スニッフィングにより、「@orderkey = 300000000」というパラメーターによって最適化されたクエリとしてコンパイルが行われます。。
今回は上記のクエリのハッシュ値がわかっているため、キャッシュされているクエリの情報を取得してみます。
SELECT
qp.query_plan
FROM
sys.dm_exec_query_stats AS qs
OUTER APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE
query_hash = 0x3B2744F6B4DC1A74
キャッシュされているクエリの実行プランには、「パラメーター リスト」という情報が含まれています。
実行プランの XML で確認した場合には、次のような情報です。
<ParameterList>
<ColumnReference Column="@orderkey" ParameterDataType="int" ParameterCompiledValue="(300000000)" />
</ParameterList>
この情報から、キャッシュされているパラメーター化クエリの実行プランは、どのようなパラメーターによって、生成されたのかを確認することができます。
ここからが本題です。
先ほどのクエリは「300,000,000 件」のレコードのテーブルに対して「@orderkey = 300000000」で検索を行っていますので、実行はすぐに完了します。
それでは、次のパラメーターで実行した場合はどうでしょうか。
sp_executesql N'SELECT * FROM LINEITEM WHERE L_ORDERKEY >= @orderkey', N'@orderkey int', @orderkey = 1
このクエリの場合はすべてのレコードを取得することになります。
同じパラメーター化クエリを使用して実行していますが、実行時のパラメーターによって、処理速度に大きな差が出るクエリとなると言えますね。
クエリの情報を取得する際には、次のようなクエリで取得することが多いのではないでしょうか。
SELECT * FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_query_plan_stats (plan_handle) CROSS APPLY sys.dm_exec_sql_text(sql_handle)
このようなクエリで取得した場合、実行中のクエリの実行プランやテキストの情報を取得することができるのですが、
- 実行プラン : キャッシュされているプランまたは、キャッシュ前のプラン
- テキスト : パラメーター化されたテキスト
を取得することになりますので、実行中のクエリのパラメーターまでは取得することはできません。
それでは、「実行中のクエリのパラメーター」を取得するためにはどうすればよいでしょうか。
実行中のクエリのテキストからパラメーターを取得する
方法の一つとしては「実行中のクエリのテキスト」からパラメーターを取得する方法です。
先ほども触れましたが、実行中のクエリのテキストを取得する方法としては、一般的に使用される DMV を使ってみます。
SELECT st.text FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st WHERE session_id <> @@spid
この方法で取得した場合、取得されるクエリのテキストは「パラメーター化されたテキスト」となりますので、実行中のクエリがどのようなパラメーターで実行されたのかは取得することができません。
実行中のパラメーター化クエリについて、どのようなパラメーターが指定されたのかを確認するためには、いくつかの方法があります。
一つは「DBCC INPUTBUFFER」を使用する方法です。
どのようなパラメーターを使用して実行されたクエリなのかが確認できますね。
DBCC INPUTBUFFER は古くからある DBCC ですので、SQL Server のバージョンを問わず、汎用的に利用することができます。
DBCC コマンドは、特定の情報 (今回は特定のセッション ID) をピンポイントで取得するのであれば活用できますが、ベースとなる DMV 等の情報と結合して使用するには汎用性が低いものとなります。
そこで、SQL Server 2014 以降であれば、dm_exec_input_bufferを使用することができます。
dm_exec_input_buffer も DBCC INPUTBUFER 相当の情報を返すものですが、こちらは DMF として実装されていますので、実行中のクエリをベースとしてテキストを取得することも容易です。
実行中のクエリの実行プランからパラメーターを取得する
私がよく使用する方法はこちらになります。
SQL Server では、クエリ プロファイリング インフラストラクチャ という機能を使用することができ、この機能を使用すると実行中のクエリの情報を取得することができます。
SQL Server 2016 SP1 以降であれば dm_exec_query_statistics_xml という DMF を使用することができ、これを使用すると、「実行中のクエリの実行プラン」を取得することが可能です。
SQL Server 2017 までは、設定変更 (トレースフラグ 7412 等) が必要なのですが、SQL Server 2019 / SQL Database であれば、標準で使用することができるため、導入直後の環境で活用できるようになっています。
活用方法の例ですが、次のようなクエリとなります。
(今回は、取得したいクエリのハッシュがわかっているため、直接指定しています)
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
*,
T.query_plan.query('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";//ParameterList') AS ParameterList,
T.query_plan.value('(//ColumnReference/@Column)[1]', 'varchar(100)') AS ColumnName,
T.query_plan.value('(//ColumnReference/@ParameterCompiledValue)[1]', 'varchar(100)') AS ParameterCompiledValue,
T.query_plan.value('(//ColumnReference/@ParameterRuntimeValue)[1]', 'varchar(100)') AS ParameterRuntimeValue
FROM
(
SELECT
qst.query_plan
FROM
sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_query_statistics_xml(r.session_id) AS qst
WHERE
r.query_hash = 0x3B2744F6B4DC1A74
) AS T
情報の取得が完了すると次のような結果が取得できます。
取得している情報は、実行中のクエリの実行プランと、実行プラン内のパラメーターをパースしたものとなります。
これにより、
- どのような実行プランが使用されているのか
- どのようなパラメーターによってコンパイルされたクエリなのか
- どのような実行時パラメーターが使用されているのか
を取得することができます。
パラメーター化クエリを使用している場合、「パラメーター スニッフィングによって、汎用的ではない実行プランが生成された」という可能性も考えられますが、「パラメーターの値によって、シンプルな実行プランで取得されるデータ量が異なる」ケースも考えられます。
そのような切り分けを行うためには、「実行中のクエリの実行プラン」を分析する必要があります。
「どのようなパラメーターで実行されたのかをクエリのテキストで取得」するだけでなく「どのようなパラメーターで実行されたのかを実行プランで取得」することで、多面的に解析を行うことができるのではないでしょうか。
本ブログでは、実行中のクエリ情報を取得するというアプローチをいくつか紹介しており、 非クラスター化インデックス作成時の処理状況 (進行状況) を確認する の投稿も、実行中のクエリの情報を取得することによるアプローチです。
今の SQL Server では、実行中のクエリの情報を取得する方法がいくつも提供されていますので、これらを使いこなせると、問題が発生した際の強力な分析の材料とすることができるのではないでしょうか。