SQL Server 2008 Missing Indexes

The following SQL script uses the dynamic management views in SQL Server 2008 to determine the missing indexes which would have the biggest impact.

Impact is calculated in terms of the frequency of the index usage (scans + seeks) and the measure of query improvement (total user cost and average user impact).

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 ROUND( s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans),0 ) AS total_cost , s.avg_user_impact , d.statement AS table_name , d.equality_columns , d.inequality_columns , d.included_columns FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle ORDER BY total_cost DESC