Possible Duplicate:
Where can I find some guidance on index strategies?

I remember taking a course on SQL Server 2000, and the instructor said that you have to be careful of adding too many indexes to a table, because this can lead to performance problems with querying from the table. With the release of SQL Server 2012, are these kinds of problems still a concern?


2 Answers 2


Yes, of course. Every index you create requires maintenance (it needs to be updated for every insert, update and delete operation) and occupies space. Indexes aren't free performance boosts, and should certainly be considered as a trade-off. You should also be very careful about throwing an index on a table to satisfy one query that is called once a week if that table is written all week long. In other words, don't look at the missing index DMVs or a single execution plan and judge the validity of a suggested index in isolation.


Definitely. Adding an index can cause the following issues:

  • Deadlocks may start occurring where there were none;
  • Previously fast selects may dramatically slow down;
  • Modifications may slow down, using more CPU and more I/O.

This is why whenever we add a new index, we need to have some kind of baseline testing to verify that none of these issues happen.

  • 1
    Could you please elaborate a bit on how adding an index could cause "Previously fast selects may dramatically slow down;"? Thanks.
    – NoChance
    Commented Aug 13, 2012 at 21:52
  • @EmmadKareem sure, I can do that.
    – A-K
    Commented Aug 14, 2012 at 14:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.