次の方法で共有

  • [アーティクル]

プラン ガイドのデザインと実装

クエリのテキストを直接変更することが不可能な場合や望ましくない場合に、プラン ガイドを使用してクエリのパフォーマンスを最適化することができます。プラン ガイドは、クエリ ヒントまたは固定クエリ プランをクエリにアタッチすることにより、クエリの最適化を促します。プラン ガイドを作成すると、次のコンテキストで実行されるクエリを照合できます。

  • OBJECT プラン ガイドでは、Transact-SQL ストアド プロシージャ、ユーザー定義のスカラ関数、ユーザー定義の複数ステートメントのテーブル値関数、および DML トリガのコンテキストで実行されるクエリが照合されます。

  • SQL プラン ガイドでは、データベース オブジェクトの一部ではないスタンドアロン Transact-SQL ステートメントとスタンドアロン バッチのコンテキストで実行されるクエリが照合されます。また、SQL ベースのプラン ガイドを使用して、指定した形式にパラメータ化されたクエリを照合することもできます。

  • TEMPLATE プラン ガイドでは、指定した形式にパラメータ化されたスタンドアロン クエリが照合されます。これらのプラン ガイドは、クエリのクラスのデータベースの現在の PARAMETERIZATION データベース SET オプションを上書きするために使用されます。

詳細については、「プラン ガイドについて」を参照してください。

作成できるプラン ガイドの総数の上限は、使用可能なシステム リソースによって決まります。ただし、プラン ガイドは、ミッションクリティカルなクエリのパフォーマンスの向上と安定化を図る目的にのみ使用する必要があります。プラン ガイドの使用により配置済みのアプリケーションのクエリ負荷の多くが影響を受けることがないようにしてください。

アプリケーションを新しい SQL Server のリリースにアップグレードした場合は、プラン ガイドの定義を再評価し、テストすることをお勧めします。新しいリリースでは、パフォーマンス チューニングの要件とプラン ガイドの照合動作が異なる場合があります。無効なプラン ガイドが原因でクエリが失敗することはありませんが、そのプラン ガイドは使用されずにプランがコンパイルされます。SQL Server 2008 にデータベースをアップグレードした後は、既存のプラン ガイドを sys.fn_validate_plan_guide 関数を使用して検証することをお勧めします。また、SQL Server Profiler で、Plan Guide Unsuccessful イベントを使用して無効なプラン ガイドを監視することもできます。

注意注意

プラン ガイドを使用できるのは SQL Server Standard Edition、Developer Edition、Evaluation Edition、および Enterprise Edition だけですが、プラン ガイドはどのエディションでも表示できます。また、プラン ガイドを含むデータベースは、どのエディションに対してもアタッチできます。アップグレード済みのバージョンの SQL Server 2008 にデータベースを復元またはアタッチした場合、プラン ガイドはまったく影響を受けません。

プラン ガイドへのクエリ ヒントのアタッチ

クエリ ヒントは、有効なものであれば、任意の組み合わせでプラン ガイドに使用できます。プラン ガイドをクエリと照合する際、コンパイルや最適化が行われる前に、そのプラン ガイドのヒント句で指定されている OPTION 句がクエリに追加されます。プラン ガイドと照合するクエリで既に OPTION 句が使用されている場合、クエリ内のクエリ ヒントがプラン ガイドで指定されているクエリ ヒントに置換されます。ただし、既に OPTION 句が使用されているクエリと照合するプラン ガイドでは、sp_create_plan_guide ステートメントで照合するクエリのテキストを指定するときに、そのクエリの OPTION 句を含める必要があります。プラン ガイドで指定したヒントでクエリに既に存在するヒントを置換せず、追加する場合は、プラン ガイドでは、既存のヒントと追加するヒントの両方を指定する必要があります。

注記注意

クエリ ヒントの使用方法が正しくないプラン ガイドは、コンパイル、実行、またはパフォーマンスに関する問題の原因になることがあります。プラン ガイドは、上級開発者とデータベース管理者のみが使用するようにしてください。

プラン ガイドで使用される一般的なクエリ ヒント

通常、プラン ガイドからメリットを得られる可能性があるクエリは、パラメータ ベースのものです。このようなクエリはキャッシュされたクエリ プランを使用しますが、最悪のシナリオや最も典型的なシナリオにパラメータ値が対応していないために、クエリのパフォーマンスが低くなることがあります。この問題に対処するのに、OPTIMIZE FOR クエリ ヒントと RECOMPILE クエリ ヒントを使用できます。OPTIMIZE FOR は、クエリの最適化時にパラメータに特定の値を使用するように SQL Server に指示するクエリ ヒントです。RECOMPILE は、クエリ プランを実行後に破棄し、次に同じクエリが実行されるときに、クエリ オプティマイザにより新しいクエリ プランに強制的に再コンパイルされるようにサーバーに指示するクエリ ヒントです。例については、「プラン ガイドについて」を参照してください。

