sp_create_plan_guide (Transact-SQL)

  • 项目

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例

创建用于将查询提示或实际查询计划与数据库中的查询关联的计划指南。 有关计划指南的详细信息，请参阅 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 }

参数

[ @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参数指定的上下文中。

必须 以允许查询优化器将其与@module_or_batch和@params标识的批处理或模块中提供的相应语句匹配的方式提供statement_text。 有关更多信息，请参见“备注”部分。 statement_text的大小仅受服务器的可用内存限制。

[@type = ]N'{ 对象 |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数据库 语句。

要使计划指南与从应用程序提交的批相匹配，batch_text 必须以提交到SQL Server时采用相同的格式（字符对应字符）。 不会执行内部转换来帮助完成该匹配。 有关详细信息，请参见“备注”部分。

[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内部提交语句。 从数据库 API 提交参数化查询 (包括 ODBC、OLE DB 和 ADO.NET) 似乎SQL Server为对 sp_executesql API 服务器游标例程的调用或对 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 ] )
指定要附加到与@stmt匹配的查询的 OPTION 子句。@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 2016 各个版本支持的功能。 计划指南在任何版本中可见。 包含计划指南的数据库可以附加到任何版本。 将数据库还原或附加到升级版本的 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 AdventureWorks2022.Sales.SalesOrderHeader AS h  
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d   
    ON h.SalesOrderID = d.SalesOrderID  
WHERE h.SalesOrderID = 45639;  
  
SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h  
INNER JOIN AdventureWorks2022.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 AdventureWorks2022.Sales.SalesOrderHeader AS h  
              INNER JOIN AdventureWorks2022.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 AdventureWorks2022.Sales.SalesOrderHeader AS h  
      INNER JOIN AdventureWorks2022.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 profiler 跟踪事件，在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;

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