As mentioned, Query Store was introduced in SQL Server 2016 and it is a great tool for identifying regressed queries, especially after the version upgrade. You can use different reports to search for regressed queries or query the appropriate catalog views. However, in production databases, you could have thousands of queries, and if regressed queries are top-consuming queries, you will need time and patience to identify significantly regressed but less frequently executed queries. It would be nice if Query Store did this and created notifications for you, so that you can easily and quickly see all regressed queries in a database. This feature was not available in SQL Server 2016, but SQL Server 2017 brings it in as part of the new Automatic Tuning feature.

Automatic tuning lets SQL Server automatically detect plan choice regression including the plan that should be used instead of the regressed plan. When it detects it, it can apply the last known good plan. It continues to monitor automatically the performance of the forced plan. If the forced plan is not better than the regressed plan, the new plan will be unforced and the database engine will compile a new plan. If the forced plan is better than the regressed one, the forced plan will be retained until a recompile (for example, on the next statistics or schema change). You can use the Automatic tuning feature by choosing between two modes:

• Offline recommendations: SQL Server detects and stores info about regressed queries that includes instructions on which execution plan to force in order to solve regression problems, but without forcing plans
• Automatic tuning: SQL Server detects regression and forces last good plans automatically

In the next sections, you will see both modes in action.