There’s a solution for everything. If your queries are running slower after migrating, you can use the Query Store to force lower compatibility plans. 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.

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

• First execute the query that is no longer problematic in the lower compatibility level.
• Afterwards, find your query in the query store:

To find your query id in the query store,  you could execute the following statements:

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:

Step 3: Force the query plan under compatibility level 110 or lower

Click on plan 8, and press force plan.

Press yes.

Step 4: Set the compatibility mode back to 140 / 130

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.