sp_create_plan_guide (Transact-SQL)

若要使指定 @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 的计划指南会从该数据库中的计划缓存中删除所有单语句批处理。

以下示例创建一个计划指南，它与在基于应用程序的存储过程的上下文中所执行的查询匹配，并将 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''))';

以下示例创建一个计划指南，它与使用 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)';

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

SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

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

有关如何获得参数化格式的查询以便在基于 TEMPLATE 的计划指南中使用的详细信息，请参阅设计参数化查询的计划指南。

计划指南可以与通过 API 服务器游标例程所提交的查询匹配。这些例程包括 sp_cursorprepare、sp_cursorprepexec 和 sp_cursoropen。使用 ADO、OLE DB 和 ODBC API 的应用程序将使用 API 服务器游标与 SQL Server 频繁交互。有关详细信息，请参阅 API 服务器游标。通过查看 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 h INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate >= @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 >= @P1 AND <= @P2',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 varchar(255),@P2 varchar(255)',
    @hints = N'OPTION(HASH JOIN)';

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

有关如何在通过游标提交的查询的计划指南中使用 USE PLAN 查询提示的信息，请参阅对包含游标的查询使用 USE PLAN 查询提示。

下面的示例为简单的临时 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 AdventureWorks2008R2;
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