Missing Indexes DMV

This query will show the indexes that are missing ordered according to those having the most impact. It will also provide the create index script needed in order to help you create the index.

SELECT
mid.statement
  ,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,OBJECT_NAME(mid.Object_id),
  '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

What you need to know about this script however is what it does not account for. It does not account for an index that should be clustered. One of the warning signs that an index should be clustered is when this query suggests to you an index that contains a lot of columns (or has a lot of include columns). It is suggesting that, because it does not want to do a bookmark lookup to get the columns it needs for the select list. In those cases, you want to compare the number of user_seeks this query tells you, with the number that is shown in the Index Usage DMV. If this query’s seeks are higher, then consider making this the clustered index.

Also, once the queries are created, you can check to see if your assumption was correct by using the Clustered Index Checker DMV Script.

16 comments
James L 15 Dec 2011 at 8:55 am

Thanks for this…using it right now!

praful 06 Sep 2011 at 1:52 am

my stored proecedure run 4 min off peak time and 30 mins in peak time, i am not able to find the cause for 30 mins duration.Please tell me the various ways to find the bottleneck cause solution.
we has transaction replication setup on the same server.

Derek Dieter 16 Sep 2011 at 1:02 pm

Hi Praful,

What sounds like is happening, is during peak time the data returned for the query is not in memory and needs to be retrieved from disk. It definitely sounds like you are missing indexes or possibly using table variables. You should get the execution plan for the query and see if there are any indexes missing.

Derek

Curt Mader 11 Feb 2011 at 2:10 pm

Why do the these queries that are supposed to show indexes that the system feels are needed often contain indexes that already exist?

Derek Dieter 17 Feb 2011 at 1:50 pm

Hi Curt,

This is because a lot of times, the indexes are suggested in order to avoid a “bookmark lookup”. In other words, the index used to find the fields does exist, however it needs to go back to the clustered index (or heap) in order to get the required columns. The query optimizer does not like this scenario, so it will often suggest new indexes that contain “include” columns. These suggestions can be misleading. The other problem with the suggestions is that it does not take into account the fact that you may need to change your clustered index.

With all that said, the index suggestions are still very useful and something I find myself considering a lot. Much better than the suggestions in SQL 2000..

Curt Mader 20 Feb 2011 at 10:57 am

Thanks Derek!

Joseph Mills 14 Dec 2010 at 11:49 am

Thanks for the script. Makes using the DMVs much faster.

Troubleshooting SQL Server Slowdowns 20 Jun 2009 at 12:43 am

[...] CPU – (Historically Medium/High) For a CPU that has historically been high, the most common issue is a lack of indexes. This is an easy fix with the new DMVs introduced in SQL Server 2005. Usually adding indexes for the top 5 offending queries will resolve this issue. Follow this to Find Missing Indexes. [...]

Featured Articles

 Site Author