DMV Missing Index Report

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.

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.