Can I Force A Hinted Plan with Query Store in SQL Server 2016?

SQL Server 2016’s Query Store feature promises to be better than Plan Guides ever were. The Query Store lets  you track query performance, collect execution plans, and force a specific plan if you notice that a query is sometimes fast, and sometimes slow.

But what if we want to hint a query? Can Query Store force a hinted plan?

We’ve got a slow query that runs for 35 seconds. We can’t change the code for the query quickly– that’s going to take months.

With manual testing, we discover that if we add a couple of trace flags with an OPTION QUERYTRACEON hint, we can drop the runtime of the query down to 17 seconds.  Alternately, we discover that if we add an index hint to the query to tell it not to use a nonclustered index, we get a parallel plan that’s 17 seconds.

Can we get these plans into cache, and then force the query to use one of them?

Let’s Test It!

I ran three queries with Query Store enabled, and it saw all three o f my queries:

• Query 3 / Plan 3  - The slow 35 second query
• Query 8 / Plan 8 - The same TSQL with an OPTION QUERYTRACEON query hint
• The same TSQL with an index hint added (this is a table hint, technically a little different from a query hint)

Using the GUI, my only option is to force Plan 3 for Query 3. This is not what I want. I want to force Plan 8 or Plan 9 for Query 3!

But can I do it in TSQL?

How about using  sp_query_store_force_plan?

Query store runs an extended stored procedure behind the scenes that accepts really simple parameters - sp_query_store_force_plan.

I already know my query id’s and plan id’s, so this should be really simple.

Here’s how that went:

[

Msg 12406, Level 11, State 1, Procedure sp_query_store_force_plan Says, “That Is Not My Plan”

Adding either the table hint or the query hint did the same thing – it changed what Query Store understands as the query entity.

You cannot pin a plan for a different query using this tool. And adding a hint automatically makes a new query entity.

This is Why ‘ALTER PROCEDURE’ is listed as a Best Practice for Query Store

In the Best Practices article for Query Store, waaay down the page, they mention this:

Query Store associates query entry with a containing object (stored procedure, function, and trigger). When you recreate a containing object, a new query entry will be generated for the same query text. This will prevent you from tracking performance statistics for that query over time and use plan forcing mechanism.

In other words, they are encouraging this code pattern:

IF OBJECT_ID('dbo.NameCountByGender','P') IS  NULL
    EXEC ('CREATE PROCEDURE dbo.NameCountByGender AS RETURN 0')
GO
ALTER PROCEDURE dbo.NameCountByGender
    @firstname VARCHAR(256) 
AS   
/* etc */

This is also why the best practices article encourages using parameterized queries or the forced parameterization database setting.

TakeAway: Query Store Doesn’t Completely Replace Plan Guides

It pains me to write these words, but at this point Query Store doesn’t kill off the old, frustrating Plan Guide feature. Plan Guides do let you add query and (some) table hints to queries, even if you can’t change the code for whatever reason. That is, if you can get the Plan Guide to work, they are frustrating little beasts.

Query Store only replaces one of the three types of plan guide - the type of Plan Guide that freezes execution plans.

Query Store is Still Worth Testing

This new feature is still extremely exciting. The plan forcing aspect to Query Store is interesting, but personally I find the tracking features, and the ability to restore Query Store information with a database to be incredibly interesting in terms of improving the performance tuning process. As I see it, the beauty of the feature isn’t really about pinning plans, it’s more about empowering developers who don’t have direct access to production.