计划指南Plan Guides

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

如果您无法或不希望直接在 SQL Server 2017SQL Server 2017中更改实际查询文本,则可以使用计划指南来优化查询性能。Plan guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query in SQL Server 2017SQL Server 2017. 计划指南通过将查询提示或固定的查询计划附加到查询来影响查询的优化。Plan guides influence the optimization of queries by attaching query hints or a fixed query plan to them. 当第三方供应商提供的数据库应用程序中的一个小的查询子集没有按预期执行时,计划指南将很有用。Plan guides can be useful when a small subset of queries in a database application provided by a third-party vendor are not performing as expected. 在计划指南中,您需要指定要优化的 Transact-SQL 语句以及包含要使用的查询提示的 OPTION 子句或要用于优化查询的特定查询计划。In the plan guide, you specify the Transact-SQL statement that you want optimized and either an OPTION clause that contains the query hints you want to use or a specific query plan you want to use to optimize the query. 当执行查询时, SQL ServerSQL Server 将 Transact-SQL 语句与计划指南进行匹配,然后在运行时将此 OPTION 子句附加到查询,或使用指定的查询计划。When the query executes, SQL ServerSQL Server matches the Transact-SQL statement to the plan guide and attaches the OPTION clause to the query at run time or uses the specified query plan.

可创建的计划指南总数仅受可用系统资源的限制。The total number of plan guides you can create is limited only by available system resources. 尽管如此,计划指南还是应当限于针对提高或稳定性能的关键查询。Nevertheless, plan guides should be limited to mission-critical queries that are targeted for improved or stabilized performance. 计划指南不应用来影响已部署应用程序的大部分查询负荷。Plan guides should not be used to influence most of the query load of a deployed application.

备注

计划指南不适用于每个 MicrosoftMicrosoftSQL ServerSQL ServerPlan guides cannot be used in every edition of MicrosoftMicrosoftSQL ServerSQL Server. 有关 SQL ServerSQL Server各版本支持的功能列表,请参阅 SQL Server 2016 各个版本支持的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Features Supported by the Editions of SQL Server 2016. 计划指南在任何版本中可见。Plan guides are visible in any edition. 包含计划指南的数据库可以附加到任何版本。You can also attach a database that contains plan guides to any edition. 将数据库还原或附加到升级版本的 SQL ServerSQL Server后,计划指南保持不变。Plan guides remain intact when you restore or attach a database to an upgraded version of SQL ServerSQL Server.

计划指南的类型Types of Plan Guides

可以创建以下类型的计划指南。The following types of plan guides can be created.

OBJECT 计划指南OBJECT plan guide

OBJECT 计划指南与在 Transact-SQLTransact-SQL 存储过程、用户定义标量函数、多语句表值用户定义函数和 DML 触发器上下文中执行的查询匹配。An OBJECT plan guide matches queries that execute in the context of Transact-SQLTransact-SQL stored procedures, scalar user-defined functions, multi-statement table-valued user-defined functions, and DML triggers.

假设下面的存储过程采用 @Country_region参数,位于对 AdventureWorks2012AdventureWorks2012 数据库部署的数据库应用程序中:Suppose the following stored procedure, which takes the @Country_region parameter, is in a database application that is deployed against the AdventureWorks2012AdventureWorks2012 database:

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'(澳大利亚)编译并优化了此存储过程。Assume that this stored procedure has been compiled and optimized for @Country_region = N'AU' (Australia). 但是,由于来自澳大利亚的销售订单相对较少,当使用具有较多销售订单的国家/地区的参数值执行查询时,性能会降低。However, because there are relatively few sales orders that originate from Australia, performance decreases when the query executes using parameter values of countries with more sales orders. 因为大多数销售订单来自美国,所以针对 @Country_region = N'US' 生成的查询计划的性能对于所有可能的 @Country_region 参数值而言都可能会更好一些。Because the most sales orders originate in the United States, a query plan that is generated for @Country_region = N'US' would likely perform better for all possible values of the @Country_region parameter.

您可以通过修改存储过程以将 OPTIMIZE FOR 查询提示添加到查询中来解决这一问题。You could address this problem by modifying the stored procedure to add the OPTIMIZE FOR query hint to the query. 但是,因为存储过程在部署应用程序中,所以您不能直接修改应用程序代码。However, because the stored procedure is in a deployed application, you cannot directly modify the application code. 不过,您可以在 AdventureWorks2012AdventureWorks2012 数据库中创建下面的计划指南。Instead, you can create the following plan guide in the AdventureWorks2012AdventureWorks2012 database.

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'') 子句。When the query specified in the sp_create_plan_guide statement executes, the query is modified before optimization to include the OPTIMIZE FOR (@Country = N''US'') clause.

