sp_create_plan_guide (Transact-SQL)

项目
07/17/2024

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

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

语法

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

参数

[ @name = ] N'name'

计划指南的名称。 @name为 sysname，无默认值，最大长度为 124 个字符。计划指南名称的作用域限于当前数据库。 @name必须遵守标识符规则，并且不能以数字符号（#）开头。

[ @stmt = ] N'stmt'

要为其创建计划指南的 Transact-SQL 语句。 @stmt为 nvarchar（max），默认值为 NULL. 当 SQL Server 查询优化器识别与@stmt匹配的查询时，@name生效。若要成功创建计划指南，@stmt必须出现在由@type、@module_or_batch和@params参数指定的上下文中。

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

[ @type = ] { N'OBJECT' |N'SQL' |N'TEMPLATE' }

显示@stmt的实体的类型。这指定与@name匹配@stmt的上下文。 @type为 nvarchar（60），可以是以下值之一：

OBJECT

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

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

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

[ @module_or_batch = ] { N' [ schema_name。 ] object_name' |N'batch_text' }

指定显示@stmt的对象的名称，或显示@stmt的批处理文本。 @module_or_batch为 nvarchar（max），默认值为 NULL. 批文本不能包含 USE <database> 语句。

若要使计划指南与从应用程序提交的批处理匹配， @module_or_batch 必须采用与提交到 SQL Server 相同的格式（字符表示字符）。不会执行内部转换来帮助完成该匹配。有关详细信息，请参阅备注部分。

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

[ @params = ] N'@parameter_name data_type [ ,... n ]'

指定嵌入@stmt的所有参数的定义。 @params为 nvarchar（max），默认值为 NULL. 仅当以下任一选项为 true 时，@params才适用：

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

@params 必须采用与在参数化后使用 sp_executesql 或提交到 SQL Server 完全相同的格式提供。有关详细信息，请参阅备注部分。如果批处理不包含参数， NULL 则必须指定。 @params的大小仅受可用服务器内存的限制。

[ @hints = ] { N'OPTION （ query_hint [ ， ...n ] ]' |N'XML_showplan' }

@hints为 nvarchar（max），默认值为 NULL.

OPTION ( <query_hint> [ , ...n ] )

指定要OPTION附加到与@stmt匹配的查询的子句。 @hints在语法上必须与语句中的SELECT子句相同OPTION，并且可以包含任何有效的查询提示序列。
<XML_showplan>'

要作为提示应用的 XML 格式的查询计划。

建议将 XML 显示计划分配给变量。否则，您必须在 showplan 中转义任何单引号，方法是将它们与另一个单引号放在一起。请参阅示例 E。
NULL

指示查询子句中指定的 OPTION 任何现有提示不会应用于查询。有关详细信息，请参阅 OPTION 子句。

注解

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

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

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

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

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

计划指南匹配要求

对于指定@typeSQL或TEMPLATE成功匹配查询的计划指南，@module_or_batch和@params的值 [， ...n ] 必须以与应用程序提交的对应项完全相同的格式提供。这意味着必须提供与 SQL Server 编译器接收的批处理文本完全一样。若要捕获实际的批处理和参数文本，可以使用 SQL Server Profiler。有关详细信息，请参阅使用 SQL Server Profiler 创建和测试计划指南。

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

当 SQL Server 将@stmt的值与@module_or_batch和@params [， ...n ]，或如果@typeOBJECT是，到内部<object_name>相应查询的文本，则不考虑以下字符串元素：

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

例如，SQL Server 可以将@stmt字符串N'SELECT * FROM T WHERE a = 10'与以下@module_or_batch匹配：

 N'SELECT *
 FROM T
 WHERE a = 10'

但是，此@module_or_batch不匹配相同的字符串：

N'SELECT * FROM T WHERE b = 10'

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

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

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

权限

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

示例

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)';

重要

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

D. 使用 API 游标请求针对提交的查询创建计划指南

计划指南可以与通过 API 服务器游标例程所提交的查询匹配。这些例程包括 sp_cursorprepare， sp_cursorprepexec和 sp_cursoropen。使用 ADO、OLE DB 和 ODBC API 的应用程序经常使用 API 服务器游标与 SQL Server 进行交互。可以通过查看探查器跟踪事件来查看 SQL Server Profiler 跟踪中 API 服务器游标例程的 RPC:Starting 调用。

假设以下数据出现在 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 语句创建计划指南。此语句的所需查询计划通过在参数中直接指定查询的 XML 显示计划，在计划指南中 @hints 提供。该示例首先执行 SQL 语句以在计划缓存中生成计划。对于此示例，假定生成的计划是所需的计划，不需要进一步的查询优化。通过查询sys.dm_exec_query_statssys.dm_exec_sql_text和sys.dm_exec_text_query_plan动态管理视图获取查询的 XML 显示计划，并分配给变量@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_create_plan_guide (Transact-SQL)

语法

参数

[ @name = ] N'name'

[ @stmt = ] N'stmt'

[ @type = ] { N'OBJECT' |N'SQL' |N'TEMPLATE' }

[ @module_or_batch = ] { N' [ schema_name。 ] object_name' |N'batch_text' }

[ @params = ] N'@parameter_name data_type [ ,... n ]'

[ @hints = ] { N'OPTION （ query_hint [ ， ...n ] ]' |N'XML_showplan' }

注解

计划指南匹配要求

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

权限

示例

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

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

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

D. 使用 API 游标请求针对提交的查询创建计划指南

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

反馈

反馈

其他资源

通过

sp_create_plan_guide (Transact-SQL)

语法

参数

[ @name = ] N'name'

[ @stmt = ] N'stmt'

[ @type = ] { N'OBJECT' |N'SQL' |N'TEMPLATE' }

[ @module_or_batch = ] { N' [ schema_name。 ] object_name' |N'batch_text' }

[ @params = ] N'@parameter_name data_type [ ,... n ]'

[ @hints = ] { N'OPTION （ query_hint [ ， ...n ] ]' |N'XML_showplan' }

注解

计划指南匹配要求

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

权限

示例

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

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

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

D. 使用 API 游标请求针对提交的查询创建计划指南

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

相关内容

反馈

反馈

其他资源