Finding missing indexes

find_missing_indexes.sql

-- Find Missing Indexes by Index Advantage
-- Look at index advantage, last user seek time, number of user seeks to help determine source and importance
-- SQL Server is overly eager to add included columns, so beware
-- Do not just blindly add indexes that show up from this query!!!
SET NOCOUNT ON
GO
SELECT TOP 25
DB_NAME(dm_mid.database_id) AS Database_Name,
user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage],
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs WITH (NOLOCK)
    ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid WITH (NOLOCK)
    ON dm_mig.index_handle = dm_mid.index_handle
WHERE DB_NAME(dm_mid.database_ID) NOT IN ('msdb','master','tempdb','model')
ORDER BY Database_Name, index_advantage DESC OPTION (RECOMPILE);
GO