通过

  • 项目

了解计划指南

本主题介绍计划指南并说明当您无法或不希望直接更改查询文本时，可以如何使用计划指南来优化查询性能。当第三方供应商部署的数据库应用程序中的小部分查询未按要求执行时，计划指南非常有用。计划指南通过将查询提示或固定的查询计划附加到查询来影响查询的优化。在计划指南中，您需要指定要优化的 Transact-SQL 语句以及包含要使用的查询提示的 OPTION 子句或要用于优化查询的特定查询计划。当执行查询时，SQL Server 将 Transact-SQL 语句与计划指南进行匹配，然后在运行时将此 OPTION 子句附加到查询，或使用指定的查询计划。

注意注意

只能在 SQL Server Standard Edition、Developer Edition、Evaluation Edition 和 Enterprise Edition 中使用计划指南；不过，在任何版本中都可看到计划指南。还可以将包含计划指南的数据库附加到任何版本。将数据库还原或附加到升级版本的 SQL Server 2008 后，计划指南保持不变。

将计划指南与查询进行匹配

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

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

  • SQL 计划指南与在独立 Transact-SQL 语句和不属于数据库对象的批处理上下文中执行的查询匹配。基于 SQL 的计划指南还可用于与参数化为指定形式的查询匹配。

  • TEMPLATE 计划指南与参数化为指定形式的独立查询匹配。这些计划指南用于覆盖查询类的数据库的当前 PARAMETERIZATION 数据库 SET 选项。有关详细信息，请参阅简单参数化和强制参数化。

有关详细信息，请参阅SQL Server 如何将计划指南与查询匹配。

OBJECT 计划指南

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

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

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 语句和批处理。通常，这些语句由应用程序使用 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 计划指南用于覆盖特定查询形式的参数化行为。您可以在以下任一情况下创建 TEMPLATE 计划指南：

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

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

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

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

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

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.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 AdventureWorks.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks.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 AdventureWorks.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks.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 计划指南。

将固定查询计划应用到计划指南

您可以将固定查询计划应用到 OBJECT 或 SQL 类型的计划指南。当您注意到对于特定查询现有的执行计划比优化器选择的计划执行得更好时，应用固定查询计划的计划指南将非常有用。

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

升级后验证计划指南

建议在将应用程序升级到 SQL Server 的新版本时，重新评估和测试计划指南定义。性能优化要求和计划指南匹配行为可能会发生更改。尽管无效的计划指南不会导致查询失败，但仍应在不使用计划指南的情况下对计划进行编译，并且该计划可能不是最好的选择。将数据库升级到 SQL Server 2008 后，建议执行下列任务：