Uncategorized

Missing indexes by average estimated impact

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO

SELECT
	[migs].[avg_user_impact] * ( [migs].[user_seeks] + [migs].[user_scans] ) [avg_estimated_impact],
	[user_scans], [user_seeks], [migs].[last_user_seek] [last_user_seek],
	OBJECT_NAME([mid].[object_id], [mid].[database_id]) [table_name],
	'CREATE INDEX [IX_' + OBJECT_NAME([mid].[object_id], [mid].[database_id]) + '_' + 
	REPLACE(REPLACE(REPLACE(ISNULL([mid].[equality_columns], ''), ', ', '_'), '[', ''), ']', '')  + 
	CASE WHEN [mid].[equality_columns] IS NOT NULL AND [mid].[inequality_columns] IS NOT NULL THEN '_'  ELSE '' END + 
	REPLACE(REPLACE(REPLACE(ISNULL([mid].[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + ']' +
	' ON ' + [mid].[statement] + ' (' + ISNULL([mid].[equality_columns], '') + 
	CASE WHEN [mid].[equality_columns] IS NOT NULL AND [mid].[inequality_columns] IS NOT NULL THEN ',' ELSE '' END + 
	ISNULL([mid].[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + [mid].[included_columns] + ')', '') [create_statement]
FROM    [sys].[dm_db_missing_index_groups] [mig]
	INNER JOIN [sys].[dm_db_missing_index_group_stats] [migs] ON [migs].[group_handle] = [mig].[index_group_handle]
	INNER JOIN [sys].[dm_db_missing_index_details] [mid] ON [mig].[index_handle] = [mid].[index_handle]
WHERE [mid].[database_id] = DB_ID()
ORDER BY [avg_estimated_impact] DESC
GO