What is the query store?

The query store is a very easy way to track the queries happening in your database, with pre made
charts and filters, queries can be aggregated on cpu, reads, execution count, ….
You can also force query plans, so a query always uses a certain plan. We will be using the force
query plan further down this blog post.

Scenario:

You just migrated to a newer version of SQL Server (SQL 2016 and upwards) and now, a query that
had no issues previously, is now behaving worse than it should.
The most common reason for this are the query optimizer changes added in SQL 2014. Which causes
this issue to occur when migrating from SQL 2012 or even older versions.
Most of the time, the query optimizer should pick the better plan, but in some cases, this does not go
as planned.

The reason(s):

This might be because your query outdated, uses syntax that is already not the best approach
(functions in joins or where columns, implicit conversions , … .)
If that is the case, the best approach would be to rewrite it. Sometimes that is not possible. Reasons
could be: the application does not allow it, it needs to be thoroughly tested first, etc.

If changing the query structure is not possible, the query store might be your solution.
An important thing to note is that, when forcing a plan for a specific query, not only plans in
compatibility mode 130 or 140 can be captured, but also in compatibility 110 or lower.

A extra factor is, that your queries need to be already parameterized, so they use the same query plan for different parameters.

Step 1 Change the compatibility of your database to 130 or 140, and turn the
query store on

 

alter database testdb set compatibility_level = 140;
ALTER DATABASE testdb SET QUERY_STORE = ON;

If you’d like to, you can execute the problematic query first, or directly execute it under a lower
compatibility level.

Step 2 Execute and monitor the query

1) First execute the query that is no longer problematic in the lower compatibility level.
2) Afterwards, find your query in the query store:
To find your query id in the query store, you could execute the following statemetns:

 

SELECT q.query_id, t.query_sql_text, object_name(q.object_id) AS parent_object
FROM sys.query_store_query_text t JOIN sys.query_store_query q
ON t.query_text_id = q.query_text_id
WHERE t.query_sql_text LIKE N'%Enter query here %'

For my query i executed the following statement:

 

SELECT q.query_id, t.query_sql_text, object_name(q.object_id) AS parent_object
FROM sys.query_store_query_text t JOIN sys.query_store_query q
ON t.query_text_id = q.query_text_id
WHERE t.query_sql_text LIKE N'%select top(@p5)%'

Result:

 

 

Go to the query store folder in your database,

and select tracked queries –>  view tracked queries

Enter the query id in the tracking query pane:

 

 

press enter

 

You see, that the one way up top, was when executing the query in compatibility level 140, which had way more reads and cpu time ( > 30 seconds elapsed vs < 1 second).

 

Step 3: Force the query plan that was used under compatibility mode 110 or lower

 

 

press yes.

 

Step 4: set the compatibility level back to 140

alter database testdb set compatibility_level = 140;

End notes

And that’s all there is to it, the better query plan in compatibility mode 110 will be used for future
executions in higher compatibility modes.
There are definitely better solutions than this, but this might be helpful for some of you.
There are ways that plan forcing can fail, for example if the database name changes, plan forcing will
stop working, since query plans use a three part naming convention.

 

Other ways to accomplish this

– Use the following trace flag to mimic pre 2014 query optimizer, but that also requires
changes to the query itself:
OPTION (QUERYTRACEON 9481).
– You could manually force a plan with the USE PLAN query hint.
– Change the databases compatibility level entirely.
– Change the query itself.
– See if statistics / index changes help the query.

Leave a Comment

Your email address will not be published. Required fields are marked *