sp_create_plan_guide (Transact-SQL)

@type = 'SQL' または @type = 'TEMPLATE' を指定するプラン ガイドをクエリに正しく適合させるためには、batch_text および @parameter_name data_type [,...n ] の値を、アプリケーションで送信される対応の値と完全に同じ形式で指定する必要があります。つまり、バッチ テキストを、SQL Server コンパイラが受信したときとまったく同じように指定する必要があります。実際のバッチおよびパラメーター テキストをキャプチャするには、SQL Server Profiler を使用する必要があります。詳細については、「SQL Server Profiler を使用したプラン ガイドの作成とテスト」を参照してください。

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

SQL Server が statement_text の値を batch_text および @parameter_name data_type [,...n ] と照合するとき、または @type = **'**OBJECT' を object_name 内の対応するクエリのテキストと照合するときは、次の文字列要素は考慮されません。

• 文字列内の空白文字 (タブ、スペース、復帰、改行)

• コメント (-- または /* */)

• 末尾のセミコロン

たとえば、SQL Server は、statement_text 文字列 N'SELECT * FROM T WHERE a = 10' を次の batch_text に一致させることができます。

N'SELECT *

FROM T

WHERE a=10'

ただし、この文字列は次の batch_text には一致しません。

N'SELECT * FROM T WHERE b = 10'

SQL Server は、最初のクエリ内にある復帰、改行、空白文字を無視します。2 つ目のクエリのシーケンス WHERE b = 10 は、WHERE a = 10 とは異なるものと解釈されます。照合処理では、大文字小文字が区別されないキーワードを除き、(データベースの照合順序で大文字小文字が区別されない場合でも) 大文字小文字およびアクセントが区別されます。キーワードの省略形は区別されません。たとえば、キーワード EXECUTE、EXEC、および execute は同じものと解釈されます。

プラン ガイドをクエリに適合させる方法の詳細については、「プラン ガイドを使用した配置済みアプリケーションのクエリの最適化」を参照してください。

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

次の例では、アプリケーションベースのストアド プロシージャのコンテキストで実行されるクエリに適合するプラン ガイドを作成し、OPTIMIZE FOR ヒントをクエリに適用します。

ストアド プロシージャは次のようになります。

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
    DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountry 
    (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h 
    INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID
    INNER JOIN Sales.SalesTerritory AS t 
        ON c.TerritoryID = t.TerritoryID
    WHERE t.CountryRegionCode = @Country_region;
END
GO

ストアド プロシージャのクエリに対して作成されるプラン ガイドは次のようになります。

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

次の例では、sp_executesql システム ストアド プロシージャを使用するアプリケーションで送信されるバッチ内のクエリに適合するプラン ガイドを作成します。

バッチは、次のようになります。

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

このクエリに対して並列実行プランが生成されないようにするには、次のプラン ガイドを作成します。

EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @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)';

次の例では、指定されたフォームにパラメーター化されるクエリに適合するプラン ガイドを作成し、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;

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

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

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

パラメーター化形式のクエリを取得して TEMPLATE ベースのプラン ガイドで使用する方法の詳細については、「パラメータ化クエリのプラン ガイドの設計」を参照してください。

プラン ガイドは、API サーバー カーソル ルーチンから送信されるクエリを適合できます。これらのルーチンには、sp_cursorprepare、sp_cursorprepexec、および sp_cursoropen があります。ADO、OLE DB、および ODBC API を使用するアプリケーションは、API サーバー カーソルを使用して SQL Server と頻繁に対話します。詳細については、「API サーバー カーソル」を参照してください。RPC:Starting プロファイラー トレース イベントを表示して、SQL Server Profiler トレース内の API サーバー カーソル ルーチンの呼び出しを参照できます。

たとえば、次のデータが、プラン ガイドに合わせて調整するクエリの RPC:Starting プロファイル トレース イベントに示されているものとします。

DECLARE @p1 int;
SET @p1=-1;
DECLARE @p2 int;
SET @p2=0;
DECLARE @p5 int;
SET @p5=4104;
DECLARE @p6 int;
SET @p6=8193;
DECLARE @p7 int;
SET @p7=0;
EXEC sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(255),@P2 varchar(255)',N'SELECT * FROM Sales.SalesOrderHeader h INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate >= @P1 AND <= @P2',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT,'20040101','20050101'
SELECT @p1, @p2, @p5, @p6, @p7;

このデータを見ると、sp_cursorprepexec の呼び出しの SELECT クエリに対するプランでマージ結合を使用していることがわかりますが、ハッシュ結合を使用するとします。sp_cursorprepexec を使用して送信されるクエリは、クエリ文字列およびパラメーター文字列の両方を含めて、パラメーター化されます。sp_cursorprepexec の呼び出しで、表示されているとおり完全に同じであるクエリ文字列とパラメーター文字列を使用して、次のプラン ガイドを作成し、プランの選択を変更できます。

EXEC sp_create_plan_guide 
    @name = N'APICursorGuide',
    @stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h 
              INNER JOIN Sales.SalesOrderDetail AS d 
                ON h.SalesOrderID = d.SalesOrderID 
              WHERE h.OrderDate >= @P1 AND <= @P2',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 varchar(255),@P2 varchar(255)',
    @hints = N'OPTION(HASH JOIN)';

今後アプリケーションでこのクエリを実行すると、ここで作成したプラン ガイドの影響を受け、クエリの処理にはハッシュ結合が使用されます。

カーソルで送信されるクエリに対するプラン ガイドでの USE PLAN クエリ ヒントの使用方法の詳細については、「カーソルを使用したクエリでの USE PLAN クエリ ヒントの使用」を参照してください。

次の例では、単純なアドホック 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