Suggest Indexes to Add - SQL Snipplr Social Repository

Revision: 32968

at October 5, 2010 06:25 by g8rpal

Initial Code

DECLARE @IndexFilegroupName VARCHAR(200)
SET @IndexFilegroupName = 'PRIMARY'

SELECT
      avg_total_user_cost,
      avg_user_impact,
    --mid.database_id ,
    --mid.object_id ,
    mid.equality_columns ,
    mid.inequality_columns ,
    mid.included_columns ,
    mid.statement AS [TABLE],
    user_scans,
    user_seeks,
    last_user_seek,
    last_user_scan,
    'CREATE NONCLUSTERED INDEX ix_' + OBJECT_NAME(mid.object_id) + '_'
            + ISNULL(REPLACE(REPLACE(REPLACE(equality_columns,'[',''),']',''),',',''),'') + ISNULL(REPLACE(REPLACE(REPLACE(inequality_columns,'[',''),']',''),',',''),'') + CASE WHEN included_columns IS NOT NULL THEN '_inc' ELSE '' END
            + ' ON ' + mid.statement
            + ' (' + REPLACE(ISNULL(equality_columns,'') + ISNULL(inequality_columns,''),'][','],[') + ')'
            + CASE WHEN included_columns IS NOT NULL THEN ' INCLUDE (' + included_columns + ')' ELSE '' END
            + ' ON [' + @IndexFilegroupName + '];'
      AS statement
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    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)
ORDER BY 1 desc

Initial URL

Initial Description

Initial Title

Suggest Indexes to Add

Initial Tags

Initial Language

SQL

Choose a language for easy browsing: