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

適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database

クエリヒントまたは実際のクエリプランをデータベース内のクエリに関連付けるためのプランガイドを作成します。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. Statement_text に SQL ServerSQL Server 一致するクエリがクエリオプティマイザーにstatement_textよって認識されると、 plan_guide_nameが有効になります。When the SQL ServerSQL Server query optimizer recognizes a query that matches statement_text, plan_guide_name takes effect. プランガイドの作成を成功させるにはstatement_text 、 @ 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_text のコンテキストが plan_guide_nameに指定されます。This specifies the context for matching statement_text to plan_guide_name.

OBJECTOBJECT
現在statement_textTransact-SQLTransact-SQL データベースのストアドプロシージャ、スカラー関数、複数ステートメントのテーブル値関数、または DML トリガーのコンテキストで statement_text が表示されることを示し Transact-SQLTransact-SQL ます。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
任意のメカニズムを通じてに送信できるスタンドアロンのステートメントまたはバッチのコンテキストで statement_text 表示されることを示し SQL ServerSQL Server ます。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.

テンプレートTEMPLATE
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' |空白] 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には、statement_text を Transact-SQLTransact-SQL 含むストアドプロシージャ、スカラー関数、複数ステートメントのテーブル値関数、または DML トリガーの名前を指定し Transact-SQLTransact-SQL ます。 statement_text[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 ] ' |空白[ @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 プラン表示を変数に割り当てることをお勧めします。それ以外の場合は、Showplan で1つの引用符をもう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.

@WITH ENCRYPTION 句を指定するストアドプロシージャ、関数、または DML トリガー、または一時的なを参照する module_or_batch 値に対して、OBJECT 型のプランガイドを作成することはできません。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.

SQL ServerSQL Server Statement_textの値がbatch_textに一致し、 * @ parameter_name data_type* [,...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 Server statement_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)
dm_exec_sql_text (Transact-sql) sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL) sys.dm_exec_cached_plans (Transact-SQL)
dm_exec_query_stats (Transact-sql) 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)