通过

  • 项目

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

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

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

  1. 确定如何修改查询（通过更改联接次序，使用 FORCE ORDER、联接提示或索引提示，以及利用其他方法），以便生成一个良好计划，而不更改查询的逻辑含义。

  2. 捕获重写查询的计划，该查询的提交方式与原始查询一样（例如通过使用 sp_executesql、sp_cursorprepexec 或作为独立的批次）。

  3. 创建计划指南以对原始查询强制实施所获得的良好计划。

  4. 使用 SQL Server Profiler，从**“性能”**类别中选择 Plan Guide Successful 和 Plan Guide Unsuccessful 事件，然后运行原始查询。检查 SQL Server Profiler 以验证该查询是否正在使用此计划指南。

示例

假定下面的查询生成一个相当慢的查询计划。

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

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

USE AdventureWorks2008R2;
GO
EXEC sp_executesql 
@stmt = N'SELECT
    soh.SalesPersonID
    ,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
    ,e.JobTitle
    ,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.BusinessEntityID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.BusinessEntityID 
    INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
    WHERE st.[Group] = @p1
    OPTION (FORCE ORDER)', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO

若要创建一个计划指南来对原始查询强制实施重写查询的计划，请将此计划捕获至变量中，并按照如下代码所示在计划指南语句中指定该变量。

DBCC FREEPROCCACHE;
GO
USE AdventureWorks2008R2;
GO
EXEC sp_executesql 
@stmt = N'SELECT
    soh.SalesPersonID
    ,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
    ,e.JobTitle
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson sp 
    INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee AS e ON soh.SalesPersonID = e.BusinessEntityID 
    INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
    WHERE st.[Group] = @p1
    OPTION (FORCE ORDER)', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
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'%Sales.SalesPerson%' AND st.text LIKE N'%OPTION (FORCE ORDER)%');

EXEC sp_create_plan_guide
@name = N'ForceOrderGuide1',
@stmt = N'SELECT
    soh.SalesPersonID
    ,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
    ,e.JobTitle
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson AS sp 
    INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory ASst ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee ASe ON soh.SalesPersonID = e.BusinessEntityID 
    INNER JOIN Person.Person ASp ON e.BusinessEntityID = p.BusinessEntityID
    WHERE st.[Group] = @p1', 
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@p1 nvarchar(80)',
@hints = @xml_showplan;

GO
SELECT * FROM sys.plan_guides;
GO

运行原始查询之前，请使用 SQL Server Profiler 创建一个跟踪并从**“性能”**类别中选择 Plan Guide Successful 和 Plan Guide Unsuccessful 事件。运行原始查询并验证跟踪输出中的查询结果。

USE AdventureWorks2008R2;
GO
EXEC sp_executesql 
@stmt = N'SELECT
    soh.SalesPersonID
    ,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
    ,e.JobTitle
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson AS sp 
    INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory ASst ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee ASe ON soh.SalesPersonID = e.BusinessEntityID 
    INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
    WHERE st.[Group] = @p1', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO