Index Maintenance and Statistics
What you'll learn: How indexes degrade over time, when to rebuild them, and how query optimizers depend on statistics to make smart decisions.
Why Indexes Need Maintenance
Even well-designed indexes don't stay optimal forever. As data gets inserted, updated, and deleted, indexes can become fragmented—like a filing cabinet where papers are scattered across drawers instead of neatly organized. Pages split, gaps appear, and what was once a tightly-packed structure becomes bloated.
Fragmentation comes in two forms:
- Internal fragmentation: Pages are only partially filled, wasting space
- External fragmentation: Logical order doesn't match physical order on disk, causing inefficient reads
High fragmentation means more disk I/O for the same queries. A query that once read 10 pages might now read 15 scattered pages.
Rebuilding vs Reorganizing
You have two maintenance strategies:
Reorganize (online, lightweight): Compacts pages within existing structures. Use for moderate fragmentation (10-30%). Like tidying a messy desk without replacing furniture.
Rebuild (intensive, may lock tables): Completely recreates the index from scratch. Use for heavy fragmentation (>30%). Like demolishing and rebuilding the filing cabinet.
The Role of Statistics
Indexes aren't useful if the database doesn't know when to use them. Statistics are metadata about your data: how many rows, distribution of values, histogram of common values.
The query optimizer uses statistics to estimate query costs. Outdated statistics lead to bad execution plans—like using a 10-year-old map to navigate a city that's changed dramatically.
Update statistics regularly, especially after large data changes, to ensure the optimizer chooses the right indexes and join strategies.
Key Takeaway: Regularly monitor index fragmentation and rebuild or reorganize as needed; keep statistics current so the query optimizer makes informed decisions about using your indexes effectively.