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.
- 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.
- Leave the compatibility level on the database(s) in question at the old version, whatever that was.
- Enable Query Store
- Run the load on your machine. A day, a week, a month, you figure that out for your system.
- Switch the compatibility level on the database(s) to the new version.
- 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.
- 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.
- 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.
Thanks grant for another great article. I have a question.I have many DBs on SQL2016 with DB compat level 130 and use legacy cardinality estimator turned ON (bcoz we saw many queries regressed and support recommended to use oldCE) and we are now planning to migrated to SQL2019.Can we proceed with the below apporach. Migrate to SQL2019 with compat 130 and legacy CE ON+ query store ON, monitor workload for one week, change to compat 150, legacy cardinality still ON and check query store for query regression?. I want to take advantage of SQL 2019 tempdb enhancements and Intelligent query processing features. can we follow the above approach for migration.Requesting your inputs pls///
Hey Kevin. Yeah, you could do that. Make one change at a time while capturing the behavior. You can force plans where it’ll work for you.
One additional point, you can even turn off the data collection of Query Store while leaving it enabled. This reduces overhead, but makes it possible to still do plan forcing. This isn’t any kind of requirement. Just another option.
Grant. You may have saved me asking this on Twitter #sqlhelp.
We went from SQL2012 to SQL2019 in December keeping the compat at 2012. We just switched to 2016 compat but had to put legacy CE on some databases because they have nested views. We intend to sit at 2016 for sometime.
In your opinion should we look at fixing the nested views (thinking of changing the underlying queries and the nesting to achive this) of just moving up to 2019 compat and keeping legacy CE on?
Thanks
Hey Chris,
We are on 2016. When we upgraded from 2008 (to 2012, 2014, 2016), we had all kinds of performance issues. After much analysis and rewrites of views/functions/stored procedures, our DBA ran an analysis that listed tables needing clustered indexes. Adding clustered indexes on the column that is used to pull data (usually PK, but not always) resolved our performance issues.
Peg,
Yeah, that’s really common. Most tables (and I mean 99%+) should have a clustered index. SQL Server is optimized around that storage mechanism, so picking the right clustered index is a huge deal.
I know that it’s the less popular answer, but yeah, fix those nested views. Query Store & Plan Forcing can give you breathing room on the problem queries while you get that work done. However, yeah, I’d do that work. Keeping the legacy behaviors can affect how much new functionality you can implement. And 2016, 2017 & 2019 all have functionality that you’re going to want to take advantage of.
Had an idea that would be your thought. Thanks for the confirmation
Thanks Grant! This is going straight into the bookmarks and getting sent to the team; I can’t even begin to state how important this is going to be for the company when we start rolling out our upgrade to get off 2012 this year. Really appreciate it! 🙂
Cheers! Happy I could help out.