SQL Server のクエリの実行方法としてストアドプロシージャを使用することができます。

ストアドプロシージャを使用することのメリットとしては、次のような内容があります。

アプリケーションから SQL Server に送信されるテキストの削減によるトラフィックの効率化

クエリのパラメーター化による、SQL インジェクションの保護
コードの再利用性の向上
クエリコンパイルの削減によるパフォーマンスの向上

本投稿では「4.」について、極端な例を題材にして、ストアドプロシージャのリコンパイルについてみていきたいと思います。

コンパイルとリコンパイル

ストアドプロシージャの実行時に、実行プランが作成されるタイミングとしては、「コンパイル」「リコンパイル」の 2 種類のイベントがあります。

コンパイル

コンパイルは、ストアドプロシージャの実行プランが「メモリ上にキャッシュされていない場合」に発生するイベントです。
メモリ上にキャッシュされていない理由としては、一般的に 2 種類の要因があります。

クエリの初回実行時
クエリが一度は実行されがたが、クエリプランがキャッシュアウトされた

「1.」が、一般的なクエリのコンパイル理由になるのではないでしょうか。
クエリ実行時にメモリに実行したクエリの実行プランがキャッシュされていない場合、実行プランの生成が必要となるため、「実行時に指定されたパラメーターを使用して」実行プランが作成されます。

この動作は「パラメータースニッフィング」と呼ばれ、実行プランを生成するトリガーとなった実行タイミングで指定されていたクエリパラメーターに最適化された形で、実行プランの生成が行われます。
(この動作は、クエリヒントの「OPTIMIZE FOR」を使用することで、挙動を多少制御することができます)

「2.」についても、基本的な考え方は「1.」と同じです。
SQL Server のプランキャッシュ (クエリキャッシュ) は有限のメモリ領域ですので、すべてのクエリの実行プランをメモリ上にキャッシュすることはできません。

大量のデータアクセスにより、データキャッシュが肥大化
大量のアドホッククエリの実行により、プランキャッシュが肥大化

このような動作が行われた場合、各用途に必要となるメモリを確保するため、参照頻度の低いオブジェクトをキャッシュアウトさせ、利用可能なメモリの確保が行われます。
この、「キャッシュアウト」が発生した場合に、実行プランから、プランキャッシュがキャッシュアウトされる可能性もあり、キャッシュアウトされた実行プランが次回実行された場合には「クエリの読回実行時」相当の動作が行われますので、クエリのコンパイルが発生します。

データを他のシステムにエクスポートするための処理を実行した場合などには、大量のデータをメモリ上にキャッシュする必要がありますので、プランキャッシュが圧迫されて、キャッシュアウトされる可能性が高くなるかもしれませんね。

どの程度、クエリキャッシュから実行プランがキャッシュアウトをされているかを確認するかについては、sys.dm_os_memory_cache_clock_hands を確認する方法があります。
この DMV から、メモリのスイープの状況が確認できるため、スイープにより削除されたエントリの数を確認することで、秒間でどの程度のクエリがキャッシュアウトされているかを確認するという手法があります。

このような「メモリ上に存在しないクエリの実行時」には「コンパイル」が発生します。

コンパイル時のパラメーターを確認する

クエリのコンパイル時に「どのようなパラメーターでコンパイルが行われたか？」を確認する方法についても軽く触れておきたいと思います。
コンパイル時のパラメーターについては、クエリキャッシュ / クエリストアの両パターンで確認をすることができますが、今回はメモリ上のデータから確認する方法について、触れておきます。
(確認のポイントはクエリストアも同じです。)

クエリキャッシュからクエリの情報を取得する際には、次のようなクエリを実行して、取得することがあるのではないでしょうか。

SELECT 
    qs.plan_generation_num,
    qs.creation_time,
    qs.last_execution_time,
    qs.execution_count,
    qs.total_worker_time,
    qs.total_elapsed_time,
    qp.query_plan,
    qs.plan_handle
FROM
    sys.dm_exec_query_stats AS qs
    OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE
    qp.objectid = OBJECT_ID('Q1_Param')
