Costly Indexes

SQL Costly Indexes

----------------------------------------------------------------
-- Author:	Doug McDonald
-- Date:	2011-11-21
-- Purpose:	Scans a server for the 20 most costly missing indexes 
--			according to the stats.
----------------------------------------------------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT 
	TOP 20
	ROUND(
		missing_index_stats.avg_total_user_cost 
		*
		missing_index_stats.avg_user_impact
        * 
        (missing_index_stats.user_seeks + missing_index_stats.user_scans),0
        ) AS TotalCost,
	missing_index_details.[statement] AS TableName,
	equality_columns,
	inequality_columns,
	included_columns
FROM 
	sys.dm_db_missing_index_groups missing_index_groups
	INNER JOIN 
	sys.dm_db_missing_index_group_stats missing_index_stats
		ON missing_index_stats.group_handle = missing_index_groups.index_group_handle
	INNER JOIN 
	sys.dm_db_missing_index_details missing_index_details
		ON missing_index_details.index_handle = missing_index_groups.index_handle
		
ORDER BY 
	TotalCost 
	DESC