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.

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature

If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before. Up until now, you had...

Creating maps with R and Power BI

The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you have creativity and knowledge about the right programming language, you can let...

Sending monitoring alerts through Telegram

What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the app Telegram. Some of you...

Send mails with Azure Elastic Database Jobs

The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want a similar functionality in Azure SQL Database? There are options,...

Sorting matrices in Power BI

Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour view data into a report...

The world of data is evolving

The data landscape has changed dramatically over recent years. In the past, we mainly heard that we needed to do as much as possible “cloud-only”—but this trend has become more...