Plan Forcing Scenario: Create a Plan Guide to Force a Plan Obtained from a Rewritten Query
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 can help in this situation. Plan guides work by attaching query hints or query plans 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 the captured plan, follow this process:
Determine how to modify the query (by changing join order, using FORCE ORDER, join hints, or index hints, and employing other techniques), so that a good plan is produced, but the logical meaning of the query has not been changed.
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).
Create a plan guide to force the good plan that you obtained onto the original query.
Using SQL Server Profiler, select the Plan Guide Successful and Plan Guide Unsuccessful events from the Performance category, and then run the original query. Examine SQL Server Profiler to verify that the query is using the plan guide.
Example
Suppose the following query generates a query plan that is too slow.
USE AdventureWorks;
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
You can rewrite the query as follows to be logically the same but with a different join order, which is forced.
USE AdventureWorks;
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
OPTION (FORCE ORDER)',
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
To create a plan guide to force the plan for the rewritten query on the original query, capture the plan in a variable and specify the variable in the plan guide statement as shown in the following code.
DBCC FREEPROCCACHE;
GO
USE AdventureWorks;
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
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
,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 = @xml_showplan;
GO
SELECT * FROM sys.plan_guides;
GO
Before you run the original query, create a trace by using SQL Server Profiler and select the events Plan Guide Successful and Plan Guide Unsuccessful from the Performance category. Run the original query and verify the results of the query in the output of the trace.
USE AdventureWorks;
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