The following script helps you to find the top 10 missing indexes using SQL 2005 DMV.
SELECT TOP 10 migs_adv.index_advantage,
mid.database_id,
mid.object_id,
mid.statement as table_name,
mig.index_handle as index_handle
FROM (select (user_seeks+user_scans) * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage, migs.*
from sys.dm_db_missing_index_group_stats migs) as migs_adv,
sys.dm_db_missing_index_groups mig,
sys.dm_db_missing_index_details mid
WHERE migs_adv.group_handle = mig.index_group_handle
AND mig.index_handle = mid.index_handle
ORDER BY migs_adv.index_advantage DESC
Now take the index handle of the top missing index and apply below to determine which columns should be included in the newly created index:
SELECT * FROM sys.dm_db_missing_index_details where index_handle = 45
Once you’ve got your columns, create your new index: (we tend to put indexes in their own fileGroups so they may reside on their own disk spindle for increased performance)
CREATE INDEX missingIndex_xxx
ON [DB].[dbo].[TBL]([col1], [col2], [col3])
INCLUDE ([col4], [col5])
ON FG_XXX_Index;
