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?
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.
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.
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
