Can indexes hurt performance?
Quick Answer
Yes. Every non-clustered index is extra data that must be updated on every INSERT, UPDATE (of an indexed column) and DELETE, so too many indexes slow writes and increase locking, logging and storage. Indexes also fragment over time, a wide or random clustered key causes page splits, and redundant/overlapping indexes waste the buffer pool and can push the optimiser toward a worse plan.
Definitely. An index makes reads faster but it's a second copy of the data that SQL Server has to keep in sync. So on a write-heavy table, every insert and delete — and every update to an indexed column — has to maintain all of those indexes, which means more CPU, more log, more locking and more storage. On top of that, indexes fragment, a bad clustered key causes page splits, and a pile of overlapping indexes wastes memory and gives the optimiser more ways to go wrong. So I aim for the smallest set of indexes that covers the important queries, and I use the index-usage DMVs to drop ones that get written a lot and read never.
Specific ways indexes cost you:
- Write amplification — a single-row insert into a table with 8 indexes is 9 structures to modify, 9 sets of page latches, more transaction-log volume.
- Update cost — updating an indexed column moves the row within that index; updating the clustered key moves it everywhere (the locator changes in every non-clustered index).
- Page splits & fragmentation — inserting in the middle of a full page (random GUID clustered key, out-of-order inserts) splits the page, wasting space and I/O; indexes need periodic
REORGANIZE/REBUILD. - Memory pressure — every index competes for the buffer pool; rarely used indexes evict useful pages.
- Optimiser confusion — many similar indexes give the optimiser more choices and more chances to pick a poor one; stats on all of them must be maintained.
- Lock contention — more structures to lock under concurrent writes.
The goal is the minimum set of indexes that serves the read workload. Use the unused-index DMVs (sys.dm_db_index_usage_stats) to find and drop indexes with many writes and no seeks/scans.
Practise the whole thread the way an interviewer walks it — each question builds on the last.