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.
”: This DMV returns details about missing indexyou need to create. For more information on this, please click here
”: 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
”: This DMV returnsinformation about what missing index are contained in what missing index grouphandle. For more information on this, please click here
”: 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
,‘CREATEINDEX [IDX_’ + CONVERT(VARCHAR,GS.Group_Handle) + ‘_’ + CONVERT(VARCHAR,D.Index_Handle) + ‘_’
+ REPLACE(REPLACE(REPLACE([statement],‘]’,”),‘[‘,”),‘.’,”) + ‘]’
+‘ ON ‘
+ ‘ (‘+ ISNULL (equality_columns,”)
+ CASE WHENequality_columns IS NOTNULL ANDinequality_columns IS NOTNULL THEN ‘,’ ELSE ” END
+ ISNULL (inequality_columns, ”)
+ ISNULL (‘ INCLUDE (‘ + included_columns + ‘)’, ”)
sys.dm_db_missing_index_group_stats AS GS
GS.group_handle = G.index_group_handle
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.
If you want to refer all other articles related to index, clickhere
Ask me any SQL Server related question at my “ASK Profile”