Identify missing indexes
When the query optimizer compiles a T-SQL statement, it also tracks up to 500 indexes that could have been used if they had existed. The following DMVs help you review these missing indexes:
sys.dm_db_missing_index_details Use this DMV to identify the columns used for equality and inequality predicates. sys.dm_db_missing_index_groups Use this DMV as an intermediary between sys.dm_db_index_details and sys.dm_db_missing_group_stats. sys.dm_db_missing_index_group_stats Use this DMV to retrieve metrics on a group of missing indexes.
The information in these DMOs is cleared when SQL Server restarts and reflects only information accumulated since the last restart.
You use the three DMVs as a group. The output will list each database, schema, and table that is missing an index in descending order of the overall improvement expected by adding an index. The improvement is derived by multiplying the sum of the seeks and scans that the index helps by the average cost of the user queries that could be reduced by the index and by the average percent decrease in cost resulting from implementing the index.
The equality_columns column lists one or more columns in the table that are good candidates for the new index. The inequality_columns column lists columns that are useful for queries that include the <> operator that you might consider adding to the index. Last, the included_columns column lists the columns that are suggested for addition to the index in the INCLUDE clause.
SELECT (user_seeks + user_scans) * avg_total_user_cost *
(avg_user_impact * 0.01) AS IndexImprovement, id.statement, id.equality_columns, id.inequality_columns, id.included_columns FROM sys.dm_db_missing_index_group_stats AS igs INNER JOIN sys.dm_db_missing_index_groups AS ig ON igs.group_handle = ig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS id ON ig.index_handle = id.index_handle ORDER BY IndexImprovement DESC;
From here, we can see:
group_handle => sys.dm_db_missing_index_group_stats
index_group_handle => sys.dm_db_missing_index_groups
index_handle => sys.dm_db_missing_index_groups
index_handle => sys.dm_db_missing_index_details
Overview
There are several DMOs available to help you manage indexes. Use sys.dm_db_index_usage_stats to review current index usage or, in combination with sys.indexes and sys.objects, to find indexes that are never used. Use sys.dm_db_index_physical_stats to find fragmented indexes. To find missing indexes, use sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups, and sys.dm_db_missing_index_group_stats.
Example:
