Index Clean-Up Scripts - SQL Fool

There are a bunch of similar scripts around but I keep coming back to this one for quick one-offs. I have to hunt for it each time though so consider this a bookmark and archival topic :slight_smile:

Oh, also I have to update the script to add a collate default so here is my fixed version for posterity:

Select As 'affected_table'
    , 'Create NonClustered Index IX_' + + '_missing_' 
        + Cast(ddmid.index_handle As varchar(10)) COLLATE DATABASE_DEFAULT
        + ' On ' + ddmid.statement 
        + ' (' + IsNull(ddmid.equality_columns,'') 
        + Case When ddmid.equality_columns Is Not Null 
            And ddmid.inequality_columns Is Not Null Then ',' 
                Else '' End 
        + IsNull(ddmid.inequality_columns, '')
        + ')' 
        + IsNull(' Include (' + ddmid.included_columns + ');', ';'
        ) As sql_statement
    , ddmigs.user_seeks
    , ddmigs.user_scans
    , Cast((ddmigs.user_seeks + ddmigs.user_scans) 
        * ddmigs.avg_user_impact As int) As 'est_impact'
    , ddmigs.last_user_seek
From sys.dm_db_missing_index_groups As ddmig
Inner Join sys.dm_db_missing_index_group_stats As ddmigs
    On ddmigs.group_handle = ddmig.index_group_handle
Inner Join sys.dm_db_missing_index_details As ddmid 
    On ddmig.index_handle = ddmid.index_handle
Inner Join sys.tables As t
    On ddmid.object_id = t.object_id
Where ddmid.database_id = DB_ID()
    And Cast((ddmigs.user_seeks + ddmigs.user_scans) 
        * ddmigs.avg_user_impact As int) > 100
Order By Cast((ddmigs.user_seeks + ddmigs.user_scans) 
    * ddmigs.avg_user_impact As int) Desc

This is apparently good too:

Posting here so I remember to follow up and give it a try :slight_smile:

1 Like

Brahn, I use a lot of Brent Ozar’s scripts.
His first responders kit is very useful.
Include info for finding bottle necks, what waits are slowing down the server, tc.


A post was split to a new topic: This code if useful to Audit all SQL tables for Insert, Update, Delete