SQL Server DMV - sys.dm_db_missing_index_group_stats - What do these columns mean?

Question

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.

Answer 1

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.