— 25 missing indexes that will improve performance the most
SELECT TOP 25 dm_mid.database_id AS DatabaseID,
DB_NAME(dm_mid.database_id) AS [Database],
dm_migs.avg_user_impact * ( dm_migs.user_seeks + dm_migs.user_scans ) AS [Avg Estimated Impact],
dm_migs.last_user_seek AS [Last Usage Time],
OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) AS [Table],
‘CREATE INDEX [IDX_’ + 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 Index Statement]
FROM sys.dm_db_missing_index_groups AS dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats AS dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS dm_mid ON dm_mig.index_handle = dm_mid.index_handle
ORDER BY [Avg Estimated Impact] DESC;
GO