GO

query_plan からクエリの実行プランを取得することができます。
実行プランの XML には「ParameterList」という項目が含まれており、「ParameterCompiledValue」からコンパイル時に指定されたパラメーターを確認することができます。

トラブルシューティングを行う際には「どのようなパラメーターによってコンパイルされた実行プランで問題が発生しているか」が、再現を行う際の重要なポイントとなることがあります。
実行プランがどのようなパラメーターによって生成されたものなのかを把握する方法を知っていることは重要です。

統計情報が変わっていると再現できるかは変わりますが、類似の統計情報であれば、取得したコンパイル時のパラメーターを使用して、クエリの実行効率の再現を行うことができる可能性があります。

リコンパイル

次に、「リコンパイル」について考えてみましょう。
リコンパイルの公式のドキュメントはストアドプロシージャの再コンパイルです。

メモリ上にキャッシュされているクエリは、何らかの要因で実行プランを再生成する必要が出てきます。
この「メモリ上にキャッシュされている実行プランを、新しい実行プランに変更する」動作が「リコンパイル」となります。
リコンパイルが発生した場合「リコンパイルの発生要因となったクエリに指定されていたパラメーター」で実行プランが再作成されますので、指定されているパラメーターによっては実行プランが大きく変化する可能性があります。

リコンパイルが発生する理由

キャッシュされているクエリがリコンパイルされる理由はいくつかあります。
ドキュメントでは SP:Recompile イベントクラスにリコンパイルが発生する理由について記載が行われています。

上記の記載は SQL Server プロファイラー (SQL トレース) の時代のものですので、情報としては少し古いものです。
現在、使用している SQL Server でどのような要因により、リコンパイルが発生する可能性があるかは、次のクエリで確認ができます。

select * from sys.dm_xe_map_values WHERE name = 'statement_recompile_cause'

SQL Server の拡張イベントには「sql_statement_recompile」というイベントがあり、このイベントの中で、「statement_recompile_cause」という情報を取得することができます。
次の画像は、SQL Server 2019 の拡張イベントから取得できたリコンパイルの理由です。

SQL Server 2019 では、リコンパイルが発生する理由としては 20 種類あるようですね。
キャッシュされているクエリがこれらの条件に合致した場合は、クエリのリコンパイルが発生します。

リコンパイルが発生しているかの確認

先ほど、コンパイル時のパラメーターを取得する際に使用したクエリで情報の取得を行ってみます。

SELECT 
    qs.plan_generation_num,
    qs.creation_time,
    qs.last_execution_time,
    qs.execution_count,
    qs.total_worker_time,
    qs.total_elapsed_time,
    qp.query_plan,
    qs.plan_handle
FROM
    sys.dm_exec_query_stats AS qs
    OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE
    qp.objectid = OBJECT_ID('Q1_Param')
GO

キャッシュされているクエリの実行プランには「plan_generation_num」という項目を持っています。

この項目が 1 でない場合は、クエリのリコンパイルが発生し、新しいプランが作成されていることになります。
また、リコンパイルが発生すると sys.dm_exec_query_stats の情報がクリアされ、現在の世代のプランについての累計値となるため、過去の世代のプランのクエリの実行情報については、キャッシュからはわからなくなります。

SQL Server は「ステートメント単位」にクエリの情報をキャッシュします。
そのため、クエリが複数のステートメントで構成されている場合、各ステートメントについてキャッシュの情報が格納され、リコンパイルについても「該当のステートメントのみ」がリコンパイルされる形となります。

これは、ストアドプロシージャも同様で、ストアドプロシージャが複数のステートメントで構成されている場合は、コンパイル / リコンパイルはステートメント単位で行われます。

極端な例でリコンパイルを発生させて、影響を確認してみる

ここからが本題です。

リコンパイルが発生するとどのような影響が出るかを、極端な例を使って実際に確認してみます。
使用するクエリはこちらです。

CREATE OR ALTER PROCEDURE Q1_Param
    @p0 datetime
