次の方法で共有

プラン ガイド

  • [アーティクル]

SQL Server 2012 の実際のクエリのテキストを直接変更することが不可能な場合や望ましくない場合に、プラン ガイドを使用してクエリのパフォーマンスを最適化することができます。 プラン ガイドは、クエリ ヒントまたは固定クエリ プランをクエリにアタッチすることにより、クエリの最適化を促します。 プラン ガイドは、サード パーティ ベンダーが提供するデータベース アプリケーションのクエリの小さなサブセットで、期待どおりのパフォーマンスが得られない場合に役に立ちます。 プラン ガイドでは、最適化する Transact-SQL ステートメントのほか、使用するクエリ ヒントを含む OPTION 句またはクエリの最適化に使用する特定のクエリ プランのいずれかを指定します。 クエリが実行されると、SQL Server により Transact-SQL ステートメントがプラン ガイドと照合され、実行時にクエリに OPTION 句がアタッチされるか、指定されたクエリ プランが使用されます。

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

注意注

プラン ガイドは、Microsoft SQL Server のすべてのエディッションで使用できるわけではありません。 SQL Server の各エディションでサポートされる機能の一覧については、「SQL Server 2012 の各エディションがサポートする機能」を参照してください。 プラン ガイドはどのエディションでも表示できます。 また、プラン ガイドを含むデータベースは、どのエディションに対してもアタッチできます。 アップグレード済みのバージョンの SQL Server にデータベースを復元またはアタッチした場合、プラン ガイドはまったく影響を受けません。

プラン ガイドの種類

次の種類のプラン ガイドを作成できます。

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

    @Country\_region パラメーターを受け取る次のストアド プロシージャが、 AdventureWorks2012 データベースに対して配置されたデータベース アプリケーションに存在するとします。

    CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, 
        Sales.SalesTerritory AS t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country_region
END;

    このストアド プロシージャは @Country\_region = N'AU' (オーストラリア) 用にコンパイルおよび最適化されているものとします。 ただし、オーストラリアでは比較的少数の販売注文しか発生していないため、多くの販売注文が発生している国のパラメーター値を使用してクエリを実行するとパフォーマンスが低下します。 販売注文数が最も多いのは米国なので、@Country_region = N'US' 用に生成されたクエリ プランのパフォーマンスは、@Country_region パラメーターにどの値を使用しても低下しません。

    ストアド プロシージャを変更して OPTIMIZE FOR クエリ ヒントをクエリに追加することで、この問題に対処できます。 ただし、ストアド プロシージャは配置済みアプリケーション内にあるので、アプリケーション コードを直接変更することはできません。 代わりに、 AdventureWorks2012 データベースに次のプラン ガイドを作成できます。

    sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,
        Sales.Customer AS c,
        Sales.SalesTerritory AS t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country_region',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';

    sp_create_plan_guide ステートメントの指定したクエリが実行されると、そのクエリは最適化される前に変更され、OPTIMIZE FOR (@Country = N''US'') 句が含められます。

  • SQL プラン ガイド
    SQL プラン ガイドでは、データベース オブジェクトの一部ではないスタンドアロン Transact-SQL ステートメントとスタンドアロン バッチのコンテキストで実行されるクエリが照合されます。 また、SQL ベースのプラン ガイドを使用して、指定した形式にパラメーター化されたクエリを照合することもできます。 SQL プラン ガイドは、スタンドアロン Transact-SQL ステートメントとスタンドアロン バッチに適用されます。 これらのステートメントは、よく sp_executesql システム ストアド プロシージャを使用してアプリケーションから送信されます。 たとえば、次のスタンドアロン バッチについて考えてみましょう。

    SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;

    このクエリに並列実行プランが生成されないようにするには、次のプラン ガイドを作成し、@hints パラメーターで MAXDOP クエリ ヒントを 1 に設定します。

    sp_create_plan_guide 
@name = N'Guide2', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC',
@type = N'SQL',
@module_or_batch = NULL, 
@params = NULL, 
@hints = N'OPTION (MAXDOP 1)';
    重要な注意事項重要

    sp_create_plan guide ステートメントの @module_or_batch 引数と @params 引数に指定する値は、実際のクエリで送信される、対応するテキストと一致している必要があります。 詳細については、「sp_create_plan_guide (Transact-SQL)」および「SQL Server Profiler を使用したプラン ガイドの作成とテスト」を参照してください。

    PARAMETERIZATION データベース オプションを FORCED に設定するか、またはクエリのクラスをパラメーター化するように指定して TEMPLATE プラン ガイドを作成すると、同じ形式にパラメーター化されるクエリに SQL プラン ガイドを作成することもできます。

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

    TEMPLATE プラン ガイドは、次のいずれかの状況で作成できます。

    • PARAMETERIZATION データベース オプションを FORCED に設定したが、簡易パラメーター化のルールに従ってコンパイルするクエリがある場合。

    • PARAMETERIZATION データベース オプションを SIMPLE (既定値) に設定したが、特定のクラスのクエリについて強制パラメーター化が必要である場合。

プラン ガイドの照合要件

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

SELECT FirstName, LastName FROM Person.Person;

この場合、 AdventureWorks2012 データベース内のプラン ガイドだけがこのクエリと照合されます。 ただし、 AdventureWorks2012 が現在のデータベースの場合に、次のステートメントを実行すると結果が異なります。

USE DB1;

SELECT FirstName, LastName FROM Person.Person;

この場合、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 または OBJECT) プラン ガイドと TEMPLATE プラン ガイドの両方をステートメントに適用可能な場合、通常のプラン ガイドのみが使用されます。

注意注

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

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

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

関連タスク

タスク

トピック

プラン ガイドを作成する方法について説明します。

新しいプラン ガイドの作成

パラメーター化クエリ用のプラン ガイドを作成する方法について説明します。

パラメーター化クエリのプラン ガイドの作成

プラン ガイドを使用してクエリのパラメーター化動作を制御する方法について説明します。

プラン ガイドを使用したクエリのパラメーター化動作の指定

プラン ガイドに固定クエリ プランを含める方法について説明します。

プラン ガイドへの固定クエリ プランの適用

プラン ガイドにクエリ ヒントを指定する方法について説明します。

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

プラン ガイドのプロパティを表示する方法について説明します。

プラン ガイド プロパティの表示

プラン ガイドを作成およびテストするために SQL Server Profiler を使用する方法について説明します。

SQL Server Profiler を使用したプラン ガイドの作成とテスト

プラン ガイドを検証する方法について説明します。

アップグレード後のプラン ガイドの検証

関連項目

参照

sp_create_plan_guide (Transact-SQL)

sp_create_plan_guide_from_handle (Transact-SQL)

sp_control_plan_guide (Transact-SQL)

sys.plan_guides (Transact-SQL)

sys.fn_validate_plan_guide (Transact-SQL)