计划指南

项目
06/25/2013

如果您无法或不希望直接在 SQL Server 2012 中更改实际查询文本，则可以使用计划指南来优化查询性能。计划指南通过将查询提示或固定的查询计划附加到查询来影响查询的优化。当第三方供应商提供的数据库应用程序中的一个小的查询子集没有按预期执行时，计划指南将很有用。在计划指南中，您需要指定要优化的 Transact-SQL 语句以及包含要使用的查询提示的 OPTION 子句或要用于优化查询的特定查询计划。当执行查询时，SQL Server 将 Transact-SQL 语句与计划指南进行匹配，然后在运行时将此 OPTION 子句附加到查询，或使用指定的查询计划。

可创建的计划指南总数仅受可用系统资源的限制。尽管如此，计划指南还是应当限于针对提高或稳定性能的关键查询。计划指南不应用来影响已部署应用程序的大部分查询负荷。

注意
计划指南不适用于 Microsoft SQL Server 的各个版本。有关 SQL Server 的版本支持的功能的列表，请参阅 SQL Server 2012 各个版本支持的功能。计划指南在任何版本中都是可见的。包含计划指南的数据库可以附加到任何版本。将数据库还原或附加到升级版本的 SQL Server 后，计划指南保持不变。

计划指南的类型

可以创建以下类型的计划指南。

OBJECT 计划指南
OBJECT 计划指南与在 Transact-SQL 存储过程、用户定义标量函数、多语句表值用户定义函数和 DML 触发器上下文中执行的查询匹配。

假设下面的存储过程采用 @Country\_region 参数，位于对 AdventureWorks2012 数据库部署的数据库应用程序中：
```
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 查询提示添加到查询中来解决这一问题。但是，因为存储过程在部署应用程序中，所以您不能直接修改应用程序代码。不过，您可以在 AdventureWorks2012 数据库中创建下面的计划指南。
```
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 计划指南
SQL 计划指南与在独立 Transact-SQL 语句和不属于数据库对象的批处理上下文中执行的查询匹配。基于 SQL 的计划指南还可用于与参数化为指定形式的查询匹配。 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)';

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

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

TEMPLATE 计划指南
TEMPLATE 计划指南与参数化为指定形式的独立查询匹配。这些计划指南用于覆盖查询类的数据库的当前 PARAMETERIZATION 数据库 SET 选项。

您可以在以下任一情况下创建 TEMPLATE 计划指南：
- PARAMETERIZATION 数据库选项设置为 FORCED，但是您要按照简单参数化规则编译某些查询。
- PARAMETERIZATION 数据库选项设置为 SIMPLE（默认设置），但是您要尝试对某一类查询执行强制参数化。

符合要求的计划指南

计划指南的作用域是在其中创建这些计划指南的数据库。因此，执行查询时，只有处于当前状态的数据库中的计划指南可以与该查询匹配。例如，如果 AdventureWorks2012 是当前数据库并执行下面的查询：

SELECT FirstName, LastName FROM Person.Person;

则只有 AdventureWorks2012 数据库中的计划指南可以与此查询匹配。但是，如果 AdventureWorks2012 是当前数据库并运行下列语句：

USE DB1;

SELECT FirstName, LastName FROM Person.Person;

则只有 DB1 中的计划指南可以与该查询匹配，这是因为该查询是在 DB1 的上下文中执行的。

对于基于 SQL 或 TEMPLATE 的计划指南，SQL Server 通过对 @module\_or\_batch 和 @params 参数的值逐个字符地进行对比来将这两个值与查询匹配。这意味着必须提供与 SQL Server 在实际批处理中接收的文本完全相同的文本。

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

当常规（SQL 或 OBJECT）计划指南和 TEMPLATE 计划指南都适用于语句时，将只使用常规计划指南。

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

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

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

任务	主题
说明如何创建计划指南。	创建新的计划指南
说明如何为参数化查询创建计划指南。	为参数化查询创建计划指南
说明如何通过使用计划指南控制查询参数化行为。	使用计划指南指定查询参数化行为
说明如何在计划指南中包括固定查询计划。	将现有查询计划应用到计划指南
说明如何在计划指南中指定查询提示。	将查询提示附加到计划指南
说明如何查看计划指南属性。	查看计划指南属性
说明如何使用 SQL Server 事件探查器创建和测试计划指南。	使用 SQL Server Profiler 创建和测试计划指南
说明如何验证计划指南。	升级后验证计划指南

请参阅

参考

sp_create_plan_guide (Transact-SQL)

sp_create_plan_guide_from_handle (Transact-SQL)

sp_control_plan_guide (Transact-SQL)

sys.plan_guides (Transact-SQL)

sys.fn_validate_plan_guide (Transact-SQL)

通过

计划指南

计划指南的类型

符合要求的计划指南

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

相关任务

请参阅

参考

其他资源