How Query Store And Plan Guides Interact

Grant Fritchey shows that query metadata gets a little weird when you have a plan guide trying to use one particular query and Query Store is forcing a different plan:

If we rerun the query and then take a look at the first operator in the execution plan, we can see that the Plan Guide is in use… and that the query hash has changed. It no longer matches the original query. Now it matches the query that included the query hint. This actually makes perfect sense. The Plan Guide is basically changing the query from the first example above, into the second.

Now, what happens when we toss in the Query Store

The query behavior is exactly what you want, but some of the metadata is no longer correct.

Related Posts

Alerting In Azure SQL Database

Arun Sirpal shows how to set up an alert for an Azure SQL Database: I keep things simple and like to look at certain performance based metrics but before talking about what metrics are available let’s step through an example. For this post I want to setup an alert for CPU percentage utilised that when […]

Read More

Query Store Plan Forcing: You Can’t Always Get What You Want

Kendra Little shows an example where trying to force a Query Store plan results in an oddity: This is not considered a “failure” When I check the Query Store DMVs, force_failure_count is 0. The last_force_failure_reason_desc is NONE. Query Store didn’t fail to apply the narrow plan. Instead, it’s just deciding not to give it to […]

Read More

Categories

April 2017
MTWTFSS
« Mar May »
 12
3456789
10111213141516
17181920212223
24252627282930