Monday, January 28, 2013

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.

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 '-- Missing Indexes --'

    CONVERT (varchar, getdate(), 126) AS runtime,
  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,
    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

No comments:

Post a Comment