Menu
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?
875
The missing indexes feature is a lightweight tool for finding missing indexes that might significantly improve query performance. This article describes how to use missing index suggestions to effectively tune indexes and improve query performance.
Equality columns represent the columns in the index that would have been used for an equality operation, such as “=” or “IN”. Inequality columns are those that would have been used to support a query using operations such as “<>”, “>”, or “NOT IN”.
To determine which missing index groups a particular missing index is part of, you can query the sys. dm_db_missing_index_groups dynamic management view by equijoining it with sys. dm_db_missing_index_details based on the index_handle column. The result set for this DMV is limited to 600 rows.
Look, SQL Server 2008 Management Studio intelligence automatically generated the missing index based on your actual execution plan, now you just have to give the name of your index and simply run the statement and your index is generated.
The recommended indexes and user impact are both estimates from the cost optimizer, and, as you see there's no guarantee that they are always correct. I think the user impact column can be over 100 by design (though we'd have to hope a MS developer from the SQL Server team is responding here for the real answer.) It is always vital to test whether creating the indexes shown in the DMV will work or not. I have had the same experience where an index is shown there, but once created doesn't actually get used, perhaps due to the details of the query or the data distribution in the tables, etc. The view is right most of the time, especially for simple cases, but not always.
Also, be careful about creating really wide indexes, as they might hurt insert/update performance more than they help select performance, depending on how busy the server is.
171
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
