大网数据广告活动
数据库与应用安全

SQL Server 数据库 — sp_create_plan_guide (TRANSACT-SQL)

· 52CE 我爱测

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

适用对象:yesSQL Server(从 2008 版开始)yesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库

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

主题链接图标 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 }  

参数

[@名称 =] 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必须出现在指定的上下文@类型, @module_or_batch,和@params 参数。

statement_text必须允许查询优化器以匹配使用提供的批处理中的相应语句或模块由标识的方式提供@module_or_batch 和@params。 有关详细信息,请参阅"备注"部分。 大小statement_text仅受可用内存的服务器。

[@类型 =] N'{对象 |SQL |模板}
是实体在其中一种statement_text出现。 这将指定用于匹配的上下文statement_text到plan_guide_name。

OBJECT
指示statement_text的上下文中显示Transact-SQL存储过程、 标量函数、 多语句表值函数或Transact-SQLDML 触发器在当前数据库中的。

SQL
指示statement_text独立语句或批处理的可提交到上下文中显示SQL Server通过任何机制。 Transact-SQL 语句提交由公共语言运行时 (CLR) 对象或扩展存储的过程,或使用 EXEC N'sql_string,在服务器上的批次处理,并因此,应将标识为@类型**=** 'SQL'。 如果指定了 SQL 查询提示 PARAMETERIZATION {FORCED |不能指定简单}@提示参数。

TEMPLATE
指示计划指南应用于任何查询进行参数化到窗体中所示statement_text。 如果指定了 TEMPLATE,仅 PARAMETERIZATION {FORCED |简单} 查询提示可以指定@提示参数。 有关 TEMPLATE 计划指南的详细信息,请参阅通过使用计划指南指定查询参数化行为。

[@module_or_batch =] {N'[ schema_name。 ] object_name|N'batch_text|NULL}
指定在其中的对象的名称statement_text出现,或在其中的批处理文本statement_text出现。 批处理文本不能包括 USE数据库语句。

用于计划指南以匹配应用程序中,通过提交的批处理batch_text 必须提供相同的格式字符的字符,提交到SQL Server。 不会执行内部转换来帮助完成该匹配。 有关详细信息,请参见“备注”部分。

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

[ @params =] {N'@parameter_name data_type [,....n ]' |NULL}
指定的定义中嵌入的所有参数statement_text。 @params 应用仅在下列任一条件为 true 时:

  • @类型 = '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 仅受可用的服务器内存。

[@提示 =] {N'OPTION (query_hint [,....n ]) |N'XML_showplan|NULL}
N'OPTION (query_hint [,...n ])
指定要附加到匹配的查询的 OPTION 子句@stmt。@提示必须是语法上相同 SELECT 语句中的 OPTION 子句,并且可以包含任何有效的查询提示序列。

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

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

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

备注

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

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

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

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

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

符合要求的计划指南

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

当@类型 = 'SQL' 并@module_or_batch 设置为 NULL,则@module_or_batch 设置的值为@stmt。这意味着,对于值statement_text必须提供完全相同的格式字符的字符,提交到SQL Server。 不会执行内部转换来帮助完成该匹配。

当SQL Server的值匹配statement_text到batch_text并 @parameter_name data_type [,....n ],或者如果@类型 = ' 对象,向内相应查询的文本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 的计划指南会从该数据库中的计划缓存中删除所有单语句批处理。

Permissions

若要创建类型为 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)';  

Important:
传递到 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 事件探查器 跟踪中看到对 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;  

您会注意到,在 SELECT 调用中,sp_cursorprepexec 查询计划正在使用合并联接,但您希望使用哈希联接。 使用 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)

历史资料。此文对应 2018-11-30 的中文文档快照,适用产品版本和命令行为以原文为准。

来源与署名:Microsoft 和文档贡献者(原始作者元数据 stevestein)。固定版本原文。文档采用 CC BY 4.0,示例代码采用 MIT 授权。本站展开固定版本仓库内的引用内容,将 Markdown 转为 HTML,规范化链接,移除外部媒体、发布器占位标签和导航标记。

资料来源与适用范围

资料来源
Microsoft / SQL Server 中文文档历史快照
适用范围
历史中文快照 2018-11-30;详见原文产品版本;文档日期 03/16/2017
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-16
授权说明
CC BY 4.0(文档)/ MIT(示例代码)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。