Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

marked as duplicate by gbn, jcolebrand Aug 23 '12 at 14:02

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

add comment

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
1  
Could you please elaborate a bit on how adding an index could cause "Previously fast selects may dramatically slow down;"? Thanks. –  Emmad Kareem Aug 13 '12 at 21:52
    
@EmmadKareem sure, I can do that. –  AlexKuznetsov Aug 14 '12 at 14:04
add comment

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