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.
With Martin Smith's help I found the answer.
On the dialog where you specify which events to include click Show All Columns. Tick the Database name and Id columns. Then redo the trace. It should import into the Tuning Advisor OK.
Best Answer
SQL Server renders a handful of DVMs that can provide you with this information. See the TechNet reference on About the Missing Indexes Feature.
Mainly, you will be dealing with four DMVs:
Note: This is how you do it. But be very careful when considering "missing indexes". You could end up with a large amount of indexes and data manipulation will take a hit from that. This does not replace prudent index consideration/creation.