SQL Server のストアドプロシージャでは次のようなタイミングでコンパイル / リコンパイルが発生します。
- 初回実行時
- 実行プランがキャッシュアウトされた後の初回実行
- 統計情報が更新された後の実行
上記のケースの中で「1.」「2.」については発生すると複数のセッションで同時実行性の低下につながるケースがあります。
どのような情報が出力されていると、「1.」「2.」のケースで同時実行性が低下している可能性があるのかをまとめておきたいと思います。
今回はストアドプロシージャを対象としており、アドホッククエリやパラメーター化クエリについてはこの動作とは異なってきます。
Contents
COMPILE ロックが発生しているかを確認する
複数のセッションで同一のストアドプロシージャを実行した場合、上述の「1.」「2.」のケースでは、ストアドプロシージャのコンパイルが完了するまで「コンパイルロック」が発生します。
これはセッションの待機情報から確認できるのですが、sys.dm_exec_requests の 「wait_resource」から確認することができます。
待機情報を確認した際に次のように wait_resource に「COMPILE」の情報が出力されている場合、該当のオブジェクト ID のストアドプロシージャ (今回のケースであれば、object_id = 994102582) で、コンパイル時のロックが発生したことが確認できます。
コンパイルが発生した場合、該当のストアドプロシージャを実行しようとした複数のセッションで上記のような待機事象が発生し、同時実行性が低下することになります。
コンパイルが短時間 (or 瞬時) で終わるのであれば、このロックの発生による同時実行性の影響は無視できるものとなりますが、コンパイルに時間がかかるようなストアドプロシージャの場合は、待機時間が長くなり、同時実行性の低下の影響が大きくなってきます。
コンパイルに時間がかかるようなクエリは、「プランガイド」「クエリストアによる実行プランの強制」等を使用し、コンパイル時間を短縮させることを検討する必要があるのではないでしょうか。
ストアドプロシージャのコンパイルを誘発させる方法としてプランのキャッシュアウトを実行する際の問題
冒頭で記載した「1.」「2.」のうち、「1.」に関しては初回実行時の制約となり、ストアドプロシージャの初回実行時には避けられれないかと思います。
「2.」に関しては、初回実行時以外で発生する動作となり、実運用では次のケースで発生する可能性があるのではないでしょうか。
- メモリが不足し実行プランがキャッシュアウトされる
- DBCC FREEPROCCACHE を実行し、手動で実行プランをキャッシュアウトさせる
この動作の中で「2.」による方法については、ストアドプロシージャのコンパイルを誘発させるために使用することがあります。
DBCC FREEPROCCACHE はプランキャッシュから実行プランをキャッシュアウトさせるコマンドとなりますが、実行時に plan_handle または、sql_handle を指定することで、特定の実行プランのみをキャッシュアウトさせることができます。
これを使用して特定のストアドプロシージャの実行プランをキャッシュアウトさせることで、ストアドプロシージャのコンパイルを誘発させることができます。
実運用では、コンパイル時に指定されたパラメータでは、大多数のパラメータを使用したストアドプロシージャの実行には適していない場合などに、この方法を使用しすることがあります。
しかし、前述のとおりキャッシュに存在しないストアドプロシージャの初回実行時にはコンパイルロックが発生するため、明示的にキャッシュアウトさせてコンパイルを誘発させる場合には同時実行性の低下が発生する可能性があります。
plan_handle / sql_handle を使用した DBCC FREEPROCCACHE によるプランのキャッシュアウトを使用したクエリのコンパイル誘発は実装は楽なのですが、コンパイルロックの発生による同時実行性の低下が発生する可能性を考慮しておく必要があります。
プランをキャッシュアウトさせることなく新し実行プランの作成を誘発する他の方法
プランをキャッシュアウトさせて、ストアドプロシージャのコンパイルを誘発させる方法はコンパイルロックが発生する可能性があります。
コンパイルが短時間で完了するのであればコンパイルロックの発生は問題となりませんが、コンパイルに時間がかかるストアドプロシージャの場合はプランガイドやプランの強制を使用してコンパイルの時間を短時間にするような対策を打っておかないと、同時実行性を低下させる要因となる可能性があります。
キャッシュアウトさせる以外のクエリコンパイルの誘発としては「統計情報を更新することによるリコンパイルの誘発」があり、こちらの方法を使用できるのであれば、この方法を使用したほうが良いかと思います。
私が確認した範囲では、統計情報の更新によるリコンパイルによる実行プランの再生についてはコンパイルロックは発生していないようでした。
統計情報の更新によるリコンパイルの誘発は「リコンパイルしたいストアドプロシージャで使用されている統計情報」を把握しておく必要があり、「効率が低下したストアドプロシージャを自動的にコンパイルできる状態にしたい」というような自動化を目的として実装するのは難しいです。(実装できないことはないのですが、コンパイルを誘発したいストアドプロシージャで使用されている統計情報を自動的に判別するのに手間がかかります)
「実行効率が低下しやすいストアドプロシージャは洗い出せている」場合などには、該当のストアドプロシージャで使用している統計情報を手動で更新 (UPDATE STATISTICS の実行) することで、クエリのリコンパイルを誘発させ、新しい実行プランを生成させることができます。
可能であればこちらの方法で実行プランの再作成を誘発させたほうが良いかと考えています。
この方法でも統計情報の再作成時に METADATA の排他ロックはかかっているはずですが、DBCC FREEPROCCACHE を使用してコンパイルロックが発生する状態と比較して、同時実行性の低下が顕著に発生するという状態は確認できませんでした。
ワークロードやデータサイズによって異なる傾向も出るかと思いますが、本投稿の検証では、キャッシュアウトした場合と比較して、手動の統計更新によるリコンパイルのほうが同時実行性の低下が発生する頻度は低かったです。
まとめ
ストアドプロシージャは実行時に指定されたパラメーターの値によってクエリがコンパイルされ実行プランの生成が行われます。
そのため、特定のパラメーターのパターンに特化した値でクエリのコンパイルが実行されると、大多数のクエリには不適切なプランとなり、実行効率が低下するというようなケースがあります。
このような状態になった場合、クエリのコンパイルを誘発させて実行プランを再生成する必要がありますが、該当のストアドプロシージャをリコンパイルさせるとコンパイルロックの発生につながる可能性があります。
今回は記載していませんが、ストアドプロシージャ内のステートメント単位でリコンパイルさせるという方法もありますが、この方法は通常時の実行効率を最適化するという観点では問題があります。
実行回数が多いストアドプロシージャの実行プラン変更については、統計情報の更新によるリコンパイルにより、実行プランの変更を検討してみるのもよいのではないでしょうか。