SELECT migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure, 'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']' + ' 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 mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
You’ll find queries like it all over the internet but not necessarily an explanation of what it’s telling you. The SQL Server DMVs are based on the same concepts used in query plans and query optimization.
sys.dm_db_Missing_Index_Group_Stats – Updated By Every Query Execution
- Avg_Total_User_Cost – A number representing the cost of queries for which the index could have been used
- Avg_User_Impact – Percentage by which the average query cost would drop if index was implemented
- User_Seeks – Number of seeks caused by queries for which this index could have been used
- User_Scans – Number of scans caused by queries for which this index could have been used
sys.dm_db_Missing_Index_Details – Updated Every Time Query is Optimized by the Query Optimizer
- Statement – Table where the index is missing
- Equality_Columns – Columns used in equality predicates (Column=’a’)
- Inequality_Columns – Columns used in a predicate that’s anything except equality such as >
- Included_Columns – Columns need to cover the query
- Database_ID – Database
- Object_ID – Table
The higher the improvement_measure the greater the possibility for improvement. As always with indexes make sure you look at all of the pros and cons for the index.