また、INDEX および FORCESEEK のテーブル ヒントをクエリ ヒントとして指定できます。クエリ ヒントとして指定すると、これらのヒントはインライン テーブルまたはビュー ヒントと同じように動作します。INDEX ヒントは、指定したインデックスのみを使用して、参照されているテーブルやビューのデータにアクセスするよう、クエリ オプティマイザに指示します。FORCESEEK ヒントは、インデックスのシーク操作のみを使用して参照先テーブルやビューのデータにアクセスするよう、オプティマイザに指示します。これらのヒントによってプラン ガイド機能が拡張され、プラン ガイドを使用するクエリをさらに最適化することができます。例については、「プラン ガイドでの INDEX および FORCESEEK のクエリ ヒントの使用」を参照してください。

プラン ガイドへのクエリ プランのアタッチ

特定のクエリに対してオプティマイザによって選択された実行プランよりもパフォーマンスの高い既存の実行プランがわかっている場合は、固定クエリ プランを適用するプラン ガイドを使用すると便利です。ただし、固定プランをクエリに適用すると、クエリ オプティマイザでは、そのクエリのプランを統計やインデックスの変更に合わせて変更できなくなります。固定クエリ プランを使用するプラン ガイドを検討する場合は、固定プランを利用する利点と、データ分布や使用可能なインデックスの変更に合わせてプランが自動的に変更されない点を比較検討してください。

特定のクエリ プランをプラン ガイドにアタッチするには、sp_create_plan_guide ステートメントの xml_showplan パラメータにそのプランの XML プラン表示を指定するか、sp_create_plan_guide_from_handle ステートメントでキャッシュされたプランのプラン ハンドルを指定します。どちらの方法を使用しても、対象のクエリに固定クエリ プランが適用されます。

プラン ガイドの照合要件

プラン ガイドの範囲は、そのガイドが作成されているデータベースです。したがって、クエリの実行時に使用されているデータベース内に存在するプラン ガイドだけをクエリと照合できます。たとえば、AdventureWorks が現在のデータベースの場合に次のクエリを実行するとします。

SELECT * FROM Person.Contact;

この場合、AdventureWorks データベース内のプラン ガイドだけがこのクエリと照合されます。

ただし、AdventureWorks が現在のデータベースの場合に、次のステートメントを実行すると結果が異なります。

USE DB1;

GO

SELECT * FROM Person.Contact;

この場合、DB1 のコンテキストでこのクエリが実行されているので、DB1 内のプラン ガイドがこのクエリと照合されます。

SQL ベースのプラン ガイドまたは TEMPLATE ベースのプラン ガイドでは、SQL Server により、@module_or_batch 引数と @params 引数の値が文字単位で比較されてクエリと照合されます。つまり、SQL Server で受け取られる実際のバッチ テキストと厳密に同じテキストを指定する必要があります。

@type = 'SQL' で、@module_or_batch が NULL に設定されている場合、@module_or_batch の値は @stmt の値に設定されます。つまり、statement_text の値は、SQL Server に送信するときとまったく同じ形式で、同じ文字で指定する必要があります。この適合を容易にするために内部変換は実行されません。

一般に、クエリがプラン ガイドと照合されているかどうかを確認するには、SQL Server Profiler を使用してプラン ガイドをテストする必要があります。SQL Server Management Studio でバッチを実行して SQL ベースのプラン ガイドまたは TEMPLATE ベースのプラン ガイドをテストすると、予想外の結果になることがあります。詳細については、「SQL Server Profiler を使用したプラン ガイドの作成とテスト」を参照してください。

注意注意

プラン ガイドの作成対象のステートメントを含むバッチには、USE database ステートメントを含めることはできません。

プラン キャッシュに対するプラン ガイドの効果

モジュールにプラン ガイドを作成すると、そのモジュールのクエリ プランがプラン キャッシュから削除されます。バッチに OBJECT 型または SQL 型のプラン ガイドを作成すると、同じハッシュ値を持つバッチのクエリ プランが削除されます。TEMPLATE 型のプラン ガイドを作成すると、単一ステートメントのバッチがデータベース内のプラン キャッシュからすべて削除されます。

プラン ガイド ステートメント

プラン ガイドを作成するには

プラン ガイドを無効化、有効化、または削除するには

現在のデータベース内のプラン ガイドの情報を取得するには

プラン ガイドを検証するには