SQL Server や SQL Database/Managed Instance の相談を受ける際に、クエリの実行プランが変化するタイミングについて質問を受けることがあります。
通常、パラメーター化されたクエリやストアドプロシージャーは、コンパイルされた実行プランを再利用して動作しています。「それまで使用していた実行プラン」を使わずに、「異なる実行プラン」を使用したということは、"そのタイミングでクエリのコンパイルが行われて、コンパイルされた結果として「異なる実行プラン」が選択された"ということになります。
この動作について考えることで、質問の答えになるのではないかと思うので、ここに情報を整理してみたいと思います。
コンパイルと再コンパイル
さて、コンパイルと再コンパイルという用語があります。ざくっとこの違いを抑えることにしましょう。
| 動作 | 説明 |
|---|---|
| コンパイル | クエリを実行する際に利用できる実行プランが存在しないので、実行プランを生成する動作 |
| 再コンパイル | 実行プランは存在するが、何らかの理由で再作成が必要と判断したので、実行プランを生成する動作 |
質問としては、「それまで使用していた実行プラン」がある前提なので、再コンパイルの発生を確認すれば、タイミングについて理解できるということになります.....って、実はここで 1 つ考慮した方が良い動作が。
実行プランは、プランキャッシュに保存されますが、キャッシュから消されることがあります。DBCC FREEPROCCACHE 等のコマンドを実行した場合や、データベースをクローズした場合などは、プランキャッシュから実行プランがパージされます。
もう一つのあり得るタイミングとしては、メモリプレッシャーが挙げられます。基本的には使用頻度の低い実行プランからパージされますが、メモリがひっ迫する状況では、使用頻度がある実行プランもパージされる可能性があります。
ということで、再コンパイルだけでなく、実行プランがパージされた結果、コンパイルが実行されている可能性もあることは意識してもいいかと思います。このパターンでは、この先に記述する再コンパイルのイベントは記録されません。
再コンパイルの理由
実行プランのパージが発生していない状況で実行プランが変化した場合、再コンパイルが走ったと判断できると思います。何らかの理由で再コンパイルが行われたわけですが、この再コンパイルの理由は、以下の公開情報に記載があります。
上記のドキュメントにある通り、いろいろ理由はありますが、調査していて目にする主な理由は「統計情報の更新」と「スキーマ変更」です。前者については、「統計の自動更新」がトリガーすることが殆どです。後者に関しては、主に「インデックスの再構築」によって主にトリガーされます(実際にテーブル構造を変更しても、もちろん起きます)。
再コンパイルの確認
上記の公開情報にも記載がありますが、拡張イベントの sql_statement_recompile イベントをキャプチャーすることで、再コンパイルの発生を確認できます。recompile_cause 列に、再コンパイルの理由が記載されるので、そこから判断することができます。
例えば、以下のような拡張イベントセッションを作成しておきます。
CREATE EVENT SESSION [track_recompile] ON SERVER
ADD EVENT sqlserver.sql_statement_recompile(
ACTION(sqlserver.session_id,sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'track_recompile.xel')
WITH (MAX_EVENT_SIZE=1048576 KB)
実行プランをキャッシュした状態で、参照してるテーブルのインデックスを再構築し、もう一度クエリを実行すると、下記の様に Schema changed を理由にした再コンパイルが確認できます。
実行プランが変更されたタイミングで再コンパイルが行われていたかを確認するには、このイベントをキャプチャーすれば調査可能です。
実行プランの変化
再コンパイルが行われたからと言って、必ずしも実行プランが変化するわけではありません。前と同じ実行プランが選択されることもあります。また、実行プランが変化しても、実行時間やリソース使用量に差がないこともあります。
そもそも、この手の質問に関しては、「それまでより遅くなった」とか「タイムアウトするようになった」という話が背景にあります。そういう状況でもない限り、実行プランが変化していても気にしないものです。実際、運用環境のクエリストアを見ると、1 つのクエリが複数の実行プランを使用している状況も良く目にします。
実は気になっているのは「クエリの実行プランが変化するタイミング」という話ではなく、「適してない実行プランが何故選択されたのか」という点なのだと思います。
実行プランは、「その時のスキーマ、統計情報、クエリ、パラメータ、設定を基に実行プランを作成した時に、推定コストが一番低かった実行プラン」が選択されるので、再コンパイルでなくとも、同じ実行プランは選択されうるわけです。ということで、実は「実行プランが変化するタイミング」について調査するのではなくて、そのクエリについては「適さない実行プランが選択される可能性がある」として、対応を考えた方が効率的ではないかと思います。
対応について
ここからは、いろいろな件を対応してみた個人の感覚でのお話になってしまいますが、正直こうしたクエリの多くはチューニングが不足しているクエリが多いです。
そのクエリに適したインデックスが存在しないため、他のインデックスを組み合わせて何とか実行をしているようなパターンでは、統計情報の変化によって使用するインデックスががらりと変わり、実行プランの形が大きく変化します。まずは、基本に立ち返り、クエリで使用できるインデックスの有無を確認するのが対応になります。データ構造上、あるインデックスを使用した方が効率的であるとわかっているのであれば、インデックス ヒントを使用することを検討してください。
もちろん、ある程度チューニングをしてあるにもかかわらず、実行プランが変化して問題になることもあります。このパターンの多くは、データの分布に偏りがあり、パラメータによって対象となるデータが大きく異なるような場合に発生する問題で、パラメータ依存プランの問題と呼ばれています。詳細については、以下の公開情報を確認してください。
パラメータ依存プランの問題に関しては、SQL Server でも長い間、ペインポイントとして認識されています。SQL Server 2022 からは、「パラメーターに依存するプランの最適化」という機能が実装されて、特定のパターンにおいては、問題を緩和できるようになってきています(SQL Database/Managed Instance でも使用できます)。
「パラメーターに依存するプランの最適化」では救えない状況でも、自動チューニングの「自動プラン修正」によって、直前の実行プランを強制することで影響を緩和できる可能性があります。こちらの機能は、SQL Server 2017 以降で使用可能なので、オンプレの SQL Server を使用している場合、有効化することも検討してみてください。
ちょっとタイトルと異なる内容に着地した感じですが、この情報が誰かのお役に立てれば幸いです。
Discussion