Duplicate and unused indexes tax every write
Indexes that never seek still slow inserts and updates. How reviews identify write tax without recklessly dropping production indexes.
Missing indexes get attention. Extra indexes quietly tax every write and bloat maintenance windows.
What we find
- Near-duplicate indexes (same keys, different includes)
- Indexes unused for long sample windows
- Wide indexes built “just in case” after one incident
- Heaps with many nonclustered indexes compensating for design
Caution before dropping
Usage stats reset on restart. A monthly report might only need an index once a month. Dropping blindly is how you create a new incident.
Sensible process
- Inventory overlapping definitions
- Correlate with seeks/scans/lookups over a representative period
- Check job and report schedules
- Drop or merge in controlled changes
- Keep a recreate script ready
Sales-honest framing
A free health check highlights obvious duplication and maintenance cost. Safe cleanup is a short engagement with change control — exactly the kind of work retainers are for.
Need hands-on SQL Server help?
Allay Data Solutions offers maintenance, tuning, and assessments from Port Elizabeth, South Africa.