After a routine statistics update, a critical report query began choosing a plan that runs several times slower. The team has no application change ready to deploy and wants Azure SQL to revert automatically to the plan that performed well. Which option should the DBA enable?
Show answer and explanation
Answer: B
FORCE_LAST_GOOD_PLAN is an automatic tuning option that detects queries whose plan has regressed and forces the last known good plan captured in Query Store. The service monitors the regression and applies the change without a code deployment. DROP_INDEX addresses unused indexes and does nothing for a plan regression. Query Store read-only mode stops the store from capturing new runtime data, and Resource Governor limits resource use without choosing a better plan.
Why the other options are wrong
- A. DROP_INDEX targets indexes that the workload no longer uses and removes them after verification. It does not address a query that picks a worse plan after statistics change.
- C. Query Store read-only mode stops the store from capturing new runtime data. The optimizer would still choose plans, so a regressed plan would continue to be used.
- D. Resource Governor limits CPU, memory and I/O for workload groups. It can slow a runaway query but cannot instruct the optimizer to use a better plan.