SQL Script to find all Indexes that need to be added

Posted on November 14, 2013 by Michael Roma

This SQL script will show all Table Indexes that need to be added based on query history.

Just need to change database_id=12 to the database you want to check:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 50
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 TableName, 
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
where d.database_id = 12
ORDER BY [Total Cost] DESC

 

comments powered by Disqus