Query Store After Migration — How to Detect Execution Plan Regressions
The worst performance issues do not always show up immediately after a migration.
Often, an application appears to work correctly, but individual queries start using worse execution plans. Query Store lets you observe the change rather than guess what happened.
Is Query Store running?
| |
If Query Store has switched to READ_ONLY, find out why before proceeding.
One query_id, multiple plans
| |
If a new plan appeared after migration, compare its statistics with those of the previous plan.
What should you compare?
- Duration.
- CPU time.
- Logical reads.
- Execution count.
- Wait statistics.
- Parameter distribution.
A plan change is not, by itself, a regression. A regression means that query behavior has deteriorated under a representative workload.
Force a plan — with caution
| |
Forcing a plan can quickly mitigate a regression, but it does not replace root-cause analysis.
Takeaway
Query Store is especially valuable when a system is changing.
A composed DBA does not ask only, “Is it slow?” They ask, “What changed in the behavior of this particular query?”