One tool I have in my toolbox is the great missing index script from Bart Duncan. However for me there were a few things I wanted to tweak to make it a better tool. I needed just the absolutely relevant information and I needed away to cut my time spent looking at the results.
1.) I changed the index naming convention so that it is more standard instead of the gobbly gook it normally displays. I used to spend a lot of time changing that gobbly gook to something more meaningful. Personally I generally stick to naming my indexes something like IX__TableName__Col1__Col2 etc. This way I can easily see what table and columns are used in it. I also use two underscores to separate objects as so many people seem to use underscores in their object names.
2.) Sometimes I see it return 200, 300, or more indexes. I definately don’t want to add that many indexes or even consider them. So generally I limit myself to looking at the top 20. This is just one tool in my toolbox and I don’t want to spend too much time just on it.
3.) The original script only ordered by the improvement measure. This lets you see the indexes which should provide the most improvement. That’s great but sometimes the recommendations are duplicates of each other or are similar and could be combined. Therefore I take the top 20 ordered by improvement measure and then order them again by database and table name. It makes it a little easier to work through them as I verify them against the existing indexes, queries the recommendations came from, etc.
4.) The original script has a lot of good info but generally it was too much. I also found it a pain to have to right click and copy the index create statements that I was interested in. In my script you can save the results to a text file and work on it from there.
So now that you have this great tool, make sure you use it in moderation and make sure you heed a few warnings. Bart’s blog that I linked to above lists a few important caveats that you should look into before just blindly adding everything the DMVs recommend.
WITH misind (improvement_measure, database_id, table_name, create_index_statement) AS ( SELECT TOP 20 CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) ) AS improvement_measure, mid.database_id, object_name(mid.object_id,mid.database_id) as [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 + ')', '') AS create_index_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 CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10000 AND last_user_seek>DATEADD(wk,-1,GETDATE()) ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC ) SELECT --improvement_measure, create_index_statement FROM misind ORDER BY database_id, table_name