Uncover Missing Indexes with DMV

Dynamic Management Views are a great way to peak under the covers of SQL Server to see what’s going on without having to run a full profiler trace and parse that through SQL tuning advisor. While the following is also available as a Performance Dashboard report, sometimes it’s handy to have the actual DMV query to find a databases’ missing indexes.

It should be noted that DMVs will only report information collected since the last time the SQL Server service is restarted – so if you’ve just patched and rebooted the server then you may not find a great deal of information available. The below query will output a list of proposed index inclusions for each instances where a full table scan needed to be performed which could have been avoided if that index were in place.

The output of this DMV is also not intended to be blindly executed as over-indexing of a table can also introduce additional overheads and lead to negative performance, so any proposed recommendations should always be reviewed by the database developers before being added in.

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

A few additional points of note in using this DMV;

• While very useful for generating a quick report on missing indexes, SQL DMVs are not a replacement for running a proper profiler trace and using Database Engine Tuning Advisor to make recommendations.