Find missing indexes

--missing indexes on a single table
SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )AS [index_advantage] ,migs.last_user_seek ,mid.[statement] AS [Database.Schema.Table] ,mid.equality_columns ,mid.inequality_columns ,mid.included_columns ,migs.unique_compiles ,migs.user_seeks ,migs.avg_total_user_cost ,migs.avg_user_impactFROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK )
ON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK )ON mig.index_handle = mid.index_handleWHERE
AND mid.database_id = DB_ID() mid.[statement]= '[statusupdates].[dbo].[Referrals]'ORDER
Look at the last_user_seek column and see how frequently it is seeked. This is an important factor to decide whether the suggested index is needed or not


I created the following index based on the equality column and there was no include column NONCLUSTERED INDEX IDX_referrals_Account
CREATE
ON Referrals (Account,Hospitalcode,Deleted,uploaded )
GO
/* to see when was the last index was updated*/
DBCC SHOW_STATISTICS ('referrals', 'IDX_referrals_hospitalcode') GO

/* after adding the new index I updated the statistics of the table */

EXEC
GO sp_updatestats
GO