SQL Server では、「OPTION (RECOMPILE)」のクエリヒントを使用することで、明示的に SQL ステートメントをリコンパイルすることができます。
パラメータースニフィングにより、パラメーターを使用したクエリで、実行タイミングによって大きく件数が変わる場合などに、コンパイルによる再利用効率とのトレードオフを考慮してクエリヒントをケースが多いと思いますが、それ以外でも RECOMPILE のヒントによって動作が変更されるケースがあります。
RECOMPILE の説明には次のような記載があります。
RECOMPILE
SQL Server データベース エンジン に、クエリの新しい一時的なプランを生成し、クエリ実行完了直後にそのプランを破棄するよう指示します。 生成されたクエリ プランは、RECOMPILE ヒントを指定しないで同じクエリを実行したときにキャッシュに格納されるプランを置き換えません。 RECOMPILE を指定しない場合、データベース エンジンはクエリ プランをキャッシュして再利用します。 クエリ プランをコンパイルする場合、RECOMPILE クエリ ヒントは、クエリ内のローカル変数の現在値を使用します。 クエリがストアド プロシージャ内にある場合は、任意のパラメーターに渡された現在値を使用します。
ポイントとなるのが「クエリ プランをコンパイルする場合、RECOMPILE クエリ ヒントは、クエリ内のローカル変数の現在値を使用します。 クエリがストアド プロシージャ内にある場合は、任意のパラメーターに渡された現在値を使用します。」の個所ですね。
この辺の動作をすっかり忘れていて、処理時間に大きく影響が出たクエリを作ってしまったので、実際のクエリをベースに確認していきます。
クエリ内で使用している条件を変更しやすいように次のように変数化したクエリを実行しています。
この変数をクエリ内で条件として使用しています。
このようなクエリを実行した場合に「OPTION (RECOMPILE)」の有無によってどのようにクエリの実行が変わるでしょうか。
クエリの再コンパイルだけであれば、それほど変わらないのではと思った方もいるのではないでしょうか？
元々のクエリがインデックスを考慮せず、適当に書いていたクエリなので、最初から非効率なクエリとなっているのですが、「OPTION (RECOMPILE)」を使用した場合は、次の処理時間となっています。
それでは、「OPTION (RECOMPILE)」を外すとどうなるでしょうか。
通常の実行ですと次のようになります。
取得件数は変わっていないのですが、大幅に処理時間が変わっていますね。
この違いはコンパイルコストのみに起因しているわけではありません。
この動作の違いが、上記で引用している「クエリ内のローカル変数の現在値を使用します」の動作になります。
「OPTION (RECOMPILE)」を指定しない場合、クエリの再利用が考慮されることになります。
そのため、変数化した箇所についてはパラメーターとしての利用が行われます。
実際の実行プランの内容がこちらになります。
実行プラン内にパラメーターリストが生成され、
指定したパラメーターが、実行プラン内の途中のフィルターの条件として利用されています。
(アクティブにしていないアイコンのフィルターの方で「@Req」が使用されています)
それでは、「OPTION (RECOMPILE)」を使用した場合は、どのような実行プランが生成されているでしょうか。
リコンパイルを指定した場合、パラメーターリストの生成は行われていません。
それでは、変数に指定した内容はどのように使用されているのでしょうか？
「OPTION (RECOMPILE)」を指定した場合、変数に指定した文字列が展開された状態で述語に直接指定され、実行が行われます。
これは「P2.InstanceName = @Total」と指定した箇所が、「P2.InstanceName = ‘CPU Time:Total(ms)’」と、文字列リテラルを、直接記述した状態と等価の認識となっていることになります。
結合やインデックスをあまり考慮せず、力業で情報を取得するクエリを書いていたのがそもそもの原因なのですが、リコンパイルのクエリヒントの有無によって、データがフィルターされる個所が変わり、処理時間に大きな変化が出たパターンとなります。
「OPTION (RECOMPILE)」を使用した場合は、データ取得時にフィルターが行われ、使用しなかった場合は、クエリの後半でフィルターをするようなプランとなっているため、処理時間に大きな差が出ています。
「条件に文字列リテラルを指定した場合と、変数を指定した場合に処理時間に変化が出た」「OPTION (RECOMPILE) の有無によって、処理時間に変化が出た」というパターンで考えられる一例に合致したクエリ実行ですね。
実行プランを確認するようにしておけば、「どこで変化が起こったか？」を把握することができ、このような変化に気づくことができますので、プランを確認するようにしておけば原因に気づくことができるのではないでしょうか。