ストアドプロシージャのリコンパイルについて少しまとめてみたいと思います。
なお、今回の投稿ではステートメントレベルのリコンパイルについてはのぞいています。
パラメーター化クエリ (プリペアードクエリ) にも応用できる内容かと。
ストアドプロシージャや初回実行時にコンパイルされ、以降はコンパイルされたプランがメモリ上にキャッシュされ再利用されることになります。
キャッシュされたプランについては sys.dm_exec_query_stats / sys.dm_exec_procedure_stats / sys.dm_exec_cached_plans などから取得することができます。
これらの DMV では実行された回数のカウンターがありますのでプランが何回利用されているかを確認することができます。
ストアドプロシージャの実行プランはデータの分布状況やスキーマの定義状況によって初回コンパイル時のプランからは変えたほうが効率が良いことがあります。
そのために、実行プランのリコンパイル (再コンパイル) が行われることがあります。
再コンパイルの理由としては、実行プランのキャッシュと再利用 に記載されている以下のような理由がトリガーとなります。
- スキーマが変更されました。
- 統計が変更されました。
- コンパイルが遅延されました。
- SET オプションが変更されました。
- 一時テーブルが変更されました。
- リモート行セットが変更されました。
- FOR BROWSE 権限が変更されました。
- クエリ通知環境が変更されました。
- パーティション ビューが変更されました。
- カーソル オプションが変更されました。
- OPTION (RECOMPILE) が要求されました。
このほかにも sp_recompile や WITH RECOMPILE (作成時や実行時に指定) オプションを使用することでも明示的にリコンパイルすることが可能です。
ストアドプロシージャーの再コンパイル状況ですが、sys.dm_exec_query_stats の plan_generation_num から確認することができます。
再コンパイルされていない状態ですと、1 となっていますが、 
ストアドプロシージャのリコンパイルが発生することで増加していきます。 
sp_recompile でリコンパイルす場合、以下のパターンが考えられます。
- ストアドプロシージャで使用しているテーブル等をリコンパイル対象としてマークする
- ストアドプロシージャ自体をリコンパイル対象としてマークする
リコンパイル対象としてマークするオブジェクトによってストアドプロシージャのキャッシュの状態が変わってきます。
特定のテーブル (オブジェクト) を使用しているストアドプロシージャをまとめてリコンパイルしたい場合などは前者を使用します。
# 使用しているテーブルのスキーマ変更を行った場合も同様の動作となるかと思いますが。
この場合、リコンパイルが行われると、plan_generation_num が増加し、リコンパイルが行われたことを確認することができます。
また、このタイミングで creation_time も変更されますのでいつリコンパイルされたものなのかを確認することができます。
# キャッシュされているプランがリコンパイルされ実行状態の統計情報を継続して取得することができます。
後者の場合は plan_generation_num から確認することはできません。
ストアドプロシージャ自体をリコンパイル対象としてマークした場合にはキャッシュから対象のストアドプロシージャのプランが削除され、次回実行時にコンパイルが行われます。
# キャッシュされているプランがクリアされますので対象のストアドプロシージャの事項状態の統計もクリアされます。
次に WITH RECOMPILE を使用してリコンパイルをした場合を考えてみます。
WITH RECOMPILE を使用して作成されたストアドプロシージャについてはプランがキャッシュされません。
そのため、毎回コンパイルされることになります。
# ストアドプロシージャ全体を WITH RECOMPILE で実行した場合も調べた感じでは同様の動作となっていました。以前べたときは特定のパラメーターのプランとしてキャッシュされたような気がしていたのですが。
RECOMPILE データベース エンジンでこのプロシージャ用のクエリ プランをキャッシュせず、プロシージャが実行されるたびにコンパイルされるようにすることを示します。 再コンパイルを強制する理由の詳細については、「ストアド プロシージャの再コンパイル」を参照してください。 このオプションは、FOR REPLICATION を指定した場合または CLR プロシージャには使用できません。 データベース エンジンでプロシージャ内にある個々のクエリに対するプランを破棄するには、クエリの定義で RECOMPILE クエリ ヒントを使用します。 詳細については、「クエリ ヒント (Transact-SQL)」を参照してください。
簡単に調べただけでもいろいろなパターンが出てきます。
クエリの実行は奥が深いですね。