Query Store as an Upgrade Tool

There are a lot of uses for Query Store, but one of the most interesting is as an upgrade tool. We all know that upgrades in SQL Server can be more than a little bit nerve wracking. No matter how much you tested stuff in lower environments, deploying an update to production might result in performance issues as your code hits a regression. This is even more true when upgrading from versions of SQL Server prior to 2014 to anything 2014 and above. That’s because of the new cardinality estimation engine introduced in 2014. Most queries won’t notice it. Some queries will benefit from the better estimates. A few, problematic, queries will suffer. This is where Query Store can be used as an upgrade tool.

The Steps

We’re going to use two of the functions that Query Store offers us to turn it into a tool for upgrading SQL Server. First, of course, is the ability of Query Store to capture query behaviors. The second is the ability of Query Store to force the use of execution plans. Between these two pieces of functionality, you can have safer, saner, upgrades. Here are the steps to make this work.

1. Upgrade your server, whether in place or side-by-side. Get your databases on to the new server, detach/attach, backup & restore, doesn’t matter.
2. Leave the compatibility level on the database(s) in question at the old version, whatever that was.
3. Enable Query Store
4. Run the load on your machine. A day, a week, a month, you figure that out for your system.
5. Switch the compatibility level on the database(s) to the new version.
6. Query Store has a report that will show regressed queries. Or, your users may alert you to queries that are no longer performing in the same way.
7. Take a look at the query & plan in Query Store. If there’s a plan that was in use prior to the compatibility switch that was running faster than the new plan, force that old plan.
8. Circle back to the query and tune it (it needs it). Do this secure in the knowledge, it’s running the old way while you figure it out.

That’s it. Is this 100% fool proof? Of course not. Any number of issues could arise. You may have such a massive transaction load that Query Store doesn’t work for you out of the box. It could be that the performance differences are reflected in execution plans. There might not be a confirmed plan that works better. All sorts of stuff. However, is this method going to work for most people, most of the time?

Yes.

Conclusion

Any time you can increase the reliability and stability of your production systems, especially across upgrades, I’d sure jump on it. Query Store as an upgrade tool is pretty good. It’s really easy to use. It’s built right in to 2016 and above SQL Server versions. You don’t have to pay for it. It’s right there. Take advantage of it.

I have a whole bunch of posts on how to use Query Store, plus I co-wrote a book on the topic. With all this, you should have enough to get started with Query Store.