実行プランのキャッシュと再利用

  • [アーティクル]

更新 : 2006 年 7 月 17 日

SQL Server 2005 には、実行プランとデータ バッファの両方を格納するためのメモリのプールが用意されています。実行プランまたはデータ バッファに割り当てられるプールの割合は、システムの状態によって動的に変動します。実行プランの格納に使用されるメモリ プールの部分をプロシージャ キャッシュといいます。

SQL Server 2005 の実行プランは、主に次の要素から構成されます。

  • クエリ プラン
    実行プランの大部分は、任意の数のユーザーが使用できる再入可能な読み取り専用のデータ構造体です。これをクエリ プランといいます。クエリ プランにはユーザー コンテキストは格納されません。また、メモリに複数のクエリ プランのコピーが配置されることはありません。すべての直列実行に 1 つのコピーが使用され、すべての並列実行に 1 つのコピーが使用されます。並列実行用コピーは、並列処理の次数に関係なくすべての並列実行に適用されます。
  • 実行コンテキスト
    クエリを現在実行しているユーザーごとに、パラメータ値など、実行に固有のデータを保持するデータ構造体が用意されています。このデータ構造体を実行コンテキストといいます。実行コンテキストのデータ構造体は再利用されます。ユーザーがクエリを実行したときに使用されていない構造体が 1 つある場合、新しいユーザーのコンテキストでその構造体が再初期化されます。

同じクエリで異なるリテラルの実行コンテキスト

SQL Server 2005 で任意の SQL ステートメントを実行すると、まずリレーショナル エンジンにより、プロシージャ キャッシュが調査され、同じ SQL ステートメントの既存の実行プランが存在するかどうかが確認されます。検出された既存のプランは SQL Server 2005 によって再利用されます。これにより、SQL ステートメントを再コンパイルする際のオーバーヘッドが少なくなります。既存の実行プランが存在しない場合、SQL Server 2005 によってクエリの新しい実行プランが生成されます。

SQL Server 2005 には、特定の SQL ステートメントの既存の実行プランを検索する効率的なアルゴリズムが用意されています。ほとんどのシステムでは、このスキャンで使用される最低限のリソースの量が、すべての SQL ステートメントをコンパイルせずに既存の実行プランを再利用することで節約できるリソースの量を超えることはありません。

新しい SQL ステートメントをキャッシュ内の使用されていない既存の実行プランと照合するアルゴリズムでは、すべてのオブジェクト参照が完全に修飾されている必要があります。たとえば、次の SELECT ステートメントのうち 2 番目のステートメントは既存の実行プランと一致しますが、最初のステートメントは一致しません。

SELECT * FROM Contact

SELECT * FROM Person.Contact

個々の実行プランが再利用される確率は、SQL Server 6.5 以前のバージョンよりも SQL Server 2000 と SQL Server 2005 のインスタンスの方が高くなります。

実行プランの保存期間

生成された実行プランは、プロシージャ キャッシュに保存されます。空き領域が必要な場合のみ、SQL Server 2005 により、古い使用されていないプランがキャッシュから削除されます。クエリ プランにも実行コンテキストにも、構造体のコンパイルに要するコストを示すコスト係数があります。これらのデータ構造体には、時効フィールドも含まれています。時効フィールドの値は、オブジェクトが接続により参照されるたびに、コンパイルのコスト係数分だけ増加します。たとえば、コスト係数が 8 のクエリ プランが 2 回参照された場合、その時効フィールドの値は 16 になります。レイジーライター プロセスは、プロシージャ キャッシュ内のオブジェクトのリストを定期的にスキャンします。その際、レイジーライターは、各オブジェクトの時効フィールドの値をスキャンごとに 1 ずつ減少させます。サンプル クエリ プランの時効フィールドの値は、別のユーザーがそのプランを参照しない限り、プロシージャ キャッシュの 16 回のスキャンの後に 0 まで減少します。次の条件が満たされると、レイジーライター プロセスによりオブジェクトの割り当てが解除されます。

  • メモリ マネージャでメモリが必要になったが、使用可能なメモリが現在すべて使用されている場合
  • オブジェクトの時効フィールド値が 0 の場合
  • オブジェクトが現在接続によって参照されていない場合

時効フィールドの値はオブジェクトを参照するたびに増加するので、参照頻度が高いオブジェクトの時効フィールドの値は 0 になることがなく、時間が経過してもキャッシュから削除されることはありません。参照頻度が低いオブジェクトはすぐに割り当てを解除できる状態になります。ただし、他のオブジェクトへのメモリの割り当てが必要になるまでは、実際には割り当ては解除されません。

実行プランの再コンパイル

データベースに変更を加えた場合、データベースの新しい状態によっては、実行プランの効率が低下したり、実行プランが無効になったりします。実行プランが無効になるような変更は SQL Server によって検出され、その実行プランは無効としてマークされます。このため、クエリを実行する次回の接続用に新しい実行プランを再コンパイルする必要があります。実行プランが無効になるのは、次の場合です。

  • クエリ (ALTER TABLE および ALTER VIEW) によって参照されるテーブルまたはビューに変更を加えた場合
  • 実行プランで使用されるインデックスに変更を加えた場合
  • UPDATE STATISTICS などのステートメントを使用して明示的に生成した実行プラン、または自動的に生成された実行プランによって使用される統計を更新した場合
  • 実行プランで使用されるインデックスを削除した場合
  • sp_recompile を明示的に呼び出した場合
  • クエリによって参照されるテーブルを変更する他のユーザーが、INSERT ステートメントまたは DELETE ステートメントを使用して大量の変更をキーに加えた場合
  • トリガを含むテーブルで、inserted テーブルまたは deleted テーブルの行数が大幅に増加する場合
  • WITH RECOMPILE オプションを使用してストアド プロシージャを実行する場合

