On occasion you might find a query that doesn’t perform consistently or just not at all.  When you don’t have consistent query performance you might consider forcing a plan with query store.  Similarly, if you’re trying to influence a plan that doesn’t behave the way you think it should you can apply a plan guide.  The only catch with these options is they rely on the query being parameterized.  When I say “parameterized”, I mean a query that looks like this:

SELECT * FROM CustomerAccount WHERE AccountNbr=@AcctNbr

This is what I would consider to be a hardcoded/non parameterized query:

SELECT * FROM CustomerAccount WHERE AccountNbr='3'

Of course I understand why the latter is not a good practice; however, in many cases things like this find their way into production code and are hard to address without a major code release or worse yet, getting a vendor to modify their code.

If the query is not parameterized, every set of hardcoded parameters passed into it will cause a different query id to be generated.  In the case of creating plan guides, every set of hardcoded parameters will cause a different sql and plan handle to be generated.  In both these cases this generates a moving target.

If a query is not parameterized, you can force parameterization on it via the use of a Template Plan guide.  A Template Plan guide will cause a non parameterized query to be converted to parameterized SQL when it is parsed.  This does not require any code changes.  I previously wrote a post detailing this process, so I won’t go too much further into that step in this post.

For this example, I used the following to create the template plan guide which forces parameterization:

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
N'SELECT * FROM CustomerAccount WHERE AccountNbr=''3''', @stmt OUTPUT,
@params OUTPUT
EXEC sp_create_plan_guide N'LookupAccountNbrTemplate',
@stmt,
N'TEMPLATE',
NULL,
@params,
N'OPTION(PARAMETERIZATION FORCED)'

Once the query is parameterized, it should appear as a single query id in query store or a single sql handle in the plan cache.  Keep in mind, the parameterized version of the query will have parameters in the query text.

To see that the template plan guide works, you can either dig around in query store or the plan cache, or you can run the query with “Actual Plan” enabled in SSMS and you will see the query is parameterized:

From here you should be able to force plans and create plan guides like you normally would for parameterized SQL.

Caveats

You can’t create a template on a partially parameterized query.  For example, this will give you an error when you try creating a template:

SELECT * FROM CustomerAcct WHERE AccountNbr=@AcctNbr AND AccountStatus='ACTIVE'