DZone

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.

Source: DZone