This query will show the indexes that are missing ordered according to those having the most impact. It will also provide the create index script needed in order to help you create the index.
,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,OBJECT_NAME(mid.Object_id),
'CREATE INDEX [missing_index_' + CONVERT (VARCHAR, mig.index_group_handle) + '_' + CONVERT (VARCHAR, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' 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,
migs.*, mid.database_id, mid.[object_id]
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 migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
What you need to know about this script however is what it does not account for. It does not account for an index that should be clustered. One of the warning signs that an index should be clustered is when this query suggests to you an index that contains a lot of columns (or has a lot of include columns). It is suggesting that, because it does not want to do a bookmark lookup to get the columns it needs for the select list. In those cases, you want to compare the number of user_seeks this query tells you, with the number that is shown in the Index Usage DMV. If this query’s seeks are higher, then consider making this the clustered index.
Also, once the queries are created, you can check to see if your assumption was correct by using the Clustered Index Checker DMV Script.
The original author’s article of this popular query (Bart Duncan) can be found here
Don’t stop now — Learn everything about indexes and indexing with a downloadable tutorial
We will go over subjects
clustered vs nonclustered
how to idenfity if an index is needed
how to identify missing indexes
how to apply an index to your newly written queries
poor indexes that cause deadlocks
..and much more
I’m confident after this you will know all the concepts there is to know about indexing.
For the price below, the video will be yours to download and keep.