patternsqlMinor
Does automatic tuning revise the plan decision?
Viewed 0 times
thetuningplandecisiondoesreviseautomatic
Problem
When automatic tuning is enabled, using SET AUTOMATIC_TUNING(FORCE_LAST_GOOD_PLAN), then based on query regression suppose SQL server auto decides to force a plan, then when does it revisit it's decision and reassess whether or not there is a better plan that can execute the query faster/with less resources?
Solution
Sort of.
Details about automatic tuning are shown in this DMV:
sys.dm_db_tuning_recommendations
When there is a change that would affect the forced plan, it's propagated into the state column.
Documentation provides the full list, but examples are:
SchemaChanged
Recommendation expired because the schema of a referenced table is changed. New recommendation will be created if a new query plan regression is detected on the new schema.
StatisticsChanged
Recommendation expired due to the statistic change on a referenced table. New recommendation will be created if a new query plan regression is detected based on new statistics.
Details about automatic tuning are shown in this DMV:
sys.dm_db_tuning_recommendations
When there is a change that would affect the forced plan, it's propagated into the state column.
Documentation provides the full list, but examples are:
SchemaChanged
Recommendation expired because the schema of a referenced table is changed. New recommendation will be created if a new query plan regression is detected on the new schema.
StatisticsChanged
Recommendation expired due to the statistic change on a referenced table. New recommendation will be created if a new query plan regression is detected based on new statistics.
Context
StackExchange Database Administrators Q#335855, answer score: 6
Revisions (0)
No revisions yet.