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.
These three DMVs can be abbreviated as mid (or id), mig (or ig), and stats (or igs), respectively. You use the three DMVs as a group. The output, which will vary on your computer, will list each database, schema, and table that is missing an index in descending order of the overall improvement expected by adding an index.
SELECT (user_seeks + user_scans) * avg_total_user_cost * (avg_user_impact * 0.01) AS Index Improvement, 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 Index Improvement DESC;
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.
It worth noting that (user_seeks + user_scans) * avg_total_user_cost * (avg_user_impact * 0.01) is a combination, which can also be written as (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans). This combination is name AS Index Improvement, in the following example, it is name AS Impact:
