Find missing indexes

This script will help you to identify all the missing indexes indexes detected by the database engine since the last time the service was started. it provides the equality and inequality columns for the proposed index as well as the included ones.

select

-- mid.database_id,

-- mid.object_id,

mid.equality_columns,

mid.inequality_columns,

mid.included_columns,

mid.statement,

migs.unique_compiles,

migs.user_seeks,

-- migs.user_scans,

migs.last_user_seek,

datediff(mi,migs.last_user_seek,getdate()) as [Min_since_last_U_Seek], --Amount of minutes since the last user seek

--migs.last_user_scan,

round(migs.avg_total_user_cost,2) as avg_total_user_cost,

migs.avg_user_impact,

round(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans),0) as SortOrd --Calculated value to measure the missing index

--select mid.*, migs.*

from sys.dm_db_missing_index_details mid (nolock)

join sys.dm_db_missing_index_groups mig(nolock)

on mid.index_handle = mig.index_handle

join sys.dm_db_missing_index_group_stats migs(nolock)

on mig.index_group_handle = migs.group_handle

--where mid.statement like '%My_Database%'

order by migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

--migs.user_seeks desc,  mid.statement

go