The main use case, and most probably the one that triggered the introduction of this feature, is upgrading to a new SQL Server version. An upgrade is never a trivial action; it brings improvements (that’s the reason why you upgrade, right?) but sometimes also regressions. These regressions are not always predictable and it happens often that companies do not perform a full upgrade to the latest SQL Server version, but instead leave the most important or most volatile databases in the old compatibility mode. This means execution plans will use the logic, rules, and algorithms from the previous database version. By taking this approach, you can reduce the risk of performance regression, but you will not be able to use some of the new features because they are available only in the latest compatibility mode. In my experience, after changes in the Cardinality Estimator in SQL Server 2014, more than 50% of companies did not upgrade all large and volatile databases to compatibility mode 120 because of significant regressions. Most queries perform well, but some of them got different, suboptimal execution plan and fixing them on a production system would be too expensive and risky.

Query Store can help you to perform upgrades without many worries about issues with different plans in the new version. It can easily and quickly identify issues with execution plans and offers you an option to force the old plan in cases of regression, without a big impact on the production workload.

A typical scenario for a SQL Server version upgrade is as follows:

• Upgrade SQL Server to the latest version (SQL Server 2017), but leave all user databases in the old compatibility mode
• Enable and configure Query Store
• Let Query Store work and collect information about your representative workload
• Change queries with forced plan compatibility level to the latest one (140)
• Check recommendations in the sys.dm_db_tuning_recommendations dynamic management view
• Force old plans for regressed queries

Query Store will let you fix problems by choosing the old plan. However, as mentioned earlier, this might not be a final solution and it is good idea to analyze why regression happened and to try to fine-tune the query. Of course, this could be time-and resource-consuming and it is better to do this later, when you have enough time, than when under pressure, at a time when problems occur on the production system.