SQL 计划指南SQL plan guide

SQL 计划指南与在独立 Transact-SQLTransact-SQL 语句和不属于数据库对象的批处理上下文中执行的查询匹配。An SQL plan guide matches queries that execute in the context of stand-alone Transact-SQLTransact-SQL statements and batches that are not part of a database object. 基于 SQL 的计划指南还可用于与参数化为指定形式的查询匹配。SQL-based plan guides can also be used to match queries that parameterize to a specified form. SQL 计划指南适用于独立 Transact-SQLTransact-SQL 语句和批处理。SQL plan guides apply to stand-alone Transact-SQLTransact-SQL statements and batches. 通常,这些语句由应用程序使用 sp_executesql 系统存储过程来提交。Frequently, these statements are submitted by an application by using the sp_executesql system stored procedure. 以下面的独立批处理为例:For example, consider the following stand-alone batch:

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

为了防止对该查询生成并行执行计划,请创建以下计划指南并在 MAXDOP 参数中将 1 查询提示设置为 @hintsTo prevent a parallel execution plan from being generated on this query, create the following plan guide and set the MAXDOP query hint to 1 in the @hints parameter.

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_executesql 提交的以下 SQL 语句。As another example, consider the following SQL statement submitted using sp_executesql.

exec sp_executesql N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id', N'@so_id int', @so_id = 43662;  

若要为此查询的每次执行创建唯一计划,请创建以下计划指南并使用 @hints 参数中的 OPTION (RECOMPILE) 查询提示。To create a unique plan for every execution of this query, create the following plan guide and use the OPTION (RECOMPILE) query hint in the @hints parameter.

exec sp_create_plan_guide   
@name = N'PlanGuide1_SalesOrders',   
@stmt = N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id',
@type = N'SQL',  
@module_or_batch = NULL,   
@params = N'@so_id int',   
@hints = N'OPTION (recompile)';

重要

@module_or_batch 语句的 @paramssp_create_plan guide 参数提供的值必须与在实际查询中提交的相应文本匹配。The values that are supplied for the @module_or_batch and @params arguments of the sp_create_plan guide statement must match the corresponding text submitted in the actual query. 有关详细信息,请参阅 sp_create_plan_guide (Transact-SQL) 语句的 使用 SQL Server Profiler 创建和测试计划指南中更改实际查询文本,则可以使用计划指南来优化查询性能。For more information, see sp_create_plan_guide (Transact-SQL) and Use SQL Server Profiler to Create and Test Plan Guides.

PARAMETERIZATION 数据库选项设置为 FORCED 后,或者创建了指定参数化查询类的 TEMPLATE 计划指南后,还可以对参数化为相同形式的查询创建 SQL 计划指南。SQL plan guides can also be created on queries that parameterize to the same form when the PARAMETERIZATION database option is SET to FORCED, or when a TEMPLATE plan guide is created specifying that a parameterized class of queries.

TEMPLATE 计划指南TEMPLATE plan guide

TEMPLATE 计划指南与参数化为指定形式的独立查询匹配。A TEMPLATE plan guide matches stand-alone queries that parameterize to a specified form. 这些计划指南用于覆盖查询类的数据库的当前 PARAMETERIZATION 数据库 SET 选项。These plan guides are used to override the current PARAMETERIZATION database SET option of a database for a class of queries.

您可以在以下任一情况下创建 TEMPLATE 计划指南:You can create a TEMPLATE plan guide in either of the following situations:

  • PARAMETERIZATION 数据库选项设置为 FORCED,但是你要按照简单参数化规则编译某些查询。The PARAMETERIZATION database option is SET to FORCED, but there are queries you want compiled according to the rules of Simple Parameterization.

  • PARAMETERIZATION 数据库选项设置为 SIMPLE(默认设置),但是您要尝试对某一类查询执行强制参数化The PARAMETERIZATION database option is SET to SIMPLE (the default setting), but you want Forced Parameterization to be tried on a class of queries.

符合要求的计划指南Plan Guide Matching Requirements

计划指南的作用域是在其中创建这些计划指南的数据库。Plan guides are scoped to the database in which they are created. 因此,执行查询时,只有处于当前状态的数据库中的计划指南可以与该查询匹配。Therefore, only plan guides that are in the database that is current when a query executes can be matched to the query. 例如,如果 AdventureWorks2012AdventureWorks2012 是当前数据库并执行下面的查询:For example, if AdventureWorks2012AdventureWorks2012 is the current database and the following query executes:

SELECT FirstName, LastName FROM Person.Person;