主として再コンパイルが必要になるのは、ステートメントの正確性を維持したり、より処理速度が速いクエリ実行プランを取得したりする場合です。

SQL Server 2000 では、バッチ内のステートメントが原因で再コンパイルが実行されるときに、そのバッチがストアド プロシージャ、トリガ、アドホック バッチ、または準備されたステートメントのいずれを使用して送信されたかどうかに関係なく、常にバッチ全体が再コンパイルされます。SQL Server 2005 では、再コンパイルの原因となったバッチ内のステートメントのみが再コンパイルされます。この違いにより、SQL Server 2000 と SQL Server 2005 の再コンパイル回数を比較することはできません。また、SQL Server 2005 の再コンパイルには、拡張機能セットによってさらに多くの種類が用意されています。

ステートメントレベルの再コンパイルにより、パフォーマンスが向上します。これは、多くの場合、再コンパイルとそれに関連付けられた CPU 時間やロックへの悪影響を引き起こすステートメントの数が少ないからです。したがって、再コンパイルする必要がないバッチの他のステートメントでは、これらの影響を回避できます。

SQL Server Profiler の SP:Recompile トレース イベントにより、SQL Server 2005 のステートメントレベルの再コンパイルが報告されます。このトレース イベントで報告されるのは、SQL Server 2000 のバッチの再コンパイルだけです。さらに SQL Server 2005 では、このイベントの TextData 列に値が格納されます。したがって、SQL Server 2000 のように、SP:StmtStarting または SP:StmtCompleted をトレースし、再コンパイルの原因となった Transact-SQL テキストを取得する必要はなくなります。

また、SQL Server 2005 では、ステートメントレベルの再コンパイルを報告する SQL:StmtRecompile という新しいトレース イベントが追加されています。このトレース イベントを使用すると、再コンパイルを追跡してデバッグできます。SP:Recompile では、ストアド プロシージャとトリガのみに対して値が生成されますが、SQL:StmtRecompile では、sp_executesql、準備されたクエリ、および動的 SQL を使用して実行されたストアド プロシージャ、トリガ、アドホック バッチ、およびバッチに対して値が生成されます。

SP:Recompile と SQL:StmtRecompile の EventSubClass 列には、再コンパイルの理由を示す整数コードが格納されます。次の表に、各コード番号の意味を示します。

EventSubClass 列の値 説明

1

スキーマが変更されました。

2

統計が変更されました。

3

コンパイルが遅延されました。

4

SET オプションが変更されました。

5

一時テーブルが変更されました。

6

リモート行セットが変更されました。

7

FOR BROWSE 権限が変更されました。

8

クエリ通知環境が変更されました。

9

パーティション ビューが変更されました。

10

カーソル オプションが変更されました。

11

OPTION (RECOMPILE) が要求されました。
ms181055.note(ja-jp,SQL.90).gifメモ :
AUTO_UPDATE_STATISTICS データベース オプションが ON に設定されていると、対象にしているテーブルまたはインデックス付きビューの統計が更新された場合、または前回の実行から基数が大きく変更された場合、クエリが再コンパイルされます。この動作は、標準のユーザー定義テーブル、一時テーブル、および DML トリガによって作成された inserted テーブルと deleted テーブルに当てはまります。過度の再コンパイルによってクエリのパフォーマンスが低下する場合は、この設定を OFF に変更することを検討してください。AUTO_UPDATE_STATISTICS データベース オプションを OFF に設定すると、統計や基数の変化に基づく再コンパイルが行われません。ただし、DML INSTEAD OF トリガで作成した inserted テーブルおよび deleted テーブルは例外です。これらのテーブルは tempdb で作成されるため、それらにアクセスするクエリの再コンパイルは tempdb の AUTO_UPDATE_STATISTICS の設定によって異なります。SQL Server 2000 では、この設定を OFF にした場合も、DML トリガ inserted テーブルと DML トリガ deleted テーブルに対して基数の変更に基づいて再コンパイルが行われます。AUTO_UPDATE_STATISTICS の無効化に関する詳細については、「インデックス統計」を参照してください。

参照

関連項目

SQL Server: SQL Statistics オブジェクト

概念

バッファ管理

その他の技術情報

SQL Server 2005 のバッチのコンパイル、再コンパイル、およびプランのキャッシュに関する問題

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース 履歴

2006 年 7 月 17 日
新しい内容 :
  • 実行プランが無効になる条件に、WITH RECOMPILE オプションを使用してストアド プロシージャを実行する場合を追加しました。

2005 年 12 月 5 日
新しい内容 :
  • AUTO_UPDATE_STATISTICS データベース オプションを OFF に設定すると、DML INSTEAD OF トリガで作成した inserted テーブルおよび deleted テーブルにクエリの再コンパイルが異なる方法で適用されることについての注を明記しました。