Plan guides and parameterization

We’ve all had this experience. A third party application is running a piece of sql that simply isn’t choosing the best query plan, and if only it would use a hash join instead of a nested loop. Or maybe it’s blocking other processes and a nolock hint would help. The problem is, you can’t change the code.

Or can you?

SQL Server plan guides effectively allow you to optimize performance of queries you can’t modify directly by attaching hints to them. Let’s say you have an application running the following query:

And after some testing, you decide it will run much better if it were using MERGE JOINs.  So you add a hint.

That’s all well and good, but you can’t go into the application to add that hint.  And then there’s the added complication of the usage of literal values in the sql, instead of variables.  So the optimizer will see every execution of this query for ‘Manufacturing’ as completely different from an execution for ‘Quality Assurance’.  In order for a query plan to work for any value, you’ll have to parameterize the query.  Now, you could force parameterization at the database level, but that’s a mighty big hammer for this little nail.  So, instead we’ll create a plan guide using 3 steps.

• Step 1: We’ll use sp_get_query_template to generate a parameterized version of this query
• Step 2: We’ll force parameterization of any query that matches this form.  
• Step 3: We can create the plan guide. 

Sound complicated?  It’s not.  Look:

/*    Step 1: Create a parameterized version of the query. */

DECLARE @stmt nvarchar(max)
DECLARE @params nvarchar(max)
EXEC sp_get_query_template
N'SELECT E.EmployeeID, C.LastName, C.FirstName, D.GroupName, E.Title, P.PayFrequency, P.Rate
FROM 
    HumanResources.Employee E 
    inner join Person.Contact C on E.ContactID = C.ContactID
    inner join HumanResources.EmployeePayHistory P on E.EmployeeID = p.EmployeeID
            AND P.RateChangeDate = 
                (SELECT MAX(RateChangeDate) FROM HumanResources.EmployeePayHistory P2
                    WHERE P2.EmployeeID = P.EmployeeID)
    inner join HumanResources.EmployeeDepartmentHistory DH on E.EmployeeID = DH.EmployeeID
            AND DH.EndDate IS NULL
    inner join HumanResources.Department D on D.DepartmentID = DH.DepartmentID
WHERE
    D.GroupName = ''Manufacturing''    AND
    E.CurrentFlag = 1
',
@stmt OUTPUT,            --try selecting these 2 variables to see how they get parameterized
@params OUTPUT

/*    Step 2: Force parameterization of any query that matches this form.  So, any time this 
    sql statement is executed, regardless of the literal values used, it will be parameterized.  
    Therefore all iterations of the query will look the same to the optimizer. This is critical to
    using plan guides.  */
    
EXEC sp_create_plan_guide N'MyTemplateGuide', 
@stmt, 
N'TEMPLATE',             
NULL, 
@params, 
N'OPTION(PARAMETERIZATION FORCED)'

/*    Step 3: Now that we've parameterized the query, we can apply a plan guide to it.  In this
    instance, we're giving it a hint to use a MERGE JOIN */

EXEC sp_create_plan_guide N'MyPlanGuide', 
@stmt, 
N'SQL', 
NULL, 
@params, 
N'OPTION(MERGE JOIN)'

Pretty straightforward, right?  And please note that if your query is already using parameters, you don’t need to do steps 1 and 2.  Now, let’s try that original sql again.  Notice the original code is now executing as though it has the MERGE JOIN hint:

You can see what plan guides are in your current database by querying sys.plan_guides.

And you can disable/enable a particular plan guide using sp_control_plan_guide.

And that’s it.  You’re a hero.

Also recommended: