You’re planning to migrate to SQL Server 2022, and you want your databases to be faster after the migration.

This is NOT a blog post about how to migrate – that’s the same as it’s been for a long time. Go build the new 2022 servers, and use log shipping or database mirroring to sync the old and new servers. (I’m not a fan of using Distributed Availability Groups to do version upgrades. You can, but it is a heck of a lot of moving parts to set up for a one-time migration.)

This is a blog post about what to do after you migrate:

1. Turn on Query Store, but make no other changes to your databases (including compatibility levels)
2. Gather a 1-2 week baseline of performance metrics and query plans
3. Switch some (but not all) databases to SQL Server 2022 compatibility level
4. If users are unhappy with performance, use Query Store to identify plans that got worse
5. Get relief for that query quickly, possibly with 2022’s new ways to fix performance without changing the query

Let’s go into details on each one of those.

Stage 1: Turn on Query Store.

This new-in-2016 feature is like a black box recorder for your query plans. It has a lot of weaknesses, but it’s still better than your game plan that did not include Query Store.

The goal here is to start capturing query plans as they are today, before all hell breaks loose. Compatibility levels change execution plans, and SQL Server 2022 has a lot of ways that it might change your plans. If performance gets worse, we’re going to want to see what the query plan looked like before our change.

If you’re migrating from SQL Server 2014 or earlier, you don’t have access to Query Store yet. That’s okay: you can still do the migration, but just make sure that Query Store is the first thing you turn on after you go live on 2022. (You might have heard that Query Store is on by default in 2022 – that’s misleading. It’s only on for new databases that you create from scratch – it’s not on for existing databases you migrate into 2022.)

If you’re migrating from SQL Server 2016, 2017, or 2019, turn on Query Store sooner rather than later – long before you actually do the migration project. This way, you’ve got the historical data when you’re actually running on the current version, before the database is moved to 2022.

To learn how to configure Query Store, watch this video from Erin Stellato:

In either case, once you’ve moved to 2022, don’t make any other changes to your databases at first – especially not compatibility level. SQL Server 2022 has options for compatibility level going all the way back to SQL Server 2008, so you should be able to forklift your databases over as-is, and just leave them there for a week or two.

Stage 2: After going live on 2022, gather a baseline.

We want to get a crisp, clear picture of how the old compatibility level is working out for us on SQL Server 2022.

Whenever you change anything about an application – whether it’s a code deployment, new indexes, or a SQL Server version change – people are going to always say, “Hey, things are slower than they used to be.” They’re probably lying. They’re just taking advantage of the opportunity to pin the blame on you, Default Blame Acceptor, and that’s why I want you changing as few things as possible when you do the migration.

Things may actually have gotten worse for their specific query, or for the workload overall. Maybe we messed up a SQL Server configuration, missed a trace flag, or the server hardware isn’t quite what we thought it was. (True story: a recent client’s new server was accidentally provisioned on the slowest possible storage instead of the fastest, so of course their upgrade went poorly.)

Do your normal investigative troubleshooting, and you can even use Erin’s tips in her video on how to use Query Store to track down query plan changes. However, during this stage, do not upgrade the database’s Compatibility Level to fix one query’s performance. Doing so will change the performance of many other queries, some for the better and some for the worse, and you’re not prepared to troubleshoot that right now. If people are complaining about one query, troubleshoot that query. If people are complaining about the whole server, troubleshoot the whole server – but leave compat level where it is for 1-2 weeks.

Stage 3: Change databases to 2022 compat level one at a time.

Compatibility level is a database-level setting. You can see it by right-clicking on a database and going into its options, or by looking at the compatibility_level column in sys.databases.

It’s database-level because it’s possible that some of your databases will perform better on newer compatibility levels – but some of them may not. That means you should take the angriest users, the ones who are the most pissed off about slow performance, and try setting just their databases over to 2022 compat level.

It’s a simple one-line change to change to , as the documentation illustrates:

“Wait – that’s two lines, not one,” you say, pointing your Cheeto-dust-encrusted finger at the screen. Well, the change is one line, but I want you to note the prior compatibility level and the date/time that you changed it – because you might need to roll back. Don’t worry, rolling back is as easy as running that same ALTER DATABASE command, but with the old compat level instead of the new one.

You can make this change whenever you want, without taking the database offline, but there’s a catch: it clears the plan cache for that database. That means you’re temporarily susceptible to parameter sniffing issues as SQL Server suddenly builds new query plans for this database.

Stage 4: Troubleshoot reports of slow performance.

Most of your queries are probably going to go faster after the change. But what’s most? 90%? 99%? 99.9%? Even if just 0.1% of your queries slow down, that’s still a heck of a lot of queries to suddenly have to troubleshoot – especially when users often don’t even know which query they’re talking about. They’ll put in frantic help desk tickets that say things like “The customer screen is slow!!1!” and “The import process is down!!one!” and “My keyboard is filled with Cheetos dust!”

If tons of reports of slowness come in quickly, don’t be afraid to change the compatibility level back to the prior one. It’s a safe, quick, easy way to make the screaming stop. And… just stop there. If users are happy enough on the old compatibility level, leave it there.

However, if the slow query complaints come in at a rate that you can handle, and you’re seeing performance improvements that you wanna keep in other areas of the app, then it’s time to roll up your sleeves and do troubleshooting on the slow queries.

One of the easiest ways is to ask Query Store, “What query plans have gotten worse?” In SSMS, go into the database, Query Store, and then run the Regressed Queries report.

As soon as the report opens, you’re going to need to change the configuration, because the defaults are wrong. At the top right of the report, click Configure. Here’s what the defaults look like:

By default, it’s showing total duration – so queries that ran more often during a time window will show artificially higher on the graph – when the real root cause is that they’re running more often. Me, I like changing “Based On” to “Avg”.

Then, down at the bottom of the window, change “Minimum number of query plans” from 1 to 2. We’re looking for queries whose plan actually changed (perhaps due to the new compat level), not just slowed down. Click OK, and view the report.

The top left window lists the regressed queries, with biggest impact to smallest. As you click on each query, the top right window will update to show the performance of various plans for that query. Remember how I asked you to save the prior compatibility level, and the date/time that you changed it? We’re concerned about queries whose plan changed after the time that you changed the compatibility level. That indicates a query whose performance may have been adversely affected by the new compat level.

Stage 5: Get relief for that slow query.

I’m going to list these from easiest to most time-consuming:

Option A: forcing an older plan. While you’re in Query Store’s Regressed Queries report, one of the easiest ways to get temporarily relief is to click on the query plan you used to get with the prior version of SQL Server, then click the Force Plan button on it.

I said it was easy. I didn’t say it was good.

When you force a plan, that doesn’t mean it’s going to perform well with all possible parameters for that query. This is especially true for queries that keep changing plans for valid reasons, like the example at right. That query’s got 5 different plans, and genuinely needs them. If I force a single plan for it, but my data has outlier parameters, I’m probably gonna get performance complaints from those users. We cover better ways to fix those in my Mastering Parameter Sniffing class, but that’s outside of the scope of this blog post.

Forcing a plan also means if a better option comes along later, like En Vogue’s lovin’, you’re never gonna get it. The whole reason you upgraded compat levels was to get better performance, but that query plan is stuck in the past. You can revisit them later by going into Query Store’s Queries with Forced Plans report – and I’d recommend going back in there every couple/few Cumulative Updates. Microsoft improves query plan behavior over time, so it’s possible that by unforcing a plan later, you’ll get better plan options.

Besides, you’re going to want to go into that Queries with Forced Plans report anyway because query plan forcing can fail, plus the query may change over time. As the query changes, the forced plan will no longer be relevant (because it’s for a query that no longer exists.)

I do still like forcing plans – and I wanna tell you about it because it’s quick and easy – it just has drawbacks, so we need to keep going with more options.

Option 2: give SQL Server hints for the plan. Let’s say that in SQL Server 2022 compat level, SQL Server decided to do something in the query plan that made performance worse instead of better. Let’s say it used batch mode processing on a rowstore index. If you want to disable that behavior, you can add a hint to the query without touching the query itself.

This is called Query Store hints, and David Pless has a tutorial on it. It’s not as easy as forcing a plan in the Query Store GUI. You’re going to have to get the query’s ID from Query Store, then apply the query hint you want using sp_query_store_set_hints.

It does involve work on your part, but … it’s still faster than fixing/tuning the query. As soon as you start changing the query itself, you’re probably dealing with getting approval from different folks in the company, getting it into source control, testing it, getting it deployed, etc. Query Store hints are instant, presto, in production. I can’t imagine how that could go wrong.

Option III: fix/tune the query. This takes the most work, but I’ll be honest: as a consultant, it’s the one I do the most often. Usually when I pop open a query that people are complaining about, I say, “Okay, here’s why it’s not performing well, and if I change this, this, and this about the query, it’ll go dramatically faster than it used to, and it’ll get a better plan overall.”

This is the option that doesn’t just bring performance back to its prior levels – it makes performance better, and after all, isn’t that what we want? (Honestly: no, a lot of people just want it back to the way it was, and that’s why this is the last option.)

SUM(MARY)

When you’re preparing to migrate to SQL Server 2022:

1. Turn on Query Store, but make no other changes to your databases (including compatibility levels)
2. Gather a 1-2 week baseline of performance metrics and query plans
3. Switch some (but not all) databases to SQL Server 2022 compatibility level
4. If users are unhappy with performance, use Query Store to identify plans that got worse
5. Get relief for that query quickly, possibly with 2022’s new ways to fix performance without changing the query

But while you’re in those final two stages, pay particular attention to the bold words. If users aren’t complaining about performance, move on to the servers where they are complaining about performance. Every server has queries that need to be tuned, and you need to focus on the ones that users will actually appreciate.