sp_create_plan_guide (Transact-SQL)sp_create_plan_guide (Transact-SQL)

適用対象: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

クエリ ヒントまたは実際のクエリ プランをデータベース内のクエリと関連付けるためのプラン ガイドを作成します。Creates a plan guide for associating query hints or actual query plans with queries in a database. プラン ガイドの詳細については、「 Plan Guides」を参照してください。For more information about plan guides, see Plan Guides.

トピック リンク アイコン Transact-SQL 構文表記規則Topic link icon Transact-SQL Syntax Conventions

構文Syntax

  
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 }  

引数Arguments

[ @name =] N 'plan_guide_name'[ @name = ] N'plan_guide_name'
プラン ガイドの名前を指定します。Is the name of the plan guide. プラン ガイド名は現在のデータベースに対して有効です。Plan guide names are scoped to the current database. plan_guide_nameは、識別子の規則に従っている必要があり、番号記号 (#) で始めることはできません。plan_guide_name must comply with the rules for identifiers and cannot start with the number sign (#). Plan_guide_nameの最大長は124文字です。The maximum length of plan_guide_name is 124 characters.

[ @stmt = ] N'statement_text'[ @stmt = ] N'statement_text'
プラン ガイドを作成する対象の Transact-SQLTransact-SQL ステートメントです。Is a Transact-SQLTransact-SQL statement against which to create a plan guide. クエリオプティマイザー SQL ServerSQL Serverstatement_textと一致するクエリを認識すると、 plan_guide_nameが有効になります。When the SQL ServerSQL Server query optimizer recognizes a query that matches statement_text, plan_guide_name takes effect. プランガイドの作成を成功させるには 、 @type、 @module_or_batch、および@params パラメーターで指定されたコンテキストに statement_text を指定する必要があります。For the creation of a plan guide to succeed, statement_text must appear in the context specified by the @type, @module_or_batch, and @params parameters.

statement_textは、クエリオプティマイザーが、module_or_batch および@ @params で識別されるバッチまたはモジュール内で指定された対応するステートメントと照合できるように指定する必要があります。statement_text must be provided in a way that allows for the query optimizer to match it with the corresponding statement supplied within the batch or module identified by @module_or_batch and @params. 詳細については、「解説」セクションを参照してください。For more information, see the "Remarks" section. Statement_textのサイズは、サーバーの使用可能なメモリによってのみ制限されます。The size of statement_text is limited only by available memory of the server.

[@type =] N ' {OBJECT |SQL |テンプレート} '[@type = ]N'{ OBJECT | SQL | TEMPLATE }'
Statement_textが表示されるエンティティの種類を指定します。Is the type of entity in which statement_text appears. これにより、 statement_textplan_guide_nameに照合するためのコンテキストが指定されます。This specifies the context for matching statement_text to plan_guide_name.

OBJECTOBJECT
現在のデータベースのTransact-SQLTransact-SQLストアドプロシージャ、スカラー関数、複数ステートメントのテーブル値関数、またはTransact-SQLTransact-SQL DML トリガーのコンテキストで statement_text が表示されることを示します。Indicates statement_text appears in the context of a Transact-SQLTransact-SQL stored procedure, scalar function, multistatement table-valued function, or Transact-SQLTransact-SQL DML trigger in the current database.

SQLSQL
任意のメカニズムを通じてにSQL ServerSQL Server送信できるスタンドアロンのステートメントまたはバッチのコンテキストで statement_text が表示されることを示します。Indicates statement_text appears in the context of a stand-alone statement or batch that can be submitted to SQL ServerSQL Server through any mechanism. Transact-SQLTransact-SQL共通言語ランタイム (CLR) オブジェクトまたは拡張ストアドプロシージャ、または EXEC N 'sql_string' を使用して送信されたステートメントは、サーバー上でバッチとして処理@さ = れるため、型 ' sql ' として識別される必要があります。statements submitted by common language runtime (CLR) objects or extended stored procedures, or by using EXEC N'sql_string', are processed as batches on the server and, therefore, should be identified as @type = 'SQL'. SQL が指定されている場合、クエリヒントのパラメーター化 {FORCED |SIMPLE} を@ヒントパラメーターに指定することはできません。If SQL is specified, the query hint PARAMETERIZATION { FORCED | SIMPLE } cannot be specified in the @hints parameter.

TEMPLATETEMPLATE
Statement_textに示されている形式にパラメーター化されたクエリに、プランガイドが適用されることを示します。Indicates the plan guide applies to any query that parameterizes to the form indicated in statement_text. TEMPLATE が指定されている場合は、パラメーター化 {FORCED |SIMPLE} クエリヒントは、 @hint パラメーターで指定できます。If TEMPLATE is specified, only the PARAMETERIZATION { FORCED | SIMPLE } query hint can be specified in the @hints parameter. テンプレートプランガイドの詳細については、「プランガイドを使用してクエリのパラメーター化の動作を指定する」を参照してください。For more information about TEMPLATE plan guides, see Specify Query Parameterization Behavior by Using Plan Guides.

[@module_or_batch =] {N ' [ schema_name[@module_or_batch =]{ N'[ schema_name. ] object_name' | N'batch_text' | NULL }] object_name' | N'batch_text' | NULL }
Statement_textが表示されるオブジェクトの名前、またはstatement_textが表示されるバッチテキストのいずれかを指定します。Specifies either the name of the object in which statement_text appears, or the batch text in which statement_text appears. バッチテキストに USEdatabaseステートメントを含めることはできません。The batch text cannot include a USEdatabase statement.

プランガイドをアプリケーションから送信されたバッチに一致させるには、 batch_text をにSQL ServerSQL Server送信するときと同じ形式の文字文字で指定する必要があります。For a plan guide to match a batch submitted from an application, batch_text must be provided in the same format, character-for-character, as it is submitted to SQL ServerSQL Server. この適合を容易にするために内部変換は実行されません。No internal conversion is performed to facilitate this match. 詳細については、「解説」を参照してください。For more information, see the Remarks section.

[schema_name]object_name Transact-SQLTransact-SQLストアドプロシージャ、スカラー関数、複数ステートメントのテーブル値関数、またはTransact-SQLTransact-SQL statement_textを含む DML トリガーの名前を指定します。[schema_name.]object_name specifies the name of a Transact-SQLTransact-SQL stored procedure, scalar function, multistatement table-valued function, or Transact-SQLTransact-SQL DML trigger that contains statement_text. 場合schema_nameが指定されていない、 schema_nameは、現在のユーザーのスキーマを使用します。If schema_name is not specified, schema_name uses the schema of the current user. NULL が指定され@、type = ' SQL ' の場合、 @module_or_batch の値は stmt の@値に設定されます。Type @= ' TEMPLATE ' の場合@、module_or_batch は NULL である必要があります。If NULL is specified and @type = 'SQL', the value of @module_or_batch is set to the value of @stmt. If @type = 'TEMPLATE**'**, @module_or_batch must be NULL.

[ @params = ]{ N' @parameter_name data_type [ , ...n ]' | NULL }[ @params = ]{ N'@parameter_name data_type [ ,...n ]' | NULL }
Statement_textに埋め込まれているすべてのパラメーターの定義を指定します。Specifies the definitions of all parameters that are embedded in statement_text. @params は、次のいずれかに該当する場合にのみ適用されます。@params applies only when either of the following is true:

  • @「= ' SQL ' または ' TEMPLATE '」と入力します。@type = 'SQL' or 'TEMPLATE'. ' TEMPLATE ' の場合@、params を NULL にすることはできません。If 'TEMPLATE', @params must not be NULL.

  • statement_textは sp_executesql を使用して送信され、 @params パラメーターの値が指定SQL ServerSQL Serverされているか、または内部でステートメントをパラメーター化した後に送信します。statement_text is submitted by using sp_executesql and a value for the @params parameter is specified, or SQL ServerSQL Server internally submits a statement after parameterizing it. データベース API (ODBC、OLE DB、ADO.NET など) からのパラメーター化クエリの送信は、sp_executesql または API サーバー カーソル ルーチンの呼び出しとして SQL ServerSQL Server に示されるため、SQL または TEMPLATE プラン ガイドでも適合させることができます。Submission of parameterized queries from database APIs (including ODBC, OLE DB, and ADO.NET) appear to SQL ServerSQL Server as calls to sp_executesql or to API server cursor routines; therefore, they can also be matched by SQL or TEMPLATE plan guides.

parameter_name data_type は、sp_executesql を使用して送信するか、パラメーター化SQL ServerSQL Serverした後に内部で送信されるのとまったく同じ形式で指定する必要があります。 @@parameter_name data_type must be supplied in the exact same format as it is submitted to SQL ServerSQL Server either by using sp_executesql or submitted internally after parameterization. 詳細については、「解説」を参照してください。For more information, see the Remarks section. バッチにパラメーターが含まれていない場合は、NULL を指定する必要があります。If the batch does not contain parameters, NULL must be specified. Params の@サイズは、使用可能なサーバーメモリによってのみ制限されます。The size of @params is limited only by available server memory.

[@ヒント =] {N'OPTION (query_hint [, ...n ]) ' |N 'XML_showplan' |空白[@hints = ]{ N'OPTION (query_hint [ ,...n ] )' | N'XML_showplan' | NULL }
N'OPTION (query_hint [ , ...n ] )N'OPTION (query_hint [ ,...n ] )
Stmt に一致@するクエリにアタッチする OPTION 句を指定し@ます。ヒントは、SELECT ステートメントの option 句と構文的に同じである必要があり、任意の有効なクエリヒントのシーケンスを含めることができます。Specifies an OPTION clause to attach to a query that matches @stmt. @hints must be syntactically the same as an OPTION clause in a SELECT statement, and can contain any valid sequence of query hints.

N 'XML_showplan'N'XML_showplan'
ヒントとして適用する XML 形式のクエリ プランを指定します。Is the query plan in XML format to be applied as a hint.

XML プラン表示を変数に割り当てることをお勧めします。XML プラン表示を変数に割り当てない場合は、XML プラン表示内の単一引用符をエスケープする必要があります。これを行うには、単一引用符の前にもう 1 つ単一引用符を追加します。We recommend assigning the XML Showplan to a variable; otherwise, you must escape any single quotation marks in the Showplan by preceding them with another single quotation mark. 例 E を参照してください。See example E.

NULLNULL
クエリの OPTION 句で指定した既存のヒントがクエリに適用されないことを示します。Indicates that any existing hint specified in the OPTION clause of the query is not applied to the query. 詳細については、「 OPTION 句(transact-sql)」を参照してください。For more information, see OPTION Clause (Transact-SQL).

コメントRemarks

sp_create_plan_guide の引数は、表示される順序で指定する必要があります。The arguments to sp_create_plan_guide must be provided in the order that is shown. sp_create_plan_guideのパラメーターに値を指定する場合、パラメーター名はすべて明示的に指定するか、すべて指定しないかのいずれかにする必要があります。When you supply values for the parameters of sp_create_plan_guide, all parameter names must be specified explicitly, or none at all. たとえば、 @name = @ が指定されている場合は、 stmt=、type=なども指定する必要があります。@For example, if @name = is specified, then @stmt = , @type =, and so on, must also be specified. 同様に、 @name = を省略し、パラメーター値だけを指定した場合は、残りのパラメーター名も省略し、値だけを指定する必要があります。Likewise, if @name = is omitted and only the parameter value is provided, the remaining parameter names must also be omitted, and only their values provided. 引数の名前は、構文を理解しやすくするための説明目的のものです。Argument names are for descriptive purposes only, to help understand the syntax. SQL ServerSQL Server では、指定したパラメーター名と、その名前が使用されている位置にあるパラメーターの名前が一致しているかどうかは確認されません。does not verify that the specified parameter name matches the name for the parameter in the position where the name is used.

同一のクエリとバッチまたはモジュールに対し、複数の OBJECT または SQL プラン ガイドを作成できます。You can create more than one OBJECT or SQL plan guide for the same query and batch or module. ただし、有効にできるプラン ガイドは常に 1 つだけです。However, only one plan guide can be enabled at any given time.

@Module_or_batch 値に対して OBJECT 型のプランガイドを作成することはできません。これは、WITH ENCRYPTION 句を指定するストアドプロシージャ、関数、または DML トリガーを参照するか、一時的なものです。Plan guides of type OBJECT cannot be created for an @module_or_batch value that references a stored procedure, function, or DML trigger that specifies the WITH ENCRYPTION clause or that is temporary.

有効、無効にする場合のどちらでも、そのプラン ガイドで参照されている関数、ストアド プロシージャ、または DML トリガーを削除または変更しようとすると、エラーが発生します。Trying to drop or modify a function, stored procedure, or DML trigger that is referenced by a plan guide, either enabled or disabled, causes an error. プラン ガイドで参照され、トリガーが定義されているテーブルを削除しようとする場合もエラーが発生します。Trying to drop a table that has a trigger defined on it that is referenced by a plan guide also causes an error.

注意

プラン ガイドは、 MicrosoftMicrosoftSQL ServerSQL Serverのすべてのエディションで使用できるわけではありません。Plan guides cannot be used in every edition of MicrosoftMicrosoftSQL ServerSQL Server. SQL ServerSQL Serverの各エディションでサポートされる機能の一覧については、「 SQL Server 2016 の各エディションでサポートされる機能」を参照してください。For a list of features that are supported by the editions of SQL ServerSQL Server, see Features Supported by the Editions of SQL Server 2016. プラン ガイドはどのエディションでも表示できます。Plan guides are visible in any edition. また、プラン ガイドを含むデータベースは、どのエディションに対してもアタッチできます。You can also attach a database that contains plan guides to any edition. アップグレード済みのバージョンの SQL ServerSQL Serverにデータベースを復元またはアタッチした場合、プラン ガイドはまったく影響を受けません。Plan guides remain intact when you restore or attach a database to an upgraded version of SQL ServerSQL Server. サーバーのアップグレード後に、各データベース内のプラン ガイドが適切かどうかを確認する必要があります。You should verify the desirability of the plan guides in each database after performing a server upgrade.

プラン ガイドの照合要件Plan Guide Matching Requirements

Type = ' SQL ' @または@type = ' TEMPLATE ' を指定するプランガイドでは、クエリと正常に一致させるために、 batch_textおよび @parameter_name data_type [,...n ] は、アプリケーションによって送信された対応する形式とまったく同じ形式で指定する必要があります。For plan guides that specify @type = 'SQL' or @type = 'TEMPLATE' to successfully match a query, the values for batch_text and @parameter_name data_type [,...n ] must be provided in exactly the same format as their counterparts submitted by the application. つまり、バッチ テキストを、SQL ServerSQL Server コンパイラが受信したときとまったく同じように指定する必要があります。This means you must provide the batch text exactly as the SQL ServerSQL Server compiler receives it. 実際のバッチおよびパラメーター テキストをキャプチャするには、SQL Server プロファイラーSQL Server Profiler を使用する必要があります。To capture the actual batch and parameter text, you can use SQL Server プロファイラーSQL Server Profiler. 詳細については、「 SQL Server プロファイラー使用したプランガイドの作成とテスト」を参照してください。For more information, see Use SQL Server Profiler to Create and Test Plan Guides.

Type @= ' SQL ' と@module_or_batch が NULL に設定されている場合@、module_or_batch の値は stmt の@値に設定されます。つまり、 statement_textの値は、にSQL ServerSQL Server送信されるときとまったく同じ形式の文字で指定する必要があります。When @type = 'SQL' and @module_or_batch is set to NULL, the value of @module_or_batch is set to the value of @stmt. This means that the value for statement_text must be provided in exactly the same format, character-for-character, as it is submitted to SQL ServerSQL Server. この適合を容易にするために内部変換は実行されません。No internal conversion is performed to facilitate this match.

が statement_text の値を batch_text and @parameter_name data_type [,... SQL ServerSQL Server n ] または type @= ' OBJECT ' の場合、 object_name内の対応するクエリのテキストに対して、次の文字列要素は考慮されません。When SQL ServerSQL Server matches the value of statement_text to batch_text and @parameter_name data_type [,...n ], or if @type = ' OBJECT', to the text of the corresponding query inside object_name, the following string elements are not considered:

  • 文字列内の空白文字 (タブ、スペース、復帰、改行)White space characters (tabs, spaces, carriage returns, or line feeds) inside the string.

  • コメント ( -- また /* */ )。Comments (-- or /* */).

  • 末尾のセミコロンTrailing semicolons

たとえば、はSQL ServerSQL Serverstatement_text文字列N'SELECT * FROM T WHERE a = 10'を次のbatch_textに一致させることができます。For example, SQL ServerSQL Server can match the statement_text string N'SELECT * FROM T WHERE a = 10' to the following batch_text:

N'SELECT *

FROM T

WHERE a=10'

ただし、同じ文字列がこのbatch_textと一致しません。However, the same string would not be matched to this batch_text:

N'SELECT * FROM T WHERE b = 10'

SQL ServerSQL Server は、最初のクエリ内にある復帰、改行、空白文字を無視します。ignores the carriage return, line feed, and space characters inside the first query. 2 つ目のクエリのシーケンス WHERE b = 10 は、WHERE a = 10 とは異なるものと解釈されます。In the second query, the sequence WHERE b = 10 is interpreted differently from WHERE a = 10. 照合処理では、大文字小文字が区別されないキーワードを除き、(データベースの照合順序で大文字小文字が区別されない場合でも) 大文字小文字およびアクセントが区別されます。Matching is case- and accent-sensitive (even when the collation of the database is case-insensitive), except in the case of keywords, where case is insensitive. キーワードの省略形は区別されません。Matching is insensitive to shortened forms of keywords. たとえば、キーワード EXECUTEEXEC、および execute は同じものと解釈されます。For example, the keywords EXECUTE, EXEC, and execute are considered equivalent.

プラン キャッシュに対するプラン ガイドの効果Plan Guide Effect on the Plan Cache

モジュールにプラン ガイドを作成すると、そのモジュールのクエリ プランがプラン キャッシュから削除されます。Creating a plan guide on a module removes the query plan for that module from the plan cache. バッチに OBJECT 型または SQL 型のプラン ガイドを作成すると、同じハッシュ値を持つバッチのクエリ プランが削除されます。Creating a plan guide of type OBJECT or SQL on a batch removes the query plan for a batch that has the same hash value. TEMPLATE 型のプラン ガイドを作成すると、単一ステートメントのバッチがデータベース内のプラン キャッシュからすべて削除されます。Creating a plan guide of type TEMPLATE removes all single-statement batches from the plan cache within that database.

アクセス許可Permissions

OBJECT 型のプラン ガイドを作成するには、参照先オブジェクトに対する ALTER 権限が必要です。To create a plan guide of type OBJECT, requires ALTER permission on the referenced object. SQL または TEMPLATE タイプのプラン ガイドを作成するには、現在のデータベースに対する ALTER 権限が必要です。To create a plan guide of type SQL or TEMPLATE, requires ALTER permission on the current database.

使用例Examples

A.A. ストアド プロシージャのクエリに対する OBJECT タイプのプラン ガイドを作成するCreating a plan guide of type OBJECT for a query in a stored procedure

次の例では、アプリケーションベースのストアド プロシージャのコンテキストで実行されるクエリに適合するプラン ガイドを作成し、OPTIMIZE FOR ヒントをクエリに適用します。The following example creates a plan guide that matches a query executed in the context of an application-based stored procedure, and applies the OPTIMIZE FOR hint to the query.

ストアド プロシージャは次のようになります。Here is the stored procedure:

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  

ストアド プロシージャのクエリに対して作成されるプラン ガイドは次のようになります。Here is the plan guide created on the query in the stored procedure:

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.B. スタンドアロン クエリに対する SQL タイプのプラン ガイドを作成するCreating a plan guide of type SQL for a stand-alone query

次の例では、sp_executesql システム ストアド プロシージャを使用するアプリケーションで送信されるバッチ内のクエリに適合するプラン ガイドを作成します。The following example creates a plan guide to match a query in a batch submitted by an application that uses the sp_executesql system stored procedure.

バッチは、次のようになります。Here is the batch:

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

このクエリに対して並列実行プランが生成されないようにするには、次のプラン ガイドを作成します。To prevent a parallel execution plan from being generated on this query, create the following plan guide:

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.C. パラメーター化形式のクエリに対する TEMPLATE タイプのプラン ガイドを作成するCreating a plan guide of type TEMPLATE for the parameterized form of a query

次の例では、指定されたフォームにパラメーター化されるクエリに適合するプラン ガイドを作成し、 SQL ServerSQL Server に対してクエリのパラメーター化を強制的に実行させます。The following example creates a plan guide that matches any query that parameterizes to a specified form, and directs SQL ServerSQL Server to force parameterization of the query. 次の 2 つのクエリは構文的には同じですが、定数リテラル値のみが異なります。The following two queries are syntactically equivalent, but differ only in their constant literal values.

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

パラメーター化形式のクエリに対するプラン ガイドは次のようになります。Here is the plan guide on the parameterized form of the query:

EXEC sp_create_plan_guide   
    @name = N'TemplateGuide1',  
    @stmt = N'SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h  
              INNER JOIN AdventureWorks2012.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 パラメーターの値は、パラメーター化形式のクエリになっています。In the previous example, the value for the @stmt parameter is the parameterized form of the query. この値を取得して sp_create_plan_guide で使用できるようにするには、 sp_get_query_template システム ストアド プロシージャを使用するのが唯一信頼できる方法です。The only reliable way to obtain this value for use in sp_create_plan_guide is to use the sp_get_query_template system stored procedure. 次のスクリプトを使用すると、パラメーター化クエリを取得してそのクエリに対してプラン ガイドを作成することができます。The following script can be used both to obtain the parameterized query and then create a plan guide on it.

DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h  
      INNER JOIN AdventureWorks2012.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 パラメーターの定数リテラルの値は、リテラルを置き換えるパラメーターで選択されるデータ型に影響する場合があります。The value of the constant literals in the @stmt parameter passed to sp_get_query_template might affect the data type that is chosen for the parameter that replaces the literal. この値は、プラン ガイドの適合にも影響します。This will affect plan guide matching. 場合によっては、異なるパラメーター値範囲に対応する複数のプラン ガイドを作成する必要があります。You may have to create more than one plan guide to handle different parameter value ranges.

D.D. API カーソル要求を使用して送信されるクエリでプラン ガイドを作成するCreating a plan guide on a query submitted by using an API cursor request

プラン ガイドは、API サーバー カーソル ルーチンから送信されるクエリを適合できます。Plan guides can match queries that are submitted from API server cursor routines. これらのルーチンには、sp_cursorprepare、sp_cursorprepexec、および sp_cursoropen があります。These routines include sp_cursorprepare, sp_cursorprepexec, and sp_cursoropen. ADO、OLE DB、および ODBC API を使用するアプリケーションは、API サーバー カーソルを使用して SQL ServerSQL Server と頻繁に対話します。Applications that use the ADO, OLE DB, and ODBC APIs frequently interact with SQL ServerSQL Server by using API server cursors. RPC:Starting プロファイラー トレース イベントを表示して、SQL Server プロファイラーSQL Server Profiler トレース内の API サーバー カーソル ルーチンの呼び出しを参照できます。You can see the invocation of API server cursor routines in SQL Server プロファイラーSQL Server Profiler traces by viewing the RPC:Starting profiler trace event.

たとえば、次のデータが、プラン ガイドに合わせて調整するクエリの RPC:Starting プロファイル トレース イベントに示されているものとします。Suppose the following data appears in an RPC:Starting profiler trace event for a query you want to tune with a plan guide:

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 クエリに対するプランでマージ結合を使用していることがわかりますが、ハッシュ結合を使用するとします。You notice that the plan for the SELECT query in the call to sp_cursorprepexec is using a merge join, but you want to use a hash join. sp_cursorprepexec を使用して送信されるクエリは、クエリ文字列およびパラメーター文字列の両方を含めて、パラメーター化されます。The query submitted by using sp_cursorprepexec is parameterized, including both a query string and a parameter string. sp_cursorprepexec の呼び出しで、表示されているとおり完全に同じであるクエリ文字列とパラメーター文字列を使用して、次のプラン ガイドを作成し、プランの選択を変更できます。You can create the following plan guide to change the choice of plan by using the query and parameter strings exactly as they appear, character for character, in the call to 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)';  

今後アプリケーションでこのクエリを実行すると、ここで作成したプラン ガイドの影響を受け、クエリの処理にはハッシュ結合が使用されます。Subsequent executions of this query by the application will be affected by this plan guide, and a hash join will be used to process the query.

E.E. キャッシュされたプランから XML プラン表示を取得してプラン ガイドを作成するCreating a plan guide by obtaining the XML Showplan from a cached plan

次の例では、単純なアドホック SQL ステートメントのプラン ガイドを作成します。The following example creates a plan guide for a simple ad hoc SQL statement. このステートメントの目的のクエリ プランは、クエリの XML プラン表示を @hints パラメーターで直接指定することにより、プラン ガイドで提供されます。The desired query plan for this statement is provided in the plan guide by specifying the XML Showplan for the query directly in the @hints parameter. 最初に SQL ステートメントを実行して、プラン キャッシュ内にプランを生成します。The example first executes the SQL statement to generate a plan in the plan cache. この例では、生成されたプランが目的のプランであり、追加のクエリ チューニングが不要であると想定しています。For the purposes of this example, it is assumed that the generated plan is the desired plan and no additional query tuning is required. クエリの XML プラン表示は、 sys.dm_exec_query_statssys.dm_exec_sql_text、および sys.dm_exec_text_query_plan の各動的管理ビューをクエリすることにより取得され、 @xml_showplan 変数に割り当てられます。The XML Showplan for the query is obtained by querying the sys.dm_exec_query_stats, sys.dm_exec_sql_text, and sys.dm_exec_text_query_plan dynamic management views and is assigned to the @xml_showplan variable. 次に @xml_showplan 変数が、 sp_create_plan_guide パラメーターで @hints ステートメントに渡されます。The @xml_showplan variable is then passed to the sp_create_plan_guide statement in the @hints parameter. または、 sp_create_plan_guide_from_handle ストアド プロシージャを使用して、プラン キャッシュ内のクエリ プランからプラン ガイドを作成することもできます。Or, you can create a plan guide from a query plan in the plan cache by using the sp_create_plan_guide_from_handle stored procedure.

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

参照See Also

プランガイド Plan Guides
sp_control_plan_guide (Transact-SQL) sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL) sys.plan_guides (Transact-SQL)
データベース エンジン ストアド プロシージャ (Transact-SQL) Database Engine Stored Procedures (Transact-SQL)
システム ストアド プロシージャ (Transact-SQL) System Stored Procedures (Transact-SQL)
sys.dm_exec_sql_text (transact-sql (dm)) sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL) sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (transact-sql (dm)) sys.dm_exec_query_stats (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL) sp_create_plan_guide_from_handle (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL) sys.fn_validate_plan_guide (Transact-SQL)
sp_get_query_template (Transact-SQL)sp_get_query_template (Transact-SQL)