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.

Answer 2

@Joe's answer seems right, I'm just adding some possibly-useful info:

There's an article here that uses this calculation to give an overal impact value:

avg_total_user_cost * avg_user_impact * (migs.user_seeks + migs.user_scans)) 

Here's the query they suggest:

SELECT CONVERT (varchar, getdate(), 126) AS runtime, 
    mig.index_group_handle, mid.index_handle, 
    CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * 
            (migs.user_seeks + migs.user_scans)) AS improvement_measure, 
    'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + 
              CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + ' 
              (' + ISNULL (mid.equality_columns,'') 
              + CASE WHEN mid.equality_columns IS NOT NULL 
                          AND mid.inequality_columns IS NOT NULL 
                     THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '')
              + ')' 
              + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, 
    migs.*, 
    mid.database_id, 
    mid.[object_id]
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs 
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid 
    ON mig.index_handle = mid.index_handle
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

The resulting improvement_measure is unitless, i.e. only useful in a relative sense, but this should help combine the different values from dm_db_missing_index_group_stats into something useful to make decisions from.