Geek Answers Handbook

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

I am creating a query to find missing indexes. I took the basic query created by the Red-Gate people in their SQL Server DMV Starter Pack book, and modified it a bit. There sys.dm_db_missing_index_group_statsare a couple of columns in which I do not know how to interpret. It:

avg_total_user_cost
avg_user_impact

According to the documentation, I found that avg_total_user_cost is defined as Represents the average total cost of a user each time a user request has been executed . And, avg_user_impact Represents the value in percent. It shows the amount of improvement you can get if the index is created.

I should add an index that is indicated in my request, which shows an average consumer value of 2.22 and a user influence of 99.82. What do these numbers mean? Does this mean by adding an index, I can improve the speed of a related query by 99.82%. I do not know what 2.22 can mean.

Thank.

+5
source share
2 answers

My interpretation was as follows:

+9

@Joe , , , :

, , :

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

, :

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

improvement_measure , , dm_db_missing_index_group_stats - .

+3

More articles:


All Articles