[SQL]T-SQL, Find out SQL Missing Index through DMVs.

2014-07-09

The source was from bartduncan, here.


SELECT migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure
    ,'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 migINNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handleWHERE 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


Note: The DMVs T-SQL scripts just show the best way for the current query against the database. it won’t tell you that you’ve had other indexes covered columns. Details, please see the link above.
Newer Older