sp_create_plan_guide (Transact-SQL)

クエリ ヒントまたは実際のクエリ プランをデータベース内のクエリと関連付けるためのプラン ガイドを作成します。プラン ガイドの詳細については、「プラン ガイドについて」を参照してください。

トピック リンク アイコンTransact-SQL 構文表記規則

構文

sp_create_plan_guide [ @name = ] N'plan_guide_name'
    , [ @stmt = ] N'statement_text'
    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
    , [ @module_or_batch = ]
      { 
                    N'[ schema_name. ] object_name'
        | N'batch_text'
        | NULL
      }
    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL } 
    , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )' 
                 | N'XML_showplan'
                 | NULL }

引数

  • [ @name = ] N'plan_guide_name'
    プラン ガイドの名前を指定します。プラン ガイド名は現在のデータベースに対して有効です。plan_guide_name は識別子の規則に従う必要があり、番号記号 (#) で始めることはできません。plan_guide_name の最大長は 124 文字です。

  • [ @stmt = ] N'statement_text'
    プラン ガイドを作成する対象の Transact-SQL ステートメントです。SQL Server クエリ オプティマイザが statement_text に適合するクエリを認識すると、plan_guide_name が有効になります。プラン ガイドの作成を成功させるには、statement_text が、@type、@module_or_batch、および @params パラメータで指定されるコンテキストに存在する必要があります。

    statement_text は、クエリ オプティマイザが @module\_or\_batch および @params で識別されるバッチまたはモジュール内の対応するステートメントと照合できるように指定する必要があります。詳細については、「解説」を参照してください。statement_text のサイズは、サーバーで使用可能なメモリの最大値までに制限されます。

  • [@type = ]N'{ OBJECT | SQL | TEMPLATE }'
    statement_text が存在するエンティティの種類です。statement_text と plan_guide_name を照合するコンテキストを指定します。

    • OBJECT
      statement_text が現在のデータベースの Transact-SQL ストアド プロシージャ、スカラ関数、複数ステートメントのテーブル値関数、または Transact-SQL DML トリガのコンテキストに存在することを示します。

    • SQL
      statement_text が、任意のメカニズムを通じて SQL Server に送信できるスタンドアロンのステートメントまたはバッチに存在することを示します。共通言語ランタイム (CLR) オブジェクトまたは拡張ストアド プロシージャ、あるいは EXEC N'sql_string' を使用して送信される Transact-SQL ステートメントは、サーバー上のバッチとして処理されるため、@type = 'SQL' として指定する必要があります。SQL を指定した場合は、@hints パラメータで PARAMETERIZATION { FORCED | SIMPLE } クエリ ヒントを指定できません。

    • TEMPLATE
      プラン ガイドが、statement_text で示されたフォームにパラメータ化されるクエリに対して適用されることを示します。TEMPLATE を指定した場合は、@hints パラメータで PARAMETERIZATION { FORCED | SIMPLE } クエリ ヒントのみ指定できます。TEMPLATE プラン ガイドの詳細については、「プラン ガイドを使用したクエリのパラメータ化動作の指定」を参照してください。

  • [@module_or_batch =]{ N'[ schema_name. ] object_name' | N'batch_text' | NULL }
    statement_text が存在するオブジェクト、または statement_text が存在するバッチ テキストの名前を指定します。バッチ テキストには、USE database ステートメントを含めることはできません。

    プラン ガイドをアプリケーションから送信されたバッチに適合させるためには、batch_text を、SQL Server に送信するときと同じ形式で、同じ文字で指定する必要があります。この適合を容易にするために内部変換は実行されません。詳細については、「解説」を参照してください。

    [schema_name.]object_name は、statement_text を含む Transact-SQL ストアド プロシージャ、スカラ関数、複数ステートメントのテーブル値関数、または Transact-SQL DML トリガの名前を指定します。schema_name を指定しないと、schema_name には現在のユーザーのスキーマが使用されます。NULL が指定され、@type = 'SQL' である場合、@module_or_batch の値は、@stmt の値に設定されます。@type = 'TEMPLATE**'** の場合は、@module_or_batch を NULL にする必要があります。

  • [ @params = ]{ N'@parameter_name data_type [ ,...n ]' | NULL }
    statement_text に埋め込まれるすべてのパラメータの定義を指定します。@params は、次の条件のいずれかを満たす場合にのみ適用されます。

    • @type = 'SQL' または 'TEMPLATE' の場合。'TEMPLATE' の場合、@params を NULL にすることはできません。

    • statement_text が sp_executesql を使用して送信され、@params パラメータの値が指定されている場合、または SQL Server が内部でステートメントをパラメータ化した後に送信する場合。データベース API (ODBC、OLE DB、ADO.NET など) からのパラメータ化クエリの送信は、sp_executesql または API サーバー カーソル ルーチンの呼び出しとして SQL Server に示されるため、SQL または TEMPLATE プラン ガイドでも適合させることができます。パラメータ化およびプラン ガイドの詳細については、「SQL Server がプラン ガイドをクエリに照合するプロセス」を参照してください。

    @parameter\_name data_type は、SQL Server に sp_executesql を使用して送信するか、パラメータ化してから内部的に送信するときとまったく同じ形式で指定する必要があります。詳細については、「解説」を参照してください。バッチにパラメータが含まれていない場合は、NULL を指定する必要があります。@params のサイズは、サーバーが使用できるメモリ量によってのみ制限されます。

  • [@hints = ]{ N'OPTION ( query_hint [ ,...n ] )' | N'XML_showplan' | NULL }

    • N'OPTION ( query_hint [ ,...n ] )
      @stmt に適合するクエリにアタッチする OPTION 句を指定します。@hints は、SELECT ステートメントの OPTION 句と構文的に同じにする必要があります。また、有効な一連のクエリ ヒントを含めることができます。

    • N'XML_showplan'
      ヒントとして適用する XML 形式のクエリ プランを指定します。

      XML プラン表示を変数に割り当てることをお勧めします。XML プラン表示を変数に割り当てない場合は、XML プラン表示内の単一引用符をエスケープする必要があります。これを行うには、単一引用符の前にもう 1 つ単一引用符を追加します。例 E を参照してください。

    • NULL
      クエリの OPTION 句で指定した既存のヒントがクエリに適用されないことを示します。詳細については、「OPTION 句 (Transact-SQL)」を参照してください。

説明

sp_create_plan_guide の引数は、表示される順序で指定する必要があります。sp_create_plan_guide のパラメータに値を指定する場合、パラメータ名はすべて明示的に指定するか、すべて指定しないかのいずれかにする必要があります。たとえば、@name = を指定する場合は、@stmt =、@type = なども指定する必要があります。同様に、@name = を省略してパラメータ値だけを指定する場合は、その他のパラメータ名も省略し、値だけを指定する必要があります。引数の名前は、構文を理解しやすくするための説明目的のものです。SQL Server では、指定したパラメータ名と、その名前が使用されている位置にあるパラメータの名前が一致しているかどうかは確認されません。

同一のクエリとバッチまたはモジュールに対し、複数の OBJECT または SQL プラン ガイドを作成できます。ただし、有効にできるプラン ガイドは常に 1 つだけです。

@module\_or\_batch 値で参照するストアド プロシージャ、関数、または DML トリガが、WITH ENCRYPTION 句を指定するものであるか一時的なものである場合、この値に対して OBJECT 型のプラン ガイドは作成できません。

プラン ガイドで参照される関数、ストアド プロシージャ、または DML トリガを削除または変更しようとすると、有効かどうかにかかわらず、エラーが発生します。プラン ガイドで参照され、トリガが定義されているテーブルを削除しようとする場合もエラーが発生します。

注意注意

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

プラン ガイドの照合要件

@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 型のプラン ガイドを作成すると、単一ステートメントのバッチがデータベース内のプラン キャッシュからすべて削除されます。

権限

OBJECT 型のプラン ガイドを作成するには、参照先オブジェクトに対する ALTER 権限が必要です。SQL または TEMPLATE タイプのプラン ガイドを作成するには、現在のデータベースに対する ALTER 権限が必要です。

A. ストアド プロシージャのクエリに対する OBJECT タイプのプラン ガイドを作成する

次の例では、アプリケーションベースのストアド プロシージャのコンテキストで実行されるクエリに適合するプラン ガイドを作成し、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''))';

B. スタンドアロン クエリに対する SQL タイプのプラン ガイドを作成する

次の例では、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)';

C. パラメータ化形式のクエリに対する TEMPLATE タイプのプラン ガイドを作成する

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

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

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.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 AdventureWorks.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks.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 AdventureWorks.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks.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 ベースのプラン ガイドで使用する方法の詳細については、「パラメータ化クエリのプラン ガイドの設計」を参照してください。

D. API カーソル要求を使用して送信されるクエリでプラン ガイドを作成する

プラン ガイドは、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 AdventureWorks.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate BETWEEN @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 BETWEEN @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 クエリ ヒントの使用」を参照してください。

E. キャッシュされたプランから XML プラン表示を取得してプラン ガイドを作成する

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