How to find out beneficial missing indexes for a database in SQL Server?


you can find out beneficial missing index in a database through below Script.

It is very important to know that what indexes should be created on a table in a database.

 


 

—Below Script help you to find beneficial missing indexes for a database

SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )
AS [index_advantage] ,
dbmigs.last_user_seek ,
dbmid.[statement] AS [Database.Schema.Table] ,
dbmid.equality_columns ,
dbmid.inequality_columns ,
dbmid.included_columns ,
dbmigs.unique_compiles ,
dbmigs.user_seeks ,
dbmigs.avg_total_user_cost ,
dbmigs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK )
INNER JOIN sys.dm_db_missing_index_groups AS dbmig WITH ( NOLOCK )
ON dbmigs.group_handle = dbmig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS dbmid WITH ( NOLOCK )
ON dbmig.index_handle = dbmid.index_handle
WHERE dbmid.[database_id] = DB_ID()ORDER BY index_advantage DESC ;

 


 

3 comments

  1. Wow, this was usefull. Keep writing this kind of texts, you will get a lot of people to this post if you continue doing this. I will be visiting this website more often. thumbs up

Leave a Reply

Your email address will not be published. Required fields are marked *