sp_create_plan_guide (Transact-SQL)

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

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

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が有効になります。 プラン ガイドの作成を成功させるには、@type、 @module_or_batch、@params の各パラメーターで指定されたコンテキストでstatement_textが表示される必要があります。

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

[@type = ]N'{ OBJECT |SQL |TEMPLATE }'
statement_textが表示されるエンティティの種類です。 これは、 plan_guide_nameに一致するstatement_text のコンテキストを指定 します

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

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

テンプレート
プラン ガイドが、 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データベース ステートメントを含めることはできません。

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

[schema_name.]object_name は、Transact-SQL ストアド プロシージャ、スカラー関数、複数状態テーブル値関数、または statement_textを含む 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 を含む) からのパラメーター化されたクエリの送信は、API サーバー カーソル ルーチンのsp_executesql呼び出しとしてSQL Serverしているように見えます。したがって、SQL または TEMPLATE プラン ガイドで照合することもできます。

@parameter_name data_typeは、 を使用sp_executesqlするか、パラメーター化後に内部的に送信することで、SQL Serverに送信されるのとまったく同じ形式で指定する必要があります。 詳細については、「解説」を参照してください。 バッチにパラメーターが含まれていない場合は、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 プラン表示を変数に割り当てることをお勧めします。それ以外の場合は、Showplan 内の単一引用符の前に別の単一引用符を付ける必要があります。 例 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 トリガーを削除または変更しようとすると、エラーが発生します。 プラン ガイドで参照され、トリガーが定義されているテーブルを削除しようとする場合もエラーが発生します。

注意

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

プラン ガイドの一致要件

クエリと正常に一致するように @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 とは異なるものと解釈されます。 キーワードの場合を除き、一致は大文字と小文字が区別され、アクセントは区別されます (データベースの照合順序で大文字と小文字が区別される場合でも)。ただし、大文字と小文字は区別されません。 一致は空白に依存します。 キーワードの省略形は区別されません。 たとえば、キーワード EXECUTEEXEC、および 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 AdventureWorks2022.Sales.SalesOrderHeader AS h  
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d   
    ON h.SalesOrderID = d.SalesOrderID  
WHERE h.SalesOrderID = 45639;  
  
SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h  
INNER JOIN AdventureWorks2022.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 AdventureWorks2022.Sales.SalesOrderHeader AS h  
              INNER JOIN AdventureWorks2022.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 AdventureWorks2022.Sales.SalesOrderHeader AS h  
      INNER JOIN AdventureWorks2022.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 パラメーターの定数リテラルの値は、リテラルを置き換えるパラメーターで選択されるデータ型に影響する場合があります。 この値は、プラン ガイドの適合にも影響します。 場合によっては、異なるパラメーター値範囲に対応する複数のプラン ガイドを作成する必要があります。

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

プラン ガイドは、API サーバー カーソル ルーチンから送信されたクエリと一致させることができます。 これらのルーチンには、sp_cursorprepare、sp_cursorprepexec、および sp_cursoropen があります。 ADO、OLE DB、ODBC API を使用するアプリケーションは、API サーバー カーソルを使用してSQL Serverと頻繁にやり取りします。 RPC:Starting profiler トレース イベントを表示することで、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 AS h INNER JOIN 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;  

このデータを見ると、SELECT の呼び出しの sp_cursorprepexec クエリに対するプランでマージ結合を使用していることがわかりますが、ハッシュ結合を使用するとします。 を使用 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)';  

アプリケーションによるこのクエリの後続の実行は、このプラン ガイドの影響を受け、ハッシュ結合を使用してクエリを処理します。

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

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

USE AdventureWorks2022;  
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  

参照

プラン ガイド
sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL)
データベース エンジンのストアド プロシージャ (Transact-SQL)
システム ストアド プロシージャ (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL)
sp_get_query_template (Transact-SQL)