计划强制方案：创建计划指南以强制实施从重写查询获得的计划

2008-12-15

通常，要获得改进的查询计划，最方便的方法是使用查询提示手动重写查询以强制实施联接次序、联接算法或索引的使用，但不更改查询的逻辑含义。但是，如果查询是在部署的应用程序内部执行的，则可能不能使用此方法。在这种情况下，请使用包含 USE PLAN 查询提示的计划指南。当不可能或不希望直接更改查询文本时，计划指南通过将查询提示附加到查询来发挥作用。有关详细信息，请参阅使用计划指南在部署的应用程序中优化查询。

若要手动重写查询，请捕获该查询的计划，然后通过包含 USE PLAN 提示的计划指南将捕获的计划应用于原始查询，过程如下：

确定如何通过更改联接次序、使用 FORCE ORDER、使用联接提示、索引提示和其他方法来修改查询，从而为该查询生成一个良好计划，但不更改查询的逻辑含义。
捕获重写查询的计划，该查询的提交方式与原始查询一样（例如通过使用 sp_executesql、sp_cursorprepexec 或作为独立的批次）。
通过附加包含捕获的计划的 OPTION (USE PLAN) 查询提示子句来修改原始查询的副本，并测试是否可以对该查询强制实施捕获的计划。
如果测试失败，则尝试其他查询重写或进行调试，直到获得可以对原始查询强制实施的合适计划。
创建计划指南以对原始查询强制实施所获得的良好计划。

示例

假定以下查询生成一个相当慢的查询计划：

USE AdventureWorks;
GO
SET STATISTICS XML ON;
GO
EXEC sp_executesql 
@stmt = N'SELECT 
    soh.[SalesPersonID]
    ,c.[FirstName] + '' '' + COALESCE(c.[MiddleName], '''') + '' '' + c.[LastName] AS [FullName]
    ,e.[Title]
    ,st.[Name] AS [SalesTerritory]
    ,soh.[SubTotal]
    ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
FROM [Sales].[SalesPerson] sp 
    INNER JOIN [Sales].[SalesOrderHeader] soh 
    ON sp.[SalesPersonID] = soh.[SalesPersonID]
    INNER JOIN [Sales].[SalesTerritory] st 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [HumanResources].[Employee] e 
    ON soh.[SalesPersonID] = e.[EmployeeID] 
    INNER JOIN [Person].[Contact] c 
    ON e.[ContactID] = c.ContactID
WHERE st.[Group] = @p1', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
SET STATISTICS XML OFF;
GO

您可以重写该查询（如下），使其逻辑保持不变，但强制实施了不同的联接次序。

USE AdventureWorks;
GO
SET STATISTICS XML ON;
GO
EXEC sp_executesql 
@stmt = N'SELECT 
    soh.[SalesPersonID]
    ,c.[FirstName] + '' '' + COALESCE(c.[MiddleName], '''') + '' '' + c.[LastName] AS [FullName]
    ,e.[Title]
    ,st.[Name] AS [SalesTerritory]
    ,soh.[SubTotal]
    ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
FROM [Sales].[SalesPerson] sp 
    INNER JOIN [Sales].[SalesTerritory] st -- Moved this join earlier 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [Sales].[SalesOrderHeader] soh 
    ON sp.[SalesPersonID] = soh.[SalesPersonID]
    INNER JOIN [HumanResources].[Employee] e 
    ON soh.[SalesPersonID] = e.[EmployeeID] 
    INNER JOIN [Person].[Contact] c 
    ON e.[ContactID] = c.ContactID
WHERE st.[Group] = @p1
OPTION (FORCE ORDER)',  -- force join order to be as specified in FROM list
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
SET STATISTICS XML OFF
GO

在捕获了重写查询的 STATISTICS XML 查询计划并对原始查询测试了该计划后，创建计划指南以对原始查询强制实施该计划，如以下代码所示：

EXEC sp_create_plan_guide
@name = N'ForceOrderGuide',
@stmt = N'SELECT 
    soh.[SalesPersonID]
    ,c.[FirstName] + '' '' + COALESCE(c.[MiddleName], '''') + '' '' + c.[LastName] AS [FullName]
    ,e.[Title]
    ,st.[Name] AS [SalesTerritory]
    ,soh.[SubTotal]
    ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
FROM [Sales].[SalesPerson] sp 
    INNER JOIN [Sales].[SalesOrderHeader] soh 
    ON sp.[SalesPersonID] = soh.[SalesPersonID]
    INNER JOIN [Sales].[SalesTerritory] st 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [HumanResources].[Employee] e 
    ON soh.[SalesPersonID] = e.[EmployeeID] 
    INNER JOIN [Person].[Contact] c 
    ON e.[ContactID] = c.ContactID
WHERE st.[Group] = @p1', 
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@p1 nvarchar(80)',
@hints = N'OPTION (USE PLAN 
N''?put XML showplan for modified query here ?')'

请确保在 @hints 字符串中替换查询计划之前，使用四个单引号 ('''') 转义 XML 查询计划中的所有单引号 (')。这是因为查询计划嵌套在两个字符串文字内。

请参阅

通过

计划强制方案：创建计划指南以强制实施从重写查询获得的计划

示例

请参阅

任务

概念

其他资源

帮助和信息

其他资源