Even though automatic tuning wasn’t enabled, SQL Server picked up on the performance changes. I got a recommendation in sys.dm_db_tuning_recommendations.
- reason: Average query CPU time changed from 2127.84ms to 66291.9ms
- state: {“currentValue”:”Active”,”reason”:”AutomaticTuningOptionNotEnabled”}
The details also include the query id in question, and the plan_id of the “fast plan”.
It’s nice to check those out for a couple of weeks before turning automatic tuning on; that way, you can get more comfortable with the types of changes the tuning engine recommends, and if you happen to have a system which is terrible for automatic tuning, you can know that before turning the feature on.