[アーティクル]
10/25/2012

プランガイドについて

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

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

プランガイドを使用できるのは 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 を使用したプランガイドの作成とテスト」を参照してください。

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

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 イベントを使用して一定期間の間監視します。

次の方法で共有

プランガイドについて

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

OBJECT プランガイド

SQL プランガイド

TEMPLATE プランガイド

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

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

関連項目

参照

概念

その他の技術情報

その他のリソース

次の方法で共有

プラン ガイドについて

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

OBJECT プラン ガイド

SQL プラン ガイド

TEMPLATE プラン ガイド

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

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

関連項目

参照

概念

その他の技術情報

その他のリソース

プランガイドについて

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

OBJECT プランガイド

SQL プランガイド

TEMPLATE プランガイド

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

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