sp_create_plan_guide (Transact-SQL)

更新日期： 2007 年 9 月 15 日

创建用于将查询提示与数据库中的查询进行关联的计划指南。 有关计划指南的详细信息，请参阅使用计划指南在部署的应用程序中优化查询。

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 ] )' | NULL }

参数

• [ @name= ] N'plan_guide_name'
  指定用于标识计划指南的名称。 计划指南名称的使用范围限于当前数据库。plan_guide_name 必须符合标识符规则，并且不能以数字符号 (#) 开头。

• [ @stmt= ] N'statement_text'
  根据其创建计划指南的 Transact-SQL 语句。 当 SQL Server 查询优化器识别与 statement_text 匹配的查询时，plan_guide_name 将生效。 若要成功创建计划指南，statement_text 必须出现在 @type、@module_or_batch 和 @params 参数所指定的上下文中。

  statement_text 的提供方式必须允许 SQL Server 将它与在由 @module_or_batch 和 @params 所标识的批处理或模块中所提供的对应语句相匹配。在 SQL Server 尝试该匹配之前（空格、注释和关键字大小写不重要），statement_text 将转换为标准的内部格式。 有关详细信息，请参阅“备注”部分。 statement_text 的大小只受服务器的可用内存限制。

• [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
  显示 statement_text 的实体的类型。 它指定用于将 statement_text 与 plan_guide_name 进行匹配的上下文。

  • OBJECT
    指示 statement_text 出现在当前数据库内的 Transact-SQL 存储过程、标量函数、多语句表值函数或 Transact-SQL DML 触发器的上下文中。
  • SQL
    指示 statement_text 出现在可以通过任何机制提交到 SQL Server 的独立语句或批处理的上下文中。由公共语言运行时 (CLR) 对象或扩展存储过程，或使用 EXEC N'sql_string' 提交的 Transact-SQL 语句将作为批处理在服务器上进行处理，因此，应当将这些语句标识为 @type ='SQL'。 如果指定了 SQL，则无法在 @hints 参数中指定 PARAMETERIZATION { FORCED | SIMPLE } 查询提示。
  • TEMPLATE
    指示计划指南应用于可参数化为 statement_text 中所指示的格式的任何查询。 如果指定了 TEMPLATE，则只能在 @hints 参数中指定 PARAMETERIZATION { FORCED | SIMPLE } 查询提示。 有关 TEMPLATE 计划指南的详细信息，请参阅使用计划指南指定查询参数化行为。

• [ @module_or_batch = ] { N'[ schema_name**.** ] object_name**'** | N'batch_text' | NULL }
  指定显示 statement_text 的对象的名称，或指定显示 statement_text 的批处理文本。 批处理文本不能包括 USE database 语句。

  对于要与通过应用程序提交的批处理匹配的计划指南，必须按照提交到 SQL Server 时所采用的格式（字符对字符）来提供 batch_tex。 不会执行内部转换来帮助完成该匹配。 有关详细信息，请参阅“备注”部分。

  [schema_name.]object_name 指定其中包含 statement_text 的 Transact-SQL 存储过程、标量函数、多语句表值函数或 Transact-SQL DML 触发器的名称。 如果未指定 schema_name ，则 schema_name 将使用当前用户的架构。 如果指定了 NULL 并且 @type='SQL'，则 @module_or_batch 的值将设置为值 @stmt。 如果 @type='TEMPLATE'，则 @module_or_batch 必须为 NULL。

• [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }
  指定嵌入 statement_text 中的所有参数的定义。 只有当下列任意一个条件为真时才会应用 @params：

  • @type='SQL' 或 'TEMPLATE'。 如果等于 'TEMPLATE'，则 @params 不能为 NULL。
  • statement_text 使用 sp_executesql 进行提交，并且指定了 @params 参数的值，或者，SQL Server 在参数化语句之后内部提交该语句。 对于 SQL Server 来说，从数据库 API（包括 ODBC、OLE DB 和 ADO.NET）提交参数化查询类似于调用 sp_executesql 或调用 API 服务器游标例程；因此，它们也可以通过 SQL 或 TEMPLATE 计划指南进行匹配。 有关参数化和计划指南的详细信息，请参阅SQL Server 如何将计划指南与查询匹配。

  @parameter_name data_type 必须完全按照提交到 SQL Server 时所采用的格式来提供，方法包括使用 sp_executesql，或在参数化该语句之后内部提交它。 有关详细信息，请参阅“备注”部分。 如果批处理不包含参数，则必须指定 NULL。 @params 的大小只受可用的服务器内存限制。

• [@hints = ] { **N'**OPTION **(**query_hint [ ,...n ] )' | NULL }
  指定将 OPTION 子句附加到与 @stmt 匹配的查询上。 @hints 必须与 SELECT 语句中的 OPTION 子句采用相同的语法，并且可以包含任何有效的查询提示序列。 NULL 指示没有 OPTION 子句。 有关详细信息，请参阅 OPTION 子句 (Transact-SQL)。

备注

sp_create_plan_guide 的参数必须以显示的顺序提供。 为 sp_create_plan_guide 的参数提供值时，所有参数名称都必须显式指定，或全部不指定。 例如，如果指定了 @name =，则也必须指定 @stmt =、@type = 等。 同样，如果省略了 @name = 并仅提供了参数值，则其余的参数名称也必须省略并仅提供它们的值。 参数名称仅用于说明，以帮助了解语法。SQL Server 不会验证指定的参数名称是否与使用此名称位置中的参数名称相匹配。

对于特定的 @module_or_batch 和 @stmt 组合，只能为其创建一个计划指南。

无法为引用存储过程、函数或 DML 触发器（指定了 WITH ENCRYPTION 子句或为临时触发器）的 @module_or_batch 值创建 OBJECT 类型的计划指南。

如果尝试删除或修改的函数、存储过程或 DML 触发器由某个计划指南引用，则不管该指南为启用状态还是禁用状态，都会导致错误。 尝试删除被计划指南引用并已为其定义触发器的表也将导致错误。

注意：
计划指南只能在 SQL Server Standard、Developer、Evaluation 和 Enterprise 版本中使用；但是，在任何版本中均可显示计划指南。 包含计划指南的数据库可以附加到任何版本。 将数据库还原或附加到升级版本的 SQL Server 2008 后，计划指南保持不变。 执行服务器升级后，应验证每个数据库中计划指南的性能。

计划指南匹配要求

若要使指定 @type='SQL' 或 @type='TEMPLATE' 的计划指南与查询成功匹配，则 batch_text 和 @parameter_name data_type [,...n ] 的值的提供格式必须与应用程序提交这些值时采用的格式完全相同。 这表示必须完全按照 SQL Server 编译器接收批处理文本的方式来提供批处理文本。 若要捕获实际的批处理和参数文本，可以使用 SQL Server Profiler。 有关详细信息，请参阅使用 SQL Server Profiler 创建和测试计划指南。

当 @type = 'SQL' 并且 @module\_or\_batch 设置为 NULL 时，@module_or_batch 的值将设置为 @stmt 的值。 也就是说，在将 statement_text 的值提交到 SQL Server 时必须以完全相同的格式（每个字符均相同）提供。 不会执行内部转换来帮助完成该匹配。

当 SQL Server 将 statement_text 的值与 batch_text 和 @parameter_name data_type [,...n ] 进行匹配时，或者如果 @type='OBJECT'，则与 object_name 内相应查询的文本进行匹配时，将不考虑下列字符串元素：

• 字符串内部的空白字符（制表符、空格、回车符或换行符）。
• 注释（-- 或 /* */）。
• 尾随分号

例如，SQL Server 可以将 statement_text 字符串 N'SELECT * FROM T WHERE a = 10' 与下列 batch_text 进行匹配：

N'SELECT *

FROM T

WHERE a=10'

但是，相同的字符串不会与该 batch_text 匹配：

N'SELECT * FROM T WHERE b = 10'

SQL Server 将忽略第一个查询内部的回车符、换行符和空格字符。 在第二个查询中，序列 WHERE b = 10 的解释方式不同于 WHERE a = 10。 除了在关键字情况（不区分大小写）中以外，匹配是区分大小写和区分重音的（即使数据库的排序规则不区分大小写）。 对于缩短形式的关键字，匹配不进行区分。 例如，关键字 EXECUTE、EXEC 和 execute 被看作是等价的。

有关计划指南如何与查询匹配的详细信息，请参阅使用计划指南在部署的应用程序中优化查询。

计划指南对计划缓存的影响

对某个模块创建计划指南将从计划缓存中删除该模块的查询计划。 对批处理创建 OBJECT 或 SQL 类型的计划指南将删除具有相同哈希值的批处理的查询计划。 创建 TEMPLATE 类型的计划指南将从该数据库的计划缓存中删除所有单语句批处理。

权限

若要创建类型 OBJECT 的计划指南（通过指定 @type='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 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;
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',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country = 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 强制执行查询参数化。 下列两个查询在语法上是等价的，差别只是它们的常量文字值。

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

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

下面是参数化格式的查询的计划指南：

EXEC sp_create_plan_guide 
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
                  ON h.SalesOrderID = d.SalesOrderID
              WHERE h.SalesOrderID = @0',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@0 int',
    @hints = N'OPTION(PARAMETERIZATION FORCED)';

在上一个示例中，@stmt 参数的值是参数化格式的查询。 获得该值以便在 sp_create_plan_guide 中使用的唯一可靠方式是使用 sp_get_query_template 系统存储过程。 以下脚本既可用来获得参数化查询，又可用来为它创建计划指南。

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template 
    N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
          ON h.SalesOrderID = d.SalesOrderID
      WHERE h.SalesOrderID = 45639;',
    @stmt OUTPUT, 
    @params OUTPUT
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
    N'TEMPLATE', 
    NULL, 
    @params, 
    N'OPTION(PARAMETERIZATION FORCED)';

重要提示：
传递到 sp_get_query_template 的 @stmt 参数中的常量文字值可能会影响为替换该文字的参数选择的数据类型。 这将影响计划指南的匹配。 可能必须创建多个计划指南，以处理不同的参数值范围。

有关如何获得参数化格式的查询以便在基于 TEMPLATE 的计划指南中使用的详细信息，请参阅设计参数化查询的计划指南。

D. 为通过使用 API 游标请求所提交的查询创建计划指南

计划指南可以与通过 API 服务器游标例程所提交的查询匹配。 这些例程包括 sp_cursorprepare、sp_cursorprepexec 和 sp_cursoropen。 使用 ADO、OLE DB 和 ODBC API 的应用程序将使用 API 服务器游标与 SQL Server 频繁交互。 有关详细信息，请参阅 API 服务器游标。 通过查看 RPC:Starting 事件探查器跟踪事件，可以在 SQL Server Profiler 跟踪中看到 API 服务器游标例程的调用。

假设以下数据出现在希望用计划指南进行优化的查询的 RPC:Starting 事件探查器跟踪事件中：

DECLARE @p1 int;
SET @p1=-1;
DECLARE @p2 int;
SET @p2=0;
DECLARE @p5 int;
SET @p5=4104;
DECLARE @p6 int;
SET @p6=8193;
DECLARE @p7 int;
SET @p7=0;
EXEC sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(255),@P2 varchar(255)',N'SELECT * FROM Sales.SalesOrderHeader h INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate BETWEEN @P1 AND @P2',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT,'20040101','20050101'
SELECT @p1, @p2, @p5, @p6, @p7;

您会注意到，在 sp_cursorprepexec 调用中，SELECT 查询计划正在使用合并联接，但您希望使用哈希联接。 使用 sp_cursorprepexec 所提交的查询将被参数化，包括查询字符串和参数字符串。 您可以完全按照查询字符串和参数字符串在 sp_cursorprepexec 调用中的显示方式（字符对字符）来使用它们创建以下计划指南，从而更改对计划的选择。

EXEC sp_create_plan_guide 
    @name = N'APICursorGuide',
    @stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h 
              INNER JOIN Sales.SalesOrderDetail AS d 
                ON h.SalesOrderID = d.SalesOrderID 
              WHERE h.OrderDate BETWEEN @P1 AND @P2',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 varchar(255),@P2 varchar(255)',
    @hints = N'OPTION(HASH JOIN)';

该计划指南将影响应用程序随后对该查询的执行，并且哈希联接将用来处理该查询。

有关如何在通过游标提交的查询的计划指南中使用 USE PLAN 查询提示的信息，请参阅对包含游标的查询使用 USE PLAN 查询提示。

请参阅

参考

sp_control_plan_guide (Transact-SQL)
sys.plan_guides
数据库引擎存储过程 (Transact-SQL)
系统存储过程 (Transact-SQL)

帮助和信息

获取 SQL Server 2005 帮助

更改历史记录