• [アーティクル]

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

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

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

  • クエリ プラン

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

  • 実行コンテキスト

    クエリを現在実行しているユーザーごとに、パラメーター値など、実行に固有のデータを保持するデータ構造体が用意されています。このデータ構造体を実行コンテキストといいます。実行コンテキストのデータ構造体は再利用されます。ユーザーがクエリを実行したときに使用されていない構造体が 1 つある場合、新しいユーザーのコンテキストでその構造体が再初期化されます。

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

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

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

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

SELECT * FROM Person;

SELECT * FROM Person.Person;

プロシージャ キャッシュからの実行プランの削除

実行プランは、格納しておくためのメモリがある間はプロシージャ キャッシュに残ります。メモリ負荷が存在する場合、データベース エンジンはコストベースの手法を使用して、どの実行プランをプロシージャ キャッシュから削除するかを判断します。コストベースの判断をするために、データベース エンジンは各実行プランの現在のコスト変数を以下のような要因に基づいて増減させます。

ユーザー プロセスは、キャッシュに実行プランを挿入するときに、現在のコストを元のクエリ コンパイル コストと同じ値に設定します。アドホック実行プランの場合、ユーザー プロセスは現在のコストをゼロに設定します。これ以降、ユーザー プロセスは、実行プランを参照するたびに、現在のコストを元のコンパイル コストにリセットします。アドホック実行プランの場合、ユーザー プロセスは現在のコストを増加させます。すべてのプランについて、現在のコストの最大値は元のコンパイル コストです。

メモリ負荷が存在する場合、データベース エンジンは実行プランをプロシージャ キャッシュから削除することによって対応します。どのプランを削除するかを決めるために、データベース エンジンは実行プランの状態を繰り返し検証して、現在のコストがゼロであるプランを削除します。現在のコストがゼロである実行プランは、メモリ負荷がある場合でも、自動的には削除されません。削除されるのは、データベース エンジンがプランを検証し、現在のコストがゼロになっている場合だけです。データベース エンジンが実行プランを検証するとき、クエリがそのプランを現在使用していない場合は、現在のコストが減少し、ゼロに近づきます。

データベース エンジンは、メモリ要件を満たすために必要な数の実行プランが削除されるまで、実行プランの検証を繰り返します。メモリ負荷が存在する場合、実行プランのコストは数回にわたって増減することになります。メモリ負荷が存在しなくなると、データベース エンジンは使用されていない実行プランの現在のコストを減らさなくなり、コストがゼロの実行プランもプロシージャ キャッシュに残されます。

データベース エンジンはリソース モニターとユーザー スレッドを使用して、メモリ負荷に応じてプロシージャ キャッシュからメモリを解放します。リソース モニターとユーザー スレッドは、使用されていない各実行プランの現在のコストを減らすために、プランを同時に実行して検証することができます。リソース モニターは、グローバルなメモリ負荷が存在する場合に、実行プランをプロシージャ キャッシュから削除します。リソース モニターはメモリを解放することによって、システム メモリ、プロセス メモリ、リソース プール メモリ、およびすべてのキャッシュの最大サイズのポリシーを強制的に適用します。

すべてのキャッシュの最大サイズは、バッファー プール サイズの関数で、最大サーバー メモリを超えることはできません。最大サーバー メモリの構成の詳細については、「sp_configure (Transact-SQL)」の最大サーバー メモリの設定を参照してください。

ユーザー スレッドは、単一キャッシュ メモリ負荷が存在する場合に、実行プランをプロシージャ キャッシュから削除します。単一キャッシュの最大サイズおよび最大エントリのポリシーを強制的に適用します。

次の例では、どの実行プランがプロシージャ キャッシュから削除されるかについて説明します。

  • 実行プランは頻繁に参照されるため、そのコストがゼロになることはありません。メモリ負荷が存在せず、現在のコストがゼロでない場合、実行プランはプロシージャ キャッシュに残り、削除されません。

  • アドホック実行プランは挿入され、メモリ負荷が生じるまでは再度参照されることはありません。アドホック実行プランは、現在のコストがゼロで初期化されます。そのため、データベース エンジンは実行プランを検証するときに現在のコストがゼロであると認識し、プロシージャ キャッシュから実行プランを削除します。メモリ負荷が存在しない場合、アドホック実行プランは、現在のコストがゼロでプロシージャ キャッシュに残ります。

キャッシュから 1 つまたはすべてのプランを手動で削除するには、DBCC FREEPROCCACHE (Transact-SQL) を使用します。

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

データベースに変更を加えた場合、データベースの新しい状態によっては、実行プランの効率が低下したり、実行プランが無効になったりします。実行プランが無効になるような変更は 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 以降の再コンパイルには、拡張機能セットによってさらに多くの種類が用意されています。

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

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

トレース イベント 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) が要求されました。
注意注

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 の無効化に関する詳細については、「クエリのパフォーマンスを向上させるための統計の使用」を参照してください。