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

In the previous articles I saw how to find unused index sothat 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 inproduction server.
Today I will be exploring the script to find the missingindex, after finding that index; you can decide whether to create it or notbased 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 youneed 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 indexyou need to create. For more information on this, please click here.
sys.dm_db_missing_index_group_stats”: This DMV returns thesummary 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 returnsinformation about what missing index are contained in what missing index grouphandle. For more information on this, please click here.
sys.dm_db_missing_index_columns(Index_Handle)”:  This DMV gives you an idea about what columnsare 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 clickhere.
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 oneof the I have copided from my “Index_Handle” column of
–sys.dm_db_missing_index_detailsDMV, 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 missingindex, we are going to use first three of the above DMVs to find our missingindex.
Here you go!!!!
      avg_total_user_cost *avg_user_impact * (user_seeks +user_scans) ASPossibleImprovement
      ,statementAS Object
      ,‘CREATEINDEX [IDX_’ + CONVERT(VARCHAR,GS.Group_Handle) + ‘_’ + CONVERT(VARCHAR,D.Index_Handle) + ‘_’
      + REPLACE(REPLACE(REPLACE([statement],‘]’,),‘[‘,),‘.’,) + ‘]’
      +‘ ON ‘
      + [statement]
      + ‘ (‘+ ISNULL (equality_columns,)
    + CASE WHENequality_columns IS NOTNULL ANDinequality_columns IS NOTNULL THEN ‘,’ ELSE END
    + ISNULL (inequality_columns, )
    + ‘)’
    + ISNULL (‘ INCLUDE (‘ + included_columns + ‘)’, )
      AS Create_Index_Syntax
      sys.dm_db_missing_index_groups ASG
      sys.dm_db_missing_index_group_stats AS GS
      GS.group_handle = G.index_group_handle
      sys.dm_db_missing_index_details ASD
      G.index_handle = D.index_handle
Order By PossibleImprovement DESC
This is just a basic advice from DMVs regarding what indexesare missing and you have to create it, finally it’s up to you based on your requirementwhether to create index or not. You have to see the table name and columnwhether it has any selectivity or not then decide whether to create that or notas more index on table might improve performance of your SELECT but it willharm other DML statements so it is always advisable to use your human skills todecide rather than leave everything on DMVs.  
These DMVs could keep information for maximum of 500indexes.
Enjoy Indexing!!!!
If you want to refer all other articles related to index, clickhere.
Reference: Ritesh Shah
Note: Microsoft Books online is adefault reference of all articles but examples and explanations prepared byRitesh Shah, founder of
Ask me any SQL Server related question at my “ASK Profile

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: