The Problem: A Good Query Suddenly Gets Slow
Yesterday a report ran in 2 seconds. Today it takes 20 minutes. Nothing in the SQL changed, but the optimizer chose a new plan after statistics were gathered or the database was upgraded. This is called a plan regression.
Find the Plans a Query Has Used
If you see two different plan_hash_value numbers for the same query, with very different times, you have a regression and you know which plan is the good one.
Create a Baseline from the Good Plan
Check the Baselines
| Column | Meaning |
|---|---|
enabled | May be used |
accepted | Verified and allowed to be used |
fixed | Optimizer prefers this plan above others |
origin | How it was created (manual load, auto capture ...) |
Make Sure Baselines Are Used
How the Optimizer Behaves
- Optimizer finds the best plan as usual.
- If it matches an accepted baseline plan, it runs it.
- If it finds a new plan, it records it as not accepted.
- The new plan is used only after it is verified (evolved) and found to be better.
Evolve New Plans
In recent versions an automatic SPM Evolve Advisor task also tests new plans in the background and accepts the ones that are clearly better.
Remove a Baseline
Conclusion
SQL Plan Management protects good plans. Capture the good plan, let the optimizer try new ones and accept them only when they prove better.

