Plan Forcing Scenario: Create a Plan Guide to Force a Plan Obtained from a Rewritten Query

  • Article

Frequently the most convenient way to obtain an improved plan for a query is to manually rewrite the query to force join order, join algorithms, or index usage by using query hints, without changing the logical meaning of the query. However, if the query is inside a deployed application, this method may not be available. Using plan guides together with the USE PLAN query hint can help in this situation. Plan guides work by attaching query hints to queries when it is not possible or desirable to change the text of a query directly. For more information, see Optimizing Queries in Deployed Applications by Using Plan Guides.

To manually rewrite a query by hand, capture the plan for it, and then apply the captured plan to the original query with a plan guide that contains a USE PLAN hint, follow this process:

  1. Determine how to modify the query by changing join order, using FORCE ORDER, using join hints, index hints, and other techniques, so that a good plan is produced for it, but the logical meaning of the query has not been changed.
  2. Capture the plan for the rewritten query, submitted just like the original query (such as by using sp_executesql, sp_cursorprepexec, or as a stand-alone batch).
  3. Modify a copy of the original query by attaching an OPTION (USE PLAN) query hint clause that contains the captured plan, and test to see whether you can force the captured plan on the query.
  4. If the test fails, try other query rewrites or otherwise debug until you obtain a suitable plan that can be forced on the original query.
  5. Create a plan guide to force the good plan that you obtained onto the original query.

Example

Suppose the following query generates a query plan that is too slow:

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

You can rewrite the query as follows to be logically the same but with a different join order, which is forced.

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

After capturing the STATISTICS XML query plan for the rewritten query, and testing it on the original query, create a plan guide to force the plan on the original query, as shown in the following code:

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 …'')'

Make sure that you escape any single quotation mark (') inside the XML query plan by using four single quotation marks ('''') before substituting the query plan in the @hints string. This is because the query plan is nested inside two string literals.

See Also

Tasks

Plan Forcing Scenario: Create a Plan Guide That Uses a USE PLAN Query Hint

Concepts

Plan Forcing Scenarios and Examples
Specifying Query Plans with Plan Forcing

Other Resources

Query Performance
sp_create_plan_guide (Transact-SQL)
Query Hint (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance