了解计划指南

2008-12-15

SQL Server 2005 引入了 sp_create_plan_guide 系统存储过程，用于创建优化查询性能的计划指南。当您无法或不希望直接更改查询文本时，可以使用此存储过程。当第三方供应商部署的数据库应用程序中的小部分查询未按要求执行时，计划指南非常有用。计划指南通过将查询提示附加到计划指南来影响查询的优化。在 sp_create_plan_guide 语句中，指定要优化的查询，以及包含用于优化该查询的查询提示的 OPTION 子句。执行查询时，SQL Server 在运行时将查询与计划指南匹配并将 OPTION 子句附加到查询。

注意：
计划指南只能在 SQL Server 2005 Standard、Developer、Evaluation 和 Enterprise 版本中创建及使用。但在所有版本中均可删除。

能够从计划指南中受益的查询通常是基于参数的，并且性能较差，因为它们使用的是参数值不表示最差情况或最具代表性方案的缓存查询计划。 OPTIMIZE FOR 查询提示和 RECOMPILE 查询提示可用于解决这一问题。优化查询时，OPTIMIZE FOR 会指示 SQL Server 使用参数的特定值。执行后，RECOMPILE 指示服务器放弃查询计划，并在下次执行相同的查询时强制查询优化器重新编译新的查询计划。

另一个与计划指南一起使用的常用提示为 USE PLAN 查询提示。因为您知道此查询提示的性能更好，所以当您意识到现有执行计划可以被优化器为特定查询选择的执行计划替代时，将应用此查询提示。执行查询时，USE PLAN 强制 SQL Server 使用提示语法中显式指定的特定查询计划。当通过重写查询来强制实施联接次序、使用联接提示或索引提示以便获取适合查询的执行计划最为方便时，应用 USE PLAN 查询提示的计划指南尤其有用。有关详细信息，请参阅计划强制方案：创建计划指南以强制实施从重写查询获得的计划。

有关 RECOMPILE、OPTIMIZE FOR、USE PLAN 和其他查询提示的详细信息，请参阅查询提示 (Transact-SQL)。

注意：
计划指南错用查询提示会导致出现编译、执行或性能问题。因此计划指南应仅由经验丰富的开发人员和数据库管理员使用。

可以创建计划指南来匹配下列上下文中执行的查询：

OBJECT 计划指南可与在 Transact-SQL 存储过程、标量函数、多语句表值函数和 DML 触发器的上下文中执行的查询匹配。
SQL 计划指南可与在不属于数据库对象的独立 Transact-SQL 语句和批处理的上下文中执行的查询匹配。基于 SQL 的计划指南还可用于与参数化为指定形式的查询匹配。
TEMPLATE 计划指南可与参数化为指定形式的独立查询匹配。这些计划指南用于覆盖查询类的数据库的当前 PARAMETERIZATION 数据库 SET 选项。

OBJECT 计划指南

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

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

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

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

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

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

SQL 计划指南

SQL 计划指南适用于应用程序使用 sp_executesql 系统存储过程频繁提交的语句和批处理。以下面的独立批处理为例：

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

若要防止生成此查询的并行执行计划，需创建下面的计划指南：

sp_create_plan_guide 
@name = N'Guide1', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader h 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 计划指南用于覆盖特定查询形式的参数化行为。您可以在以下任一情况下创建 TEMPLATE 计划指南：

PARAMETERIZATION 数据库选项设置为 FORCED，但是您要按照简单参数化规则编译某些查询。
PARAMETERIZATION 数据库选项设置为 SIMPLE（默认设置），但是您要尝试对某一类查询执行强制参数化。

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

还可以将 TEMPLATE 计划指南与 SQL 计划指南一起使用。例如，可以创建 TEMPLATE 计划指南以确保参数化查询类。然后，可以对该参数化形式的查询创建 SQL 计划指南。

请参阅

通过

了解计划指南

OBJECT 计划指南

SQL 计划指南

TEMPLATE 计划指南

请参阅

概念

其他资源

帮助和信息

其他资源