How the optimiser decides.
WHAT STATISTICS ARE
Estimates about data distribution, used to choose a plan.
WHAT THEY INFLUENCE
Whether an index is used Which table drives a join Whether to scan or seek
WHY STALE STATISTICS CAUSE PROBLEMS
The optimiser makes reasonable decisions from wrong information.
WHAT THE SYMPTOM IS
A query that was fast becoming slow with no code change.
WHEN THEY BECOME STALE
After large inserts or deletes After bulk imports After schema changes
HOW TO UPDATE THEM
The analyse command on the table.
WHAT IT COSTS
Little, usually, though it can briefly lock.
WHEN TO RUN IT
After bulk changes, and periodically on volatile tables.
WHAT PERSISTENT STATISTICS PROVIDE
Stability across restarts.
WHAT SAMPLING AFFECTS
Accuracy on large tables.
WHAT TO INCREASE WHERE PLANS ARE WRONG
The sample size.
WHAT ELSE CAUSES BAD PLANS
Skewed data, where a value is far more common than others Parameters differing wildly between executions
WHAT TO DO ABOUT PERSISTENTLY WRONG PLANS
Consider index hints, reluctantly.
WHY RELUCTANTLY
They become wrong as data changes, and are forgotten.
WHAT TO PREFER
Fixing the index or the query.
