了解计划指南

假设下面的存储过程采用 @Country_region 参数，存在于对 AdventureWorks2008R2 数据库部署的数据库应用程序中：

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, 
        Sales.SalesTerritory AS t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country_region
END;

假设已为 @Country_region = N'AU'（澳大利亚）编译并优化了此存储过程。但是，由于来自澳大利亚的销售订单相对较少，当使用具有较多销售订单的国家/地区的参数值执行查询时，性能会降低。因为大多数销售订单来自美国，所以针对 @Country_region = N'US' 生成的查询计划的性能对于所有可能的 @Country_region 参数值而言都可能会更好一些。

您可以通过修改存储过程以将 OPTIMIZE FOR 查询提示添加到查询中来解决这一问题。但是，因为存储过程在部署应用程序中，所以您不能直接修改应用程序代码。不过，您可以在 AdventureWorks2008R2 数据库中创建下面的计划指南。

sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,
        Sales.Customer AS c,
        Sales.SalesTerritory AS t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country_region',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))'

执行在 sp_create_plan_guide 语句中指定的查询时，将在优化之前修改该查询以使其包括 OPTIMIZE FOR (@Country = N''US'') 子句。

SQL 计划指南适用于独立 Transact-SQL 语句和批处理。通常，这些语句由应用程序使用 sp_executesql 系统存储过程来提交。以下面的独立批处理为例：

SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;

为了防止对该查询生成并行执行计划，请创建以下计划指南并在 @hints 参数中将 MAXDOP 查询提示设置为 1。

sp_create_plan_guide 
@name = N'Guide2', 
@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)';

注意
包含要对其创建计划指南的语句的批处理不能包含 USE database 语句。

重要提示
为 sp_create_plan guide 语句的 @module_or_batch 和 @params 参数提供的值必须与在实际查询中提交的相应文本匹配。有关详细信息，请参阅 sp_create_plan_guide (Transact-SQL) 和使用 SQL Server Profiler 创建和测试计划指南。

PARAMETERIZATION 数据库选项设置为 FORCED 后，或者创建了指定参数化查询类的 TEMPLATE 计划指南后，还可以对参数化为相同形式的查询创建 SQL 计划指南。有关详细信息，请参阅设计参数化查询的计划指南。

TEMPLATE 计划指南用于覆盖特定查询形式的参数化行为。您可以在以下任一情况下创建 TEMPLATE 计划指南：

• PARAMETERIZATION 数据库选项设置为 FORCED，但是您要按照简单参数化规则编译某些查询。

• PARAMETERIZATION 数据库选项设置为 SIMPLE（默认设置），但是您要尝试对某一类查询执行强制参数化。

有关详细信息，请参阅使用计划指南指定查询参数化行为。

以下示例创建一个计划指南，它与被参数化为指定格式的任何查询匹配，并使 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 计划指南与 SQL 计划指南一起使用。例如，可以创建 TEMPLATE 计划指南以确保参数化查询类。然后，可以对该参数化形式的查询创建 SQL 计划指南。