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.

I often use SSMS to test my slow stored procedures for missing indexes. Whenever I see a "Missing Index (Impact xxx)" my kneejerk reaction is to just create the new index. This results in a faster query every time as far as I can tell.

Any reason why I shouldn't continue to do this?

share|improve this question
    
can you tell me where from i can get this missing index feature. –  David rocky Jun 1 '13 at 4:25
add comment

3 Answers

up vote 22 down vote accepted

Many reasons.

One of the biggest that I can think of is that the missing index DMVs don't take into account existing indexes.

Example:

You have a table with ColA, ColB, ColC.

Currently you have an index on ColA. The missing index DMV will suggest you add an index on (ColA, ColB). This may be correct, but the smart thing to do is to add ColB as a second key on the existing index. Otherwise you have duplicate coverage and wasted space and overhead.

Similarly, if you have an index on ColB INCLUDE (ColA), it may suggest an index on ColB INCLUDE (ColC). Again the smart thing to do is to add ColC to the include list in the existing index.

The suggested indexes have an extremely narrow view - they only look at a single query, or a single operation within a single query. They don't take into account what already exists or your other query patterns.

You still need a thinking human being to analyze the overall indexing strategy and make sure that you index structure is efficient and cohesive.

If there were no issues with just adding all the suggested indexes then there would be no need to even have them be suggested - they would be implemented automatically.

share|improve this answer
    
I see, maybe that's the reason it feels like I have a bazillion indexes. I guess I should try to understand these explain plans better and figure out the indexes manually. –  O.O Jan 19 '12 at 20:27
    
If you google duplicate index scripts or something similar there are a lot of resources for tracking these things down. I manage most of my own indexes and know a good bit about it but I still find dupes from time to time. –  JNK Jan 19 '12 at 20:32
    
"You still need a thinking human being to analyze the overall indexing strategy and make sure that you index structure is efficient and cohesive." +1! As a consultant, I've had all sorts of clients in all sorts of situations. Sometimes I get those clients because they have way too many (and the wrong ones, redundant ones, etc.) indexes - all suggested by database engine tuning advisor. –  Mike Walsh Jan 19 '12 at 20:33
    
@JNK - I'll do that. –  O.O Jan 19 '12 at 20:46
2  
Great point -- overlapping indexes is definitely the biggest thing to watch out for here. And of course the more indexes you have, the slower insertion becomes, plus the hit to maintainability (adding complexity), etc. –  Jeff Atwood Jan 19 '12 at 23:34
show 3 more comments

I recommend cautious use of this tuning technique since I have found missing index suggestions popped up by query plans to be consistently less reliable as queries and DB schemas become progessively more complex. This has been due to a variety of reasons in my experience:

1) The "percent improvement" can be way off for all but the simplest queries/most obvious indexes, after all it is just an estimate and does not derive from the actual costs incurred or actual rowcounts when the query runs. I've seen query costs go up after implementing a suggested index, or it doesn't even get used and the plan remains the same.

2) The query plan itself is not optimal, either due to the construction of the query (joins and where clause not optimized, etc), or the rowcount estimates are off due to missing/out-of-date statistics. Indexing to a brutally bad query plan is often at best a band-aid solution with only an incremental improvement in performance.

3) You might not be seeing the whole picture. This is especially true when using only the graphical plan and not viewing the XML to see if more than one missing index has been suggested. The one shown first in the graphical plan is not necessarily the one with most impact on the query.

4) I've also encountered plenty of examples of new indexes being suggested when modifying the existing index will do. See the other answers here regarding this point, they are spot on, no need for me to elaborate further.

I only use the missing index suggestions as a starting point when working with an unfamiliar query/environment to see where to look deeper. I have gotten better results looking at the operators in the plan (mainly the seeks/scans/joins) and checking the tooltip or properties window to see which columns are involved and using that to determine index candidates to test for improvement.

share|improve this answer
add comment

There is a bunch of reasons, mainly - knowing how indexes works and stored - you'll always create an index better, or at least - not worse, that SSMS suggestions

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.