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

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

创建用于将查询提示或实际查询计划与数据库中的查询关联的计划指南。Creates a plan guide for associating query hints or actual query plans with queries in a database. 有关计划指南的详细信息,请参阅 Plan GuidesFor 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 Server查询优化器识别匹配的查询statement_textplan_guide_name才会生效。When the SQL ServerSQL Server query optimizer recognizes a query that matches statement_text, plan_guide_name takes effect. 有关创建计划指南可以成功, statement_text必须出现在指定的上下文@类型, @module_or_batch,和@params 参数。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.

[@类型 =] N'{对象 |SQL |模板}[@type = ]N'{ OBJECT | SQL | TEMPLATE }'
是实体在其中一种statement_text出现。Is the type of entity in which statement_text appears. 这将指定用于匹配的上下文statement_textplan_guide_nameThis specifies the context for matching statement_text to plan_guide_name.

OBJECTOBJECT
指示statement_text的上下文中显示Transact-SQLTransact-SQL存储过程、 标量函数、 多语句表值函数或Transact-SQLTransact-SQLDML 触发器在当前数据库中的。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 查询提示 PARAMETERIZATION {FORCED |不能指定简单}@提示参数。If SQL is specified, the query hint PARAMETERIZATION { FORCED | SIMPLE } cannot be specified in the @hints parameter.

TEMPLATETEMPLATE
指示计划指南应用于任何查询进行参数化到窗体中所示statement_textIndicates the plan guide applies to any query that parameterizes to the form indicated in statement_text. 如果指定了 TEMPLATE,仅 PARAMETERIZATION {FORCED |简单} 查询提示可以指定@提示参数。If TEMPLATE is specified, only the PARAMETERIZATION { FORCED | SIMPLE } query hint can be specified in the @hints parameter. 有关 TEMPLATE 计划指南的详细信息,请参阅通过使用计划指南指定查询参数化行为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. 批处理文本不能包括 USE数据库语句。The batch text cannot include a USEdatabase statement.

用于计划指南以匹配应用程序中,通过提交的批处理batch_text 必须提供相同的格式字符的字符,提交到SQL ServerSQL ServerFor 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包含的 DML 触发器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 并且@类型 = 'SQL',值@module_or_batch 设置的值为@stmt。如果@类型 = 模板 ' , @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_textSpecifies the definitions of all parameters that are embedded in statement_text. @params 应用仅在下列任一条件为 true 时:@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. 对于 SQL ServerSQL Server来说,从数据库 API(包括 ODBC、OLE DB 和 ADO.NET)提交参数化查询类似于调用 sp_executesql 或 API 服务器游标例程;因此,它们也可以通过 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提交到完全相同的格式必须提供SQL ServerSQL Server通过使用 sp_executesql 或参数化语句之后内部提交。@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.

[@hints = ]{ N'OPTION (query_hint [ , ...n ] )' | N'XML_showplan' | NULL }[@hints = ]{ N'OPTION (query_hint [ ,...n ] )' | N'XML_showplan' | NULL }
N'OPTION (query_hint [, ...n ])N'OPTION (query_hint [ ,...n ] )
指定要附加到匹配的查询的 OPTION 子句@stmt。@提示必须是语法上相同 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_showplanN'XML_showplan'
要作为提示应用的、采用 XML 格式的查询计划。Is the query plan in XML format to be applied as a hint.

建议将 XML 显示计划分配给变量;否则,必须通过在单引号前面再加上一个单引号来对显示计划中的任何单引号进行转义。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. 例如,如果 @名称 = 指定了,则 @stmt =@类型 = ,依此类推,还必须指定。For example, if @name = is specified, then @stmt = , @type =, and so on, must also be specified. 同样,如果 @名称 = 省略并仅提供参数值,也必须省略其余的参数名称,并提供它们的值。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. 但是,在任何给定的时间只能启用一个计划指南。However, only one plan guide can be enabled at any given time.

不能用于创建对象的类型的计划指南@引用存储的过程、 函数或 DML 触发器,指定了 WITH ENCRYPTION 子句或为临时的 module_or_batch 值。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 ServerPlan 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

是否有指定的计划指南@类型 = 'SQL' 或@类型 = '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 ProfilerSQL Server ProfilerTo capture the actual batch and parameter text, you can use SQL Server ProfilerSQL Server Profiler. 有关详细信息,请参阅使用 SQL Server Profiler 创建和测试计划指南For more information, see Use SQL Server Profiler to Create and Test Plan Guides.

当@类型 = 'SQL' 并@module_or_batch 设置为 NULL,则@module_or_batch 设置的值为@stmt。这意味着,对于值statement_text必须提供完全相同的格式字符的字符,提交到SQL ServerSQL ServerWhen @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_textbatch_text@parameter_name data_type [, ...n ],或者如果@类型 = ' 对象,向内相应查询的文本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. 在第二个查询中,序列 WHERE b = 10 的解释方式不同于 WHERE a = 10In 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. 例如,关键字 EXECUTEEXECexecute 被看作是等价的。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. 下列两个查询在语法上是等价的,差别只是它们的常量文字值。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 ProfilerSQL Server Profiler 跟踪中看到对 API 服务器游标例程的调用。You can see the invocation of API server cursor routines in SQL Server ProfilerSQL 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. 在计划指南中,直接在 @hints 参数中为查询指定 XML 显示计划,从而为该语句提供了所需的查询计划。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_textsys.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) 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) 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)