I am tinkering with creating a query to find missing indexes. I've taken a base query created by the Red-Gate folks in their SQL Server DMV Starter Pack eBook and am modifying it a bit. There are a couple columns in sys.dm_db_missing_index_group_stats which I don't know how to interpret. They are:
avg_total_user_cost
avg_user_impact
According to documentation I found avg_total_user_cost is defined as Represents the average total user cost each time when the user query was executed. And, avg_user_impact Represents the value as a percentage. It shows the amount of improvement which you can get if the index is created.
An index my query says should be added shows a 2.22 average user cost and a 99.82 user impact. What do these numbers really mean? Does this mean by adding an index, I can improve the speed of the associated query by 99.82%. I have no clue what 2.22 might mean.
Thanks.
My interpretation of these has been that:
avg_total_user_cost is the current
average of all queries that could
potentially benefit from the creation
of the missing index group. The "cost" is a
unitless value calculated by the
optimizer. See: SQL SERVER – Execution Plan – Estimated I/O Cost – Estimated CPU Cost – No Unit
avg_user_impact is a percentage
representing the average decrease in cost of all queries
if the missing index group was created. The
higher the percentage, the greater
the benefit of the new index will be.