ストアドプロシージャを明示的にリコンパイルさせる方法として ストアド プロシージャの再コンパイル に記載されている sp_recompile を使用するという方法があります。
ストアドプロシージャはコンパイル時に指定されたパラメーターによって実行プランが生成されるため、コンパイル時に指定されたパラメーターが大多数の実行に対してマイノリティな設定の場合、頻繁に実行するクエリに対しては効率の悪い実行プランが生成されることがあります。
このような場合は、ストアドプロシージャをリコンパイルさせることで新しい実行プランを生成することがあります。
このリコンパイルを同時実行性の低下をできるだけ抑えて実行するにはどのような方法が考えられるでしょうか。
sp_recompile / DBCC FREEPROCCACHE を使用するのは控える
前述のとおり、sp_recompile を使用することで対象のテーブルやストアドプロシージャをリコンパイル対象とすることができます。
そのほかにも該当のストアドプロシージャのハンドルを指定して DBCC FREEPROCCACHE を実行するという方法があります。
どちらの方法も、基本的な考え方としては「該当のストアドプロシージャの実行プランをキャッシュアウトさせる」ことでリコンパイルが誘発されているかと思います。
これらの方法でリコンパイルを誘発させる場合、同時実行数が多いストアドプロシージャでは大量のコンパイルロックが発生する可能性があります。
コンパイル時間が短いストアドプロシージャであれば、コンパイルロックの競合が発生している時間は短時間となり同時実行性の低下は抑えられる可能性があります。
しかし、コンパイル時間が長いクエリの場合は、上記のように数秒単位でコンパイルロックが発生し、コンパイルが完了するまで該当のストアドプロシージャの利用がブロックされるため頻繁に実行されるストアドプロシージャの場合には同時実行性の低下が無視できない時間発生する可能性があります。
統計情報を更新することによるリコンパイルの誘発
sp_recompile / DBCC FREEPROCCACHE は実行プランがキャッシュからクリアされるため、初回コンパイル相当の動作によりストアドプロシージャのコンパイルが行われます。
このようなコンパイルが発生した場合はコンパイルロックが発生し、コンパイルが完了するまでストアドプロシージャを実行するセッションでブロッキングが発生し、同時実行性が低下します。
リコンパイルの誘発方法として、もう一つ代表的な方法としては「統計情報の明示的な更新によるリコンパイルの誘発」があります。(ストアドプロシージャがリコンパイルされる理由については、SP:Recompile イベント クラス に記載されています)
クエリ内でで使用されている統計情報を明示的に更新することで、クエリのリコンパイルを誘発させる方法となります。
UPDATE STATISTICS を実行することで統計情報の更新を明示的に実行することができます。
SQL Server 2022 での確認となりますが、既に実行プランがキャッシュされているクエリキャッシュで使用されている統計情報の更新が行われると次のような動作でクエリの実行が行われるようです。
- 最初に実行したセッションでは、クエリのコンパイルによる実行時間の増加が発生する
- それ以外のセッションでは、コンパイル前の実行プランによる実行が行われ、コンパイルが完了しなくてもクエリの実行が行われる
プランキャッシュがクリアされる方法でコンパイルを誘発した場合、「1.」「2.」の両方のパターンがコンパイルが完了するまで実行がブロックされます。
実行プランがプランキャッシュ上に存在する場合、コンパイルを発生させた「1.」については、コンパイルによる待機が発生します。しかし、プランキャッシュがクリアされる方法でコンパイルロックによる競合が発生していた「2.」についてはコンパイルロックが発生せず、コンパイル直前にキャッシュされていた実行プランでクエリの実行が行われ、コンパイルが完了したタイミングで新しい実行プランで実行が行われます。
どの統計情報を更新すればリコンパイルが誘発されるかを把握しておく必要がありますが、統計情報の更新を実施してリコンパイルを誘発させたほうが同時実行性の低下は抑えられた形で新しいプランを生成できるのではないでしょうか。