Find missing Index with DMVs in SQL Server 2005/2008/Denali

In the previous articles I saw how to find unused index so that you can find it and drop it to improve performance of your Insert /Update /Delete statement and claim some disk space which is really useful in production server.

Today I will be exploring the script to find the missing index, after finding that index; you can decide whether to create it or not based on the requirement of your application. 

There are many important DMVs (Dynamic Management View) there in SQL Server 2005 and higher version which are keeping information you need to know to find missing index. Following is the list of those DMVs.

“sys.dm_db_missing_index_details”:  This DMV returns details about missing index you need to create. For more information on this, please click here.

“sys.dm_db_missing_index_group_stats”: This DMV returns the summary of benefit you would have received if you would have the particular index. For more information on this, please click here.

“sys.dm_db_missing_index_groups”: This DMV returns information about what missing index are contained in what missing index group handle. For more information on this, please click here.

“sys.dm_db_missing_index_columns(Index_Handle)”:  This DMV gives you an idea about what columns are missing in Index, it is based on the “Index_Handle” field of “Sys.dm_db_missing_index_details” DMV. For more information on this, please click here.

Let us run all these four DMVs to see what it has for us:

select * from sys.dm_db_missing_index_details

select * from sys.dm_db_missing_index_group_stats

select * from sys.dm_db_missing_index_groups

--43816 is one of the I have copided from my "Index_Handle" column of

--sys.dm_db_missing_index_details DMV, you might get something else than 43816

select * from sys.dm_db_missing_index_columns(43816)

So these are the DMVs which will be useful in order to find missing index, we are going to use first three of the above DMVs to find our missing index.

Here you go!!!!

SELECT

      avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) AS PossibleImprovement

      ,last_user_seek

      ,last_user_scan

      ,statement AS Object

      ,'CREATE INDEX [IDX_' + CONVERT(VARCHAR,GS.Group_Handle) + '_' + CONVERT(VARCHAR,D.Index_Handle) + '_'

      + REPLACE(REPLACE(REPLACE([statement],']',''),'[',''),'.','') + ']'

      +' ON '

      + [statement]

      + ' (' + ISNULL (equality_columns,'')

    + CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN ',' ELSE '' END

    + ISNULL (inequality_columns, '')

    + ')'

    + ISNULL (' INCLUDE (' + included_columns + ')', '')

      AS Create_Index_Syntax

FROM

      sys.dm_db_missing_index_groups AS G

INNER JOIN

      sys.dm_db_missing_index_group_stats AS GS

ON

      GS.group_handle = G.index_group_handle

INNER JOIN

      sys.dm_db_missing_index_details AS D

ON

      G.index_handle = D.index_handle

Order By PossibleImprovement DESC

This is just a basic advice from DMVs regarding what indexes are missing and you have to create it, finally it’s up to you based on your requirement whether to create index or not. You have to see the table name and column whether it has any selectivity or not then decide whether to create that or not as more index on table might improve performance of your SELECT but it will harm other DML statements so it is always advisable to use your human skills to decide rather than leave everything on DMVs.  

These DMVs could keep information for maximum of 500 indexes.

Enjoy Indexing!!!!

If you want to refer all other articles related to index, click here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”