通过

sp_create_plan_guide (Transact-SQL)

  • 项目

创建用于将查询提示或实际查询计划与数据库中的查询关联的计划指南。 有关计划指南的详细信息，请参阅计划指南。

主题链接图标 Transact-SQL 语法约定

语法

sp_create_plan_guide [ @name = ] N'plan_guide_name'
    , [ @stmt = ] N'statement_text'
    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
    , [ @module_or_batch = ]
      { 
                    N'[ schema_name. ] object_name'
        | N'batch_text'
        | NULL
      }
    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL } 
    , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )' 
                 | N'XML_showplan'
                 | NULL }

参数

  • [ @name = ] N'plan_guide_name'
    计划指南的名称。 计划指南名称的作用域限于当前数据库。 plan_guide_name 必须符合标识符规则，且不能以数字符号 (#) 开头。 plan_guide_name 的最大长度为 124 个字符。

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

    statement_text 的提供方式必须允许查询优化器将它与由 @module\_or\_batch 和 @params 标识的批处理或模块中提供的对应语句匹配。 有关详细信息，请参阅“备注”部分。 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 的批处理文本。 批处理文本不能包括 USEdatabase 语句。

    对于要与通过应用程序提交的批处理匹配的计划指南，必须按照提交到 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 计划指南进行匹配。

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

  • [@hints = ]{ N'OPTION (query_hint [ ,...n ] )' | N'XML_showplan' | NULL }

    • N'OPTION (query_hint [ ,...n ] )
      指定将 OPTION 子句附加到与 @stmt 匹配的查询上。 @hints 必须与 SELECT 语句中的 OPTION 子句采用相同的语法，并且可以包含任何有效的查询提示序列。

    • N'XML_showplan'
      要作为提示应用的、采用 XML 格式的查询计划。

      建议将 XML 显示计划分配给变量；否则，必须通过在单引号前面再加上一个单引号来对显示计划中的任何单引号进行转义。 请参见示例 E。

    • NULL
      指示查询的 OPTION 子句中指定的任何现有提示不应用于该查询。 有关详细信息，请参阅 OPTION 子句 (Transact-SQL)。

注释

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

您可以为相同的查询和批处理或模块创建多个 OBJECT 或 SQL 计划指南。 但是，在任何给定的时间只能启用一个计划指南。

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

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

注意注意

计划指南不适用于每个 Microsoft SQL Server 版本。 有关 SQL Server 的每个版本支持的功能列表，请参阅 SQL Server 2012 各个版本支持的功能。 计划指南在任何版本中可见。 包含计划指南的数据库可以附加到任何版本。 将数据库还原或附加到升级版本的 SQL Server 后，计划指南保持不变。 执行服务器升级后，应验证每个数据库中计划指南的性能。

符合要求的计划指南

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

当 @type = 'SQL' 且 @module\_or\_batch 设置为 NULL 时，@module_or_batch 的值将设置为 @stmt 的值。 这意味着 statement_text 值的提供格式必须与其提交到 SQL Server 时所采用的格式完全相同（字符对字符）。 不会执行内部转换来帮助完成该匹配。

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

  • 字符串内部的空白字符（制表符、空格、回车符或换行符）。

  • 注释（-- 或 /* */）。

  • 尾随分号

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

N'SELECT *

FROM T

WHERE a=10'

但是，不会将上面这个字符串与如下 batch_text 匹配：

N'SELECT * FROM T WHERE b = 10'

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

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

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

权限

若要创建类型为 OBJECT 的计划指南，需要拥有对被引用对象的 ALTER 权限。 若要创建类型为 SQL 或 TEMPLATE 的计划指南，需要拥有对当前数据库的 ALTER 权限。

示例

A.为存储过程中的查询创建类型为 OBJECT 的计划指南

以下示例创建一个计划指南，它与在基于应用程序的存储过程的上下文中所执行的查询匹配，并将 OPTIMIZE FOR 提示应用于该查询。

下面是此存储过程：

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
    DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountry 
    (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h 
    INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID
    INNER JOIN Sales.SalesTerritory AS t 
        ON c.TerritoryID = t.TerritoryID
    WHERE t.CountryRegionCode = @Country_region;
END
GO

下面是为此存储过程中的查询所创建的计划指南：

EXEC sp_create_plan_guide 
    @name =  N'Guide1',
    @stmt = N'SELECT *
              FROM Sales.SalesOrderHeader AS h 
              INNER JOIN Sales.Customer AS c 
                 ON h.CustomerID = c.CustomerID
              INNER JOIN Sales.SalesTerritory AS t 
                 ON c.TerritoryID = t.TerritoryID
              WHERE t.CountryRegionCode = @Country_region',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';

B.为独立查询创建类型为 SQL 的计划指南

以下示例创建一个计划指南，它与使用 sp_executesql 系统存储过程的应用程序所提交的批处理中的查询匹配。

下面是这个批处理：

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

若要防止为该查询生成并行执行计划，请创建以下计划指南：

EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @stmt = N'SELECT TOP 1 * 
              FROM Sales.SalesOrderHeader 
              ORDER BY OrderDate DESC', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (MAXDOP 1)';

C.为参数化格式的查询创建类型为 TEMPLATE 的计划指南

以下示例创建一个计划指南，它与被参数化为指定格式的任何查询匹配，并使 SQL Server 强制执行查询参数化。 下列两个查询在语法上是等价的，差别只是它们的常量文字值。

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;

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

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 参数的值是参数化格式的查询。 获取此值以用于 sp_create_plan_guide 的唯一可靠方法是使用 sp_get_query_template 系统存储过程。 以下脚本既可用来获得参数化查询，又可用来为它创建计划指南。

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 参数中的常量文字值可能会影响为替换该文字的参数选择的数据类型。 这将影响计划指南的匹配。 可能必须创建多个计划指南，以处理不同的参数值范围。

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

计划指南可以与通过 API 服务器游标例程所提交的查询匹配。 这些例程包括 sp_cursorprepare、sp_cursorprepexec 和 sp_cursoropen。 使用 ADO、OLE DB 和 ODBC API 的应用程序将使用 API 服务器游标与 SQL Server 频繁交互。 通过查看 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 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;

您会注意到，在 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)';

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

E.通过从缓存计划中获取 XML 显示计划来创建计划指南

下面的示例为简单的临时 SQL 语句创建一个计划指南。 在计划指南中，直接在 @hints 参数中为查询指定 XML 显示计划，从而为该语句提供了所需的查询计划。 该示例首先通过执行 SQL 语句在计划缓存中生成一个计划。 对于此示例，假定所生成的计划就是所需的计划，不需要做进一步的查询优化。 此查询的 XML 显示计划可通过查询 sys.dm_exec_query_stats、sys.dm_exec_sql_text 和 sys.dm_exec_text_query_plan 动态管理视图获得，并可以分配给 @xml\_showplan 变量。 然后，将 @xml\_showplan 变量传递给 sp_create_plan_guide 语句中的 @hints 参数。 您也可以使用 sp_create_plan_guide_from_handle 存储过程从计划缓存中的查询计划中创建计划指南。

USE 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

请参阅

参考

sp_control_plan_guide (Transact-SQL)

sys.plan_guides (Transact-SQL)

数据库引擎存储过程 (Transact-SQL)

系统存储过程 (Transact-SQL)

sys.dm_exec_sql_text (Transact-SQL)

sys.dm_exec_cached_plans (Transact-SQL)

sys.dm_exec_query_stats (Transact-SQL)

sp_create_plan_guide_from_handle (Transact-SQL)

sys.fn_validate_plan_guide (Transact-SQL)

sp_get_query_template (Transact-SQL)

概念

计划指南