WITH RECOMPILE
AS
BEGIN
DECLARE @p1 datetime =(SELECT MAX(C1) FROM T1)

SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY,
 SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE,
 SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE, AVG(L_QUANTITY) AS AVG_QTY,
 AVG(L_EXTENDEDPRICE) AS AVG_PRICE, AVG(L_DISCOUNT) AS AVG_DISC, COUNT(*) AS COUNT_ORDER
FROM LINEITEM
WHERE L_SHIPDATE <= dateadd(dd, -90, @p0)
GROUP BY L_RETURNFLAG, L_LINESTATUS
ORDER BY L_RETURNFLAG,L_LINESTATUS
END

TPC-H の Q1 のクエリをパラメーターをつけて、T1 というテーブルへの参照を追加して、2 ステートメントが実行されるようにストアドプロシージャ化したものです。
このストアドプロシージャは「WITH RECOMPILE」を設定していますので、実行するたびに、ストアドプロシージャはリコンパイルされます。

それでは、このストアドプロシージャを OSTRESS を使用して、複数のセッションで実行した際の処理時間を確認してみます。
WITH RECOMPILE を使用した場合、処理時間は次のようになりました。

WITH RECOMPILE を使用していない場合、は次の処理時間となります。

WITH RECOMPILE がない方が処理時間が大きく短く (00:03:19 → 00:00:07) なっていますね。

WITH RECOMPILE を設定して、クエリのリコンパイルが発生するようにしたことで、実行プランが頻繁に変わるようにすることができ、パラメータースニッフィングにより、特定のパラメーターに特化したプランが生成された場合の補正を自動的に行うことができます。
しかし、実行プランを毎回生成することによって、処理効率が著しく低下しています。

WITH RECOMPILE を使用した場合の処理効率の問題

それでは、WITH RECOMPILE を使用した場合の処理効率の問題はどのようなことが考えられるでしょうか。

WITH RECOMPILE は「クエリを毎回リコンパイルする」のではなく、「クエリをキャッシュしないことで毎回コンパイルさせる」という動作となっています。

そのため、「WITH RECOMPILE」を設定したストアドプロシージャについては、sys.dm_exec_query_stats から実行状態を確認することができなくなります。
これにより、クエリの実行効率を確認するための情報がキャッシュから確認できないことになりますのでトラブルシューティングの即応性の低下につながる可能性があります。

話を戻して「WITH RECOMPILE」を設定したことで、どのような要因により実行効率が低下したのでしょうか？
今回は一例ですが、このような実行効率の低下が発生する可能性があります。

次の情報は、WITH RECOMPILE を設定しているクエリを実行している際に発生している待機事象の情報を取得したものです。

クエリの実行による CPU を使用していることで、「SOS_SCHEDULER_YIELD」が発生していますが、これ以外に「RESOURCE_SEMAPHORE_QUERY_COMPILE」が発生しています。
sys.dm_os_wait_stats には、この待機事象は次のように記載されています。

過度なコンパイルによりスロットルの制限に達した場合に、本待機事象が発生する可能性があります。
それでは、実際に状態を確認してみたいと思います。
sys.dm_exec_query_optimizer_memory_gateways から、クエリ最適化のためのゲートウェイの使用状況を確認してみます。

「Small Gateway」で「waiter_count」が上昇していることが確認できます。
過度なコンパイルより、コンパイルゲートウェイに待機が発生し、RESOURCE_SEMAPHORE_QUERY_COMPILE の待機事象が発生したと考えられます。

頻繁に実行されるクエリをコンパイル対象とすると、このようなクエリ最適化の同時実行の問題で待機が発生する可能性があります。

ステートメント単位のリコンパイルに変更してみる

それでは、ストアドプロシージャ全体ではなく、ストアドプロシージャ内のステートメントにリコンパイルのオプションを設定してみたいと思います。

CREATE OR ALTER PROCEDURE Q1_Param
    @p0 datetime
AS
BEGIN
DECLARE @p1 datetime =(SELECT MAX(C1) FROM T1)

SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY,
 SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE,
 SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE, AVG(L_QUANTITY) AS AVG_QTY,
 AVG(L_EXTENDEDPRICE) AS AVG_PRICE, AVG(L_DISCOUNT) AS AVG_DISC, COUNT(*) AS COUNT_ORDER
FROM LINEITEM
WHERE L_SHIPDATE <= dateadd(dd, -90, @p0)
GROUP BY L_RETURNFLAG, L_LINESTATUS
ORDER BY L_RETURNFLAG,L_LINESTATUS
OPTION(RECOMPILE)
END

この場合も先ほどと同様に「RESOURCE_SEMAPHORE_QUERY_COMPILE」の待機は頻繁に発生しているのですが、ストアドプロシージャ全体をリコンパイルするよりは処理時間は短くなっています。

このストアドプロシージャは 2 ステートメントで構成されており、そのうちの 1 ステートメントのみをリコンパイル対象としています。
そのため、ストアドプロシージャ全体をリコンパイルとしてマークした時より、リコンパイルのオーバーヘッドが下がっています。
これが 1 ストアドプロシージャ / 1 ステートメントの構成であれば、ストアド全体をリコンパイルとしてマークしても、ステートメント単位でマークしても処理時間に大きな変化はないのですが、複数ステートメントの場合は、リコンパイルが必要となるステートメントのみ、リコンパイルオプションを設定したほうが同時実行性の低下を抑えることができます。

また、ステートメントレベルのリコンパイル設定にした場合は、sys.dm_exec_query_stats でも情報の確認を行うことができます。

ステートメントレベルのリコンパイルの場合は、クエリがキャッシュされた状態になりますので、1 ステートメント / 1 ストアドプロシージャの場合は、

