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 ;
Unused indexes in a database
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
Thank you very much..