SQL Server Performance

avg_user_impact values

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by hollow, Jul 28, 2009.

  1. hollow New Member

    In the DMV sys.dm_db_missing_index_group_stats, there is a field named avg_user_impact. Per BOL, this shows the

    Average percentage benefit that user queries could experience if this missing index group was implemented. The value means that the query cost would on average drop by this percentage if this missing index group was implemented.

    When I run a query to find the missing indexes that would have the greatest impact on performance, I see a few that claim the avg_user_impact will be in the thousands.

    I built an index using one of these recommendations, and it was never used according to the DMV sys.dm_db_index_usage_stats. As soon as I disabled the index, it immediately showed up again as a missing index.

    Is it possible that once the avg_user_impact field shows over 100, it's in error? Or is this an example of how a recommended index must still be tested for usefulness? What am I doing wrong or misunderstanding?

  2. sqlfrenzy New Member

    You should also consider the user_seeks and user_scans, One of the reason of the index not being used is the queries for which it was implemented were not executed; the index appeared again in missing index because it was not used..as the result of index_usage_stats show. I agree with the %age thing u said, b'coz avg_user_impact is a %age it should not go beyond 100%; as 100% means that query cost will be near to 0.

Share This Page