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.

Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement...").

From my understanding every added index can make an SQL SELECT query faster but also an UPDATE or INSERT query slower since the indexes have to be adjusted.

What I wonder is, when do I have "too many" indexes/statistics?

Maybe there is no clear answer on this but some rule-of-thumb.

share|improve this question
    
Related: dba.stackexchange.com/questions/56/… –  Nick Chammas Sep 8 '11 at 14:55
    
Thank you, @Nick. I carefully searched Google and dba.se.com before posting my question. In my opinion, this is rather unrelated. I do not want to know whether an index is required but rather how to determine when too many indexes make UPDATE and INSERT statements too slow. –  Uwe Keim Sep 8 '11 at 15:11
2  
Did you read the answers there? The accepted answer, for example, links to some analysis scripts that can help you identify unused indexes that should be removed. You may find that helpful in answering your question of when you have "too many" indexes. –  Nick Chammas Sep 8 '11 at 15:47
2  
Also see dba.stackexchange.com/q/3831/630 –  gbn Sep 8 '11 at 16:51
add comment

2 Answers

up vote 11 down vote accepted

Your load profile is the most important factor in answering this question.

  • If your load is read-intensive, you want indexes to satisfy your heaviest or most frequent queries.

  • If your load is write-intensive, index carefully. Index to satisfy a seek an UPDATE needs, for example, as well as your one or two most expensive SELECTs.

  • If your load is an OLAP one, index sparingly since you will be scanning the target tables anyway.

How do you know you have too many indexes?

  • When you can see that some of them are not used by any queries.

  • A frequent DELETE, UPDATE or INSERT shows a query plan that involves several expensive index changes (i.e. a nonclustered index insert, update, or delete). Use your judgement to determine if the penalty on those DML statements is worth the gain you are getting from the indexes that have to be updated.

share|improve this answer
add comment

If you have indexes which are being maintained but are never being used (or are rarely being used) then you have to many indexes. If all your indexes are being used to improve user performance then you don't have to many.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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