プラン ガイドについて

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

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

プラン ガイドとのクエリの照合

プラン ガイドを作成すると、次のコンテキストで実行されるクエリを照合できます。

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

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

• TEMPLATE プラン ガイドでは、指定した形式にパラメーター化されたスタンドアロン クエリが照合されます。これらのプラン ガイドは、クエリのクラスのデータベースの現在の PARAMETERIZATION データベース SET オプションを上書きするために使用されます。詳細については、「簡易パラメーター化」および「強制パラメータ化」を参照してください。

詳細については、「SQL Server がプラン ガイドをクエリに照合するプロセス」を参照してください。

OBJECT プラン ガイド

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

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;

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

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 
@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 ステートメントとスタンドアロン バッチに適用されます。これらのステートメントは、よく sp_executesql システム ストアド プロシージャを使用してアプリケーションから送信されます。たとえば、次のスタンドアロン バッチについて考えてみましょう。

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

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 
@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)';

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

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

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

TEMPLATE プラン ガイド

TEMPLATE プラン ガイドは、特定のクエリ形式のパラメーター化動作を上書きするときに使用されます。TEMPLATE プラン ガイドは、次のいずれかの状況で作成できます。

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

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

詳細については、「プラン ガイドを使用したクエリのパラメータ化動作の指定」を参照してください。

次の例では、指定の形式にパラメーター化されるクエリに適合するようにプラン ガイドを作成し、SQL Server に対してクエリのパラメーター化を強制的に実行させます。次の 2 つのクエリは構文的には同じですが、定数リテラル値のみが異なります。

SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;

SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;

パラメーター化形式のクエリに対するプラン ガイドは次のようになります。

EXEC sp_create_plan_guide 
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
                  ON h.SalesOrderID = d.SalesOrderID
              WHERE h.SalesOrderID = @0',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@0 int',
    @hints = N'OPTION(PARAMETERIZATION FORCED)';

EXEC sp_create_plan_guide 
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
                  ON h.SalesOrderID = d.SalesOrderID
              WHERE h.SalesOrderID = @0',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@0 int',
    @hints = N'OPTION(PARAMETERIZATION FORCED)';

この例では、@stmt パラメーターの値は、パラメーター化形式のクエリになっています。この値を取得して sp_create_plan_guide で使用できるようにするには、sp_get_query_template システム ストアド プロシージャを使用するのが唯一信頼できる方法です。次のスクリプトを使用すると、パラメーター化クエリを取得してそのクエリに対してプラン ガイドを作成することができます。

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template 
    N'SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
          ON h.SalesOrderID = d.SalesOrderID
      WHERE h.SalesOrderID = 45639;',
    @stmt OUTPUT, 
    @params OUTPUT
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
    N'TEMPLATE', 
    NULL, 
    @params, 
    N'OPTION(PARAMETERIZATION FORCED)';

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template 
    N'SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
          ON h.SalesOrderID = d.SalesOrderID
      WHERE h.SalesOrderID = 45639;',
    @stmt OUTPUT, 
    @params OUTPUT
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
    N'TEMPLATE', 
    NULL, 
    @params, 
    N'OPTION(PARAMETERIZATION FORCED)';

重要
sp_get_query_template に渡される @stmt パラメーターの定数リテラルの値は、リテラルを置き換えるパラメーターで選択されるデータ型に影響する場合があります。この値は、プラン ガイドの適合にも影響します。場合によっては、異なるパラメーター値範囲に対応する複数のプラン ガイドを作成する必要があります。

TEMPLATE プラン ガイドを SQL プラン ガイドと併用することもできます。たとえば、TEMPLATE プラン ガイドを作成することで、特定のクラスのクエリについて確実にパラメーター化を行うことができます。これにより、そのパラメーター化された形式のクエリに対して SQL プラン ガイドを作成できます。

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

OBJECT 型または SQL 型のプラン ガイドには固定クエリ プランを適用できます。特定のクエリに対してオプティマイザーによって選択された実行プランよりもパフォーマンスの高い既存の実行プランがわかっている場合は、固定クエリ プランを適用するプラン ガイドを使用すると便利です。

次の例では、単純なアドホック SQL ステートメントのプラン ガイドを作成します。このステートメントの目的のクエリ プランは、クエリの XML プラン表示を @hints パラメーターで直接指定することにより、プラン ガイドで提供されます。最初に SQL ステートメントを実行して、プラン キャッシュ内にプランを生成します。この例では、生成されたプランが目的のプランであり、追加のクエリ チューニングが不要であると想定しています。クエリの XML プラン表示は、sys.dm_exec_query_stats、sys.dm_exec_sql_text、および sys.dm_exec_text_query_plan の各動的管理ビューをクエリすることにより取得され、@xml_showplan 変数に割り当てられます。次に @xml_showplan 変数が、@hints パラメーターで sp_create_plan_guide ステートメントに渡されます。または、sp_create_plan_guide_from_handle ストアド プロシージャを使用して、プラン キャッシュ内のクエリ プランからプラン ガイドを作成することもできます。

USE AdventureWorks2008R2;
GO
SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;
GO
DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%');

EXEC sp_create_plan_guide 
    @name = N'Guide1_from_XML_showplan', 
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = @xml_showplan;
GO

USE AdventureWorks2008R2;
GO
SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;
GO
DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%');

EXEC sp_create_plan_guide 
    @name = N'Guide1_from_XML_showplan', 
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = @xml_showplan;
GO

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

アプリケーションを新しい SQL Server のリリースにアップグレードした場合は、プラン ガイドの定義を再評価し、テストすることをお勧めします。新しいリリースでは、パフォーマンス チューニングの要件とプラン ガイドの照合動作が異なる場合があります。無効なプラン ガイドが原因でクエリが失敗することはありませんが、そのプラン ガイドは使用されずにプランがコンパイルされるので、最適な選択ではない場合があります。SQL Server 2008 にデータベースをアップグレードした後は、次の作業を実行することをお勧めします。

• 既存のプラン ガイドを sys.fn_validate_plan_guide 関数を使用して検証します。

• SQL Server Profiler で、プラン ガイドによって不適切に生成されたプランがないか、Plan Guide Unsuccessful イベントを使用して一定期間の間監視します。

関連項目

参照

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)

クエリ ヒント (Transact-SQL)

概念

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

強制パラメータ化

簡易パラメーター化

その他の技術情報

プラン ガイドを使用した配置済みアプリケーションのクエリの最適化