Jovan Popovic shows how to find query plan regressions in SQL Server 2017:
In CTP2.0 version is added new system view sys.dm_db_tuning_recommendations that returns recommendations that you can apply to fix potential problems in your database. This view contains all identified potential performance issues in SQL queries that are caused by the SQL plan changes, and the correction scripts that you can apply. Every row in this view contains one recommendation that you can apply to fix the issue. Some of the information that are shown in this view are:
-
Id of the query, plan that caused regression, and the plan that that might be used instead of this plan.
-
Reason that describes what kind of regression is detected (e.g. CPU time for the query is changed from 17ms to 189ms)
-
T-SQL script that can be used to force the plan.
-
Information about the current plan, and previous plan that had better performance.
In the “surgical scalpel to chainsaw” range of query tuning options, this rates approximately guillotine. I think it’ll be a very useful tool for finding issues, but it wouldn’t be wise to start lopping off all the heads just because the optimizer tells you to. In this context, I imagine this DMV to be about as useful as the missing indexes DMV and for the same reasons.