Queries are running slower after migrating?

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.


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;

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)%'

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.

Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...
How to easily parse JSON in Power BI Desktop
Sometimes you want to use JSON data in Power BI. And sometimes Power BI doesn’t exactly do what you want...
Power BI reports as monitoring tool
Who has not created monitoring reports or beautiful dashboards in Power BI? Would it not be great to show off...
Your Modernization Journey starts now
Recently Microsoft introduced SQL server 2022. We wrote this post to make sure you won’t be missing out on all...