Missing Index Details

While looking at the Dynamic Management Views for missing indexes on the  Microsoft site I came across the comment on the SQL 2012 version.  It contains, supposedly, the script used by Microsoft Customer Support to help identify missing indexes and their impact.  BTW, it does work on SQL Server 2008r2.


http://msdn.microsoft.com/en-us/library/ms345434.aspx



The comment is below, both for my future reference an in case it is deleted on the msdn site...

Example script to see missing indexes as used by Microsoft Customer Support

PRINT 'Missing Indexes: '

PRINT 'The "improvement_measure" column is an indicator of the (estimated) improvement that might '

PRINT 'be seen if the index was created.  This is a unitless number, and has meaning only relative '

PRINT 'the same number for other indexes.  The measure is a combination of the avg_total_user_cost, '

PRINT 'avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.'

PRINT ''

PRINT '-- Missing Indexes --'

SELECT

    CONVERT (varchar, getdate(), 126) AS runtime,

    mig.index_group_handle,

    mid.index_handle,

  CONVERT (decimal (28,1),

   migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,

  'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle)

    + '_' + CONVERT (varchar, mid.index_handle)

    + ' 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           AS mig

    INNER JOIN sys.dm_db_missing_index_group_stats      AS migs

        ON migs.group_handle = mig.index_group_handle

    INNER JOIN sys.dm_db_missing_index_details          AS 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)) > 10

                               

ORDER BY      migs.avg_total_user_cost

            * migs.avg_user_impact

            * (migs.user_seeks + migs.user_scans) DESC

           

PRINT ''

GO