通过

sp_create_plan_guide (Transact-SQL)

  • 项目

适用于：SQL Server Azure 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的实体的类型。 这指定与plan_guide_name匹配statement_text的上下文。

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

SQL
指示 statement_text 显示在可通过任何机制提交到 SQL Server 的独立语句或批处理的上下文中。 公共语言运行时（CLR）对象或扩展存储过程（或使用 EXEC N'sql_string'提交的 Transact-SQL 语句）在服务器上作为批处理进行处理，因此，应将其标识为@type = “SQL”。 如果指定了 SQL，则查询提示 PARAMETERIZATION { FORCED |不能在 @hints 参数中指定 SIMPLE } 。

TEMPLATE
指示计划指南适用于参数化为statement_text中所示表单的任何查询。 如果指定 TEMPLATE，则仅 PARAMETERIZATION { FORCED |可以在 @hints 参数中指定 SIMPLE } 查询提示。 有关 TEMPLATE 计划指南的详细信息，请参阅 使用计划指南指定查询参数化行为。

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

若要使计划指南与从应用程序提交的批处理匹配， batch_tex不能以与提交到 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的所有参数的定义。 仅当以下任一项为 true 时，@params才适用：

  • @type = “SQL” 或 “TEMPLATE”。 如果为“TEMPLATE”，则@params不得为 NULL。

  • statement_text 通过使用 sp_executesql 和指定@params参数的值提交，或者 SQL Server 在参数化后在内部提交语句。 从数据库 API（包括 ODBC、OLE DB 和 ADO.NET）提交参数化查询显示为对 SQL Server 的调用或对 API 服务器游标例程的调用 sp_executesql ;因此，它们也可以由 SQL 或 TEMPLATE 计划指南进行匹配。

@parameter_name data_type必须采用与在参数化后使用sp_executesql或提交到 SQL Server 完全相同的格式提供。 有关详细信息，请参阅“备注”部分。 如果批处理不包含参数，则必须指定 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）