MS won't tell you these things.
- The DMVs will not suggest changes to clustering. If you don't know your data this can lead you to a false sense of security. Perhaps the data access patterns are skewed such that a change to the clustered index key would be more beneficial than other covering, non-clustered indexes.
- It does not make suggestions to modify existing indexes because it does not know why you created them. However, if it recommends a 3 col composite index where the first 2 cols are the leading index key for an indexing non-unique index, it won't recommend modifying the existing index. It should at least provide a notation that says, "Hey, there is already an index that almost has what we are recommending, you might consider modifying that index instead of creating this new, recommended index."
- It does not consider any partitioning schemes that you may already have in place or might consider. I got burned on this. When you run sp_help against a table or sp_helpindex directly you aren't told what partitions a given index resides on. At least, not directly. The output instead leads you to believe the index is merely on a separate filegroup. So, when I looked at the missing index DMVs it offered a suggestion that seemed reasonable. I doublechecked existing indexes using sp_help and still I thought everything looked ok...until I remembered the partitioning we had in effect, which negated any benefits the recommended index would've given us. In fact, the recommended index would've made things worse.
- It does not make suggestions on filegroup placement for recommended indexes, nor does it take current filegroup placement into consideration when it makes its suggestions.
- The same holds true for disk and LUN considerations for indexes.
- It does not suggest filtered indexes.
- It does not consider index intersection (see here and here for a definition if you've never heard of this before). For instance, rather than implement a suggested covered index you might want to consider adding a new index that contains only the missing cols and utilize an existing index for the remainder of the cols. A hash table is built over the two indexes with the net effect of a covered index without the overhead. Granted, index intersection is rarely better than a covered index, but I have seen cases where it is. Again, know your data.
- They won't show you the calls that the proposed index would've helped with, so it's hard to test. You would need to look at the plan cache and determine this.