Query Store is helpful for the relatively obvious scenarios. It’s common to hear Query Store and have your mind go straight to tuning query performance and keeping track of the most resource heavy queries on your environment. Do you know how Query Store can also be useful when upgrading SQL Server?
Old and New
How exactly can we use Query Store with SQL Server upgrades? Let’s take a closer look at each step of Microsoft’s recommended workflow:
1. Upgrade SQL Server version but do not change the compatibility level. Query Store aside, this is the best way to go anyway so that you can monitor the upgrade for a few days or weeks before flipping the switch on compatibility.
2. Enable Query Store on your databases. Nothing special yet, just make sure Query Store is turned on and gathering data.
3. Hurry up and wait. Let Query Store gather its data for whatever amount of time is right for your environment and you’ll have a baseline for the “old” compatibility level to compare against.
4. Change databases to the latest compatibility level. Now we’re getting some action. We can start to see how plans look in Query Store on the new compatibility level compared to the old.
5. Analyze and address any issues. In a perfect world, plans will be better or at least the same after the switch. Don’t count on that always being the case. If any plans regress or performance suffers, you’ll have a head start on making improvements. If you end up stuck, you still have the option of reverting back to the previous compatibility level to regroup.
Version Notes: These steps are only valid if you’re upgrading to SQL Server 2014 or newer. On SQL Server 2017, Automatic Plan Correction will fix regressed queries for you by forcing the previously known good plan.
Put to Use
Now you have another reason to take advantage of Query Store. If you have a SQL Server upgrade coming up soon, consider the steps above to help you prepare for and handle any query issues that arise from a compatibility switch.
Thanks for reading!