ストアドプロシージャに WITH RECOMPILE を設定して、クエリをキャッシュされないようにすることでメモリ負荷は削減
ステートメントに OPTION(RECOMPILE を設定して、クエリをキャッシュするようにして、メモリ負荷の削減にはつながらないが、情報取得の利便性を向上

というような使い分けができるのではないでしょうか。

気を付けなくてはいけないのは「どちらのパターンでもリコンパイル (コンパイル) を頻繁に発生させることで、特定のパラメーターに最適化された実行プランをキャッシュし続ける状態はなくなるが、リソースのオーバーヘッドが発生する」ということです。

実行の頻度が低いものであれば、問題はないのですが、秒間に数千実行されるようなクエリが毎回コンパイルされると、コンパイルによるオーバーヘッドは無視できないものになります。
全てのクエリをリコンパイルさせることで、特定のパラメーターに依存しない、実行プランの生成を行うということはリスクを伴うものですので、リコンパイル対象とするクエリは取捨選択したほうが良いかと。

通常のワークロードでリコンパイルを発生させる

先ほどは、明示的にリコンパイルを行うようにしていましたが、通常のワークロード下ではこれは、データベース管理者または開発者が、特定のクエリの問題解消のために実施するものとなります。
通常のワークロードで、リコンパイルが発生する要因としては、次の 2 種類が多いかと思います。

統計情報の更新 (Statistics changed)
実行プランがキャッシュアウトされることによる、キャッシュアウト後の初回実行時のクエリのコンパイル

キャッシュアウトされた場合は、通常のコンパイルと同一ですので、「統計情報の更新」によるクエリのリコンパイルが、実ワークロードでも発生する可能性が高いものではないでしょうか。

統計情報の確認

実行プランで使用されているクエリの統計情報については、「互換性レベル 120 (SQL Server 2014) 以降」の互換性レベルであれば、キャッシュされている実行プラン、推定 / 実際の実行プランから確認をすることができます。

対応している互換性レベルの実行プランを確認すると、「OptimizerStatsUsage」という項目があり、この項目を確認することで、「どの統計情報を使用して生成された実行プランなのか」を確認できます。

この情報は、残念ながらクエリストアに取得されている実行プランには含まれていませんので、確認が必要な場合は、クエリストア以外の方法で情報の取得を検討する必要があります。

クエリの実行プランのリコンパイルは、これらの統計情報が更新されるタイミングで発生します。
SQL Server の初期設定では、統計情報は「自動更新」されるのがデフォルトの動作となっています。
今の SQL Server は統計情報の自動更新が発生する閾値はデータ量に応じて変わるため、必ず 20% のデータが更新された場合に発生するというわけではないのですが、統計情報が更新されるタイミングについては、次のクエリで情報を取得することで判断できるケースがあります。

SELECT 
    s.name,
    s.stats_id,
    s.auto_created,
    s.no_recompute,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.steps,
    sp.modification_counter
FROM 
    sys.stats AS s
    OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE 
    s.object_id IN(OBJECT_ID('T1'), OBJECT_ID('LINEITEM'))
GO

統計情報のプロパティには、内部に「modification_counter」という項目を持っており、この項目から該当の統計情報に影響する変更の発生状況を確認することができます。
統計情報が更新 (自動更新) されると、「last_updated」が統計情報が変更されたタイミングに更新され、「modification_counter が 0」になります。

この変更タイミングを確認することで、統計情報がどのタイミングで変更されたのかを確認することができます。

SQL Server の統計情報の自動更新のデフォルトの動作は「同期的更新」となるため、統計情報の自動更新が発生している場合は、統計の更新が完了するまで、クエリの実行が完了しません。
この同期的更新による同時実行性に影響がある場合は、統計情報を「非同期的自動更新」にするというような対応も考えられます。(非同期更新にもメリット/デメリットがあります)

統計情報の更新によるリコンパイルの発生時の状況

クエリで使用している統計情報と統計情報の更新状況の確認方法はわかりました。
次にクエリのリコンパイルが発生した場合に、どのような事象が発生するのかを確認します。

この確認を実施する場合、次のような流れとなります。

テストを行うクエリを OStress 等で連続して実行した状態にする
その裏で、クエリで使用される統計情報に該当する列に更新を行う
更新完了後の初回実行時にクエリのリコンパイルが発生する

「2.」については UPDATE STATISTICS を実行して、手動で統計情報を更新することでも対応できます。
しかし、統計情報を手動で更新する場合、何らかのデータ変更を行っておかないと、想定する動作にならない可能性があります。
データの変更が一切行われていない状態で、UPDATE STATISTICS を実行してみたところ、クエリのリコンパイルが発生しませんでしたので、UPDATE STATISTICS でリコンパイルが発生しない場合は、「modification_counter」の値から、変更が発生しているか (0 より大きくなっているか) を確認してみると良いかと。

統計情報の変更により、リコンパイルが発生すると、拡張イベントで「sql_statement_recompile」を取得すると、「Statistics changed」がリコンパイルの理由としてイベントの発生が確認できます。

統計情報の更新による、リコンパイルの同時実行性の低下ですが、私が検証していた中では、「ACCESS_METHODS_ACCESSOR_CACHE」が発生するケースがありました。

同一のアドレスを示していますので、同一クエリのリコンパイルが発生した場合には、ラッチの待機として、何らかの同時実行性の低下は発生していそうです。
(統計情報の自動更新についてのラッチ待機なのかもしれませんが)

キャッシュアウト (または、キャッシュされていない) ことによるクエリのコンパイル発生時の状況

実行プランがキャッシュされていない、またはメモリが不足してキャッシュアウトされ、クエリがコンパイルされたことによる実行済みクエリのリコンパイル (厳密にはコンパイルですが) についても状況を確認してみます。

sp_recompile や、DBCC FREEPROCCACHE を使用して、実行済みのクエリをキャッシュアウトさせることで、コンパイルを誘発させます。

sp_recompile 'Q1_Param'
DBCC FREEPROCCACHE(<sql_handle>)

クエリのコンパイルが発生した場合、「subresource=COMPILE」のオブジェクトロックによる同時実行性の低下が発生が確認できます。

このことから、頻繁にストアドプロシージャでコンパイルが発生しているような場合は、コンパイルロックによる同時実行性の低下につながる可能性が考えられます。
ストアドプロシージャは SQL Server ではオブジェクトですので、コンパイル発生時の同時実行性の低下を避けるのはなかなか難しいのかもしれませんね…。

sp_recompile でテーブルを指定するのはできるだけ避ける

最後に sp_recompile について、触れておきたいと思います。

sp_recompile には次のように説明があります。

ストアドプロシージャ、トリガー、およびユーザー定義関数が次回実行時に再コンパイルされるようにします。これを行うには、プロシージャキャッシュから既存のプランを削除します。

上記の種類を sp_recompile に設定した場合は、プロシージャキャッシュからプランを削除することで、次回実行時にコンパイルを誘発させる動作となります。
sp_recompile には、object を指定することができます。

オブジェクトには、テーブルを指定することもできますが、テーブルを指定するのはできるだけ避けた方がよいかと思います。
大量のクエリを実行中に sp_recompile でテーブルを指定すると、次のようなロック競合が発生します。

大量のスキーマ共有ロックの待機が発生していますね

sp_recompile でテーブルに対してリコンパイルのマークを設定すると「スキーマの最終更新日付を変更することで、スキーマ変更が発生した状態とする」というような動作が行われているようで sp_recompile で「LCK_M_SCH_M」(スキーマ変更ロック) が取得されます。
テーブルに対してのリコンパイルマークは「Schema changed」により、リコンパイルを誘発させる動作となっているようです。

スキーマ変更ロックは、様々なロックと競合しますので、「sp_recompile 'LINEITEM'」のように、テーブルに対してリコンパイルマークを設定しようとすると、瞬間的に同時実行性が著しく低下する可能性が高いです。

意図的にリコンパイルを誘発させたい場合は、

sp_recompile で特定のストアドプロシージャをキャッシュアウトさせる
DBCC FREEPROCCACHE または、ALTER DATABASE SCOPED CONFIGURATION で特定の sql_handle のクエリをキャッシュアウトさせる (この方法はアドホック / パラメーター化クエリでも有効)
統計情報の手動更新を行い、Statistics changed により、リコンパイルを誘発させる

のどれかを使用したほうが良いのではないでしょうか。

最後に

クエリの実行プランは、再利用されることにより、消費されるリソースを抑えてクエリを実行することができますが、再利用されるクエリは「コンパイル時のパラメーターに依存する」形となります。
しかし、SQL Server のクエリの動作安定化のためには、実行時のパラメーターにより実行プランが生成されるのが望ましいですが、そのような挙動にした場合は、クエリ実行時のオーバーヘッドの増加は必ず発生します。

クエリ安定のための手法としては、次のような方法があります。

ステートメント単位のリコンパイルによる、特定のパラメーターに依存しないプランを都度生成 (OPTION (RECOMPILE))
クエリヒントによるプランの補正 (これには、OPTIMIZE FOR / OPTIMIZEFOR UNKNOWN の利用も含みます)
テーブルヒントによるプランの補正
[プランガイド] (https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/plan-guides?view=sql-server-ver15) によるプランの補正 (アプリケーション側に手を入れずに、DB 観点でプランを補正)
[クエリストア] (https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15#information-in-the-query-store) によるプランの補正
想定しないプランが生成された場合のリコンパイルの誘発 (sp_recompile, DBCC FREEPROCCACHE 等によるキャッシュクリア / sp_updatestats, UPDATE STATISTICS による統計情報の更新による Statistics changed によるリコンパイルの誘発)
統計情報の自動更新を無効化 (NORECOMPUTE) することによる、統計情報自動更新によるリコンパイルの抑制

本投稿でも処理時間を確認していますが、リコンパイルの発生を抑えることは、リソースの消費を抑え、クエリの処理速度を向上させることにつながることは間違いありません。
リコンパイルは必ずしも悪いものではありません。

リコンパイルを悪として捉えるのではなく、「実行されているワークロードに応じて、クエリを安定した性能で実行させるためにはどのようにすればよいか？」を考えることが重要ではないでしょうか。