则只有 AdventureWorks2012AdventureWorks2012 数据库中的计划指南可以与此查询匹配。Only plan guides in the AdventureWorks2012AdventureWorks2012 database are eligible to be matched to this query. 但是,如果 AdventureWorks2012AdventureWorks2012 是当前数据库并运行下列语句:However, if AdventureWorks2012AdventureWorks2012 is the current database and the following statements are run:

USE DB1; 
SELECT FirstName, LastName FROM Person.Person;

则只有 DB1 中的计划指南可以与该查询匹配,这是因为该查询是在 DB1的上下文中执行的。Only plan guides in DB1 are eligible to be matched to the query because the query is executing in the context of DB1.

对于基于 SQL 或 TEMPLATE 的计划指南, SQL ServerSQL Server 通过对 @module_or_batch 参数和 @params 参数的值逐个字符地进行比较来将这两个值与查询匹配。For SQL- or TEMPLATE-based plan guides, SQL ServerSQL Server matches the values for the @module_or_batch and @params arguments to a query by comparing the two values character by character. 这意味着必须提供与 SQL ServerSQL Server 在实际批处理中接收的文本完全相同的文本。This means you must provide the text exactly as SQL ServerSQL Server receives it in the actual batch.

当 @type = 'SQL' 且 @module_or_batch 设置为 NULL 时,则将 @module_or_batch 的值设置为 @stmt 的值。这意味着 statement_text 值的提供格式必须与其提交到 SQL ServerSQL Server时所采用的格式完全相同(字符对字符)。When @type = 'SQL' and @module_or_batch is set to NULL, the value of @module_or_batch is set to the value of @stmt. This means that the value for statement_text must be provided in the identical format, character-for-character, as it is submitted to SQL ServerSQL Server. 不会执行内部转换来帮助完成该匹配。No internal conversion is performed to facilitate this match.

当常规(SQL 或 OBJECT)计划指南和 TEMPLATE 计划指南都适用于语句时,将只使用常规计划指南。When both a regular (SQL or OBJECT) plan guide and a TEMPLATE plan guide can apply to a statement, only the regular plan guide will be used.

备注

包含要对其创建计划指南的语句的批处理不能包含 USE database 语句。The batch that contains the statement on which you want to create a plan guide cannot contain a USE database statement.

计划指南对计划缓存的影响Plan Guide Effect on the Plan Cache

对模块创建计划指南将会从计划缓存中删除该模块的查询计划。Creating a plan guide on a module removes the query plan for that module from the plan cache. 对批处理创建类型为 OBJECT 或 SQL 的计划指南会删除具有相同哈希值的批处理的查询计划。Creating a plan guide of type OBJECT or SQL on a batch removes the query plan for a batch that has the same hash value. 创建类型为 TEMPLATE 的计划指南会从该数据库中的计划缓存中删除所有单语句批处理。Creating a plan guide of type TEMPLATE removes all single-statement batches from the plan cache within that database.

任务Task 主题Topic
说明如何创建计划指南。Describes how to create a plan guide. 创建新的计划指南Create a New Plan Guide
说明如何为参数化查询创建计划指南。Describes how to create a plan guide for parameterized queries. 为参数化查询创建计划指南Create a Plan Guide for Parameterized Queries
说明如何通过使用计划指南控制查询参数化行为。Describes how to control query parameterization behavior by using plan guides. 使用计划指南指定查询参数化行为Specify Query Parameterization Behavior by Using Plan Guides
说明如何在计划指南中包括固定查询计划。Describes how to include a fixed query plan in a plan guide. 将现有查询计划应用到计划指南Apply a Fixed Query Plan to a Plan Guide
说明如何在计划指南中指定查询提示。Describes how to specify query hints in a plan guide. 将查询提示附加到计划指南Attach Query Hints to a Plan Guide
说明如何查看计划指南属性。Describes how to view plan guide properties. 查看计划指南属性View Plan Guide Properties
说明如何使用 SQL Server 事件探查器创建和测试计划指南。Describes how to use SQL Server Profiler to create and test plan guides. 使用 SQL Server Profiler 创建和测试计划指南Use SQL Server Profiler to Create and Test Plan Guides
说明如何验证计划指南。Describes how to validate plan guides. 升级后验证计划指南Validate Plan Guides After Upgrade

另请参阅See Also

sp_create_plan_guide (Transact-SQL) sp_create_plan_guide (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL) sp_create_plan_guide_from_handle (Transact-SQL)
sp_control_plan_guide (Transact-SQL) sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL) sys.plan_guides (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL)sys.fn_validate_plan_guide (Transact-SQL)