Index Clean-Up Scripts

I’ve been spending a lot of time lately looking at indexing in my production environments… dropping un-used ones, adding missing ones, and fine-tuning the ones I already have. I thought I’d share some of the scripts I’ve been using to accomplish this.

Here’s the script I use to find any un-used indexes. This relies heavily on the sys.dm_db_index_usage_stats DMV (2005+). This query will also return the SQL statements needed to drop the indexes for convenience. This does NOT mean you should necessarily drop the index. This is only a guide and a starting point; only you know how your application is used and whether SQL Server’s recommendations make sense.

Un-Used Indexes Script

Declare @dbid int
    , @dbName varchar(100);
 
Select @dbid = DB_ID()
    , @dbName = DB_Name();
 
With partitionCTE (object_id, index_id, row_count, partition_count) 
As
(
    Select [object_id]
        , index_id
        , Sum([rows]) As 'row_count'
        , Count(partition_id) As 'partition_count'
    From sys.partitions
    Group By [object_id]
        , index_id
) 
 
Select Object_Name(i.[object_id]) as objectName
        , i.name
        , Case 
            When i.is_unique = 1 
                Then 'UNIQUE ' 
            Else '' 
          End + i.type_desc As 'indexType'
        , ddius.user_seeks
        , ddius.user_scans
        , ddius.user_lookups
        , ddius.user_updates
        , cte.row_count
        , Case When partition_count > 1 Then 'yes' 
            Else 'no' End As 'partitioned?'
        , Case 
            When i.type = 2 And i.is_unique_constraint = 0
                Then 'Drop Index ' + i.name 
                    + ' On ' + @dbName 
                    + '.dbo.' + Object_Name(ddius.[object_id]) + ';'
            When i.type = 2 And i.is_unique_constraint = 1
                Then 'Alter Table ' + @dbName 
                    + '.dbo.' + Object_Name(ddius.[object_ID]) 
                    + ' Drop Constraint ' + i.name + ';'
            Else '' 
          End As 'SQL_DropStatement'
From sys.indexes As i
Inner Join sys.dm_db_index_usage_stats ddius
    On i.object_id = ddius.object_id
        And i.index_id = ddius.index_id
Inner Join partitionCTE As cte
    On i.object_id = cte.object_id
        And i.index_id = cte.index_id
Where ddius.database_id = @dbid
Order By 
    (ddius.user_seeks + ddius.user_scans + ddius.user_lookups) Asc
    , user_updates Desc;

This next script relies on several DMV’s (2005+) that identify missing indexes. While this is good information, the index recommendations do not always make sense and/or sometimes overlap. Also, these DMV’s store data since the SQL Server was last restarted, so if it’s been a while since your server was rebooted, this data may be out of date. This script also provides a SQL statement, in case you do decide to create the index, but it doesn’t take into consideration advanced parameters (i.e. sort_in_tempDB, Online, MaxDop, etc.) and only provides a basic create statement. Nonetheless, it’s another good starting point.

Missing Index Script

Select t.name As 'affected_table'
    , 'Create NonClustered Index IX_' + t.name + '_missing_' 
        + Cast(ddmid.index_handle As varchar(10))
        + ' 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;
0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , , , . Bookmark the permalink.

15 Responses to Index Clean-Up Scripts

  1. Sean Decker says:

    StatisticsIO referred me.

    Great Queries! I appreciate your willingness to share them.

    How do YOU handle heavily indexes tables from 3rd party apps when you see millions of rows, writes, zero reads, and GB of space?

  2. Thanks, Sean!

    So you have large tables with lot of writes and very little (or no) reads? Is performance a concern?

    In my experience with 3rd party apps, the indexing sometimes doesn’t make much sense. Some 3rd parties may toss indexes on tables in anticipation of how the data will be used, which may vary significantly from how it ends up actually being used. If you’re finding that’s the case, then I’d remove them. Just make sure not to remove all of them at once, that way you can more easily identify the source of any problems that may arise.

  3. Sean Decker says:

    Michelle,

    That is the case: 3rd Party app(s). Indexed for various customer scenarios. One table in particular has tens of millions of rows and writes. Eight of the indexes on it have a grand total of 14 reads (five have zero).

    Reports of slowness came in. Performance isn’t quite as critical…it’s an “IS” box…but the education has been.

    Supports reply, paraphrased: “Changing the indexes is not a supported modification, as it can affect the performance of the software, this has not been tested. With that said, ultimately it will not HURT anything since it is just an index, but it may change how fast the app performs. Indexes are used in writing as well as reading.”

    Thanks,
    Sean.

  4. Pingback: SQL Queries That Identify Unused Indexes - Finders Keepers! « Sean Decker - SQL Server DBA

  5. Micchael B says:

    Sean, one supposed way to work around that is to disable the index – which means it no longer gets updated, which would improve write performance. The downside is that I’ve not had the DISABLE work properly, though I would play with the ability.

    For you it’s less of a deal – they’re just saying it’s unsupported, not that they’ll stop supporting it (there are some apps where changing ANYTHING with the indexes negates their support).

    Michelle, thanks for the script. Interesting to see how people do it slightly differently; mine doesn’t worry about the partitioned indexes (I should add it), but doesn’t look at clustered indexes.

  6. Pingback: Weekly Link Roundup 2009-02-04 | codegumbo

  7. alerya says:

    I found a small error in the code. The matter that if inde type is 2 and can be unique, so better I think will be instead of :
    …..
    WHEN i.type = 2 And i.is_unique = 1
    THEN ‘Alter Table ‘ + @dbName
    …..
    better way is:
    WHEN i.type = 2
    THEN ‘Drop Index ‘ + i.name
    + ‘ On ‘ + @dbName
    + ‘.dbo.’ + OBJECT_NAME(ddius.[OBJECT_ID]) + ‘;’
    WHEN i.type = 1 And i.is_unique = 1
    THEN ‘Alter Table ‘ + @dbName
    + ‘.dbo.’ + OBJECT_NAME(ddius.[OBJECT_ID])
    + ‘ Drop Constraint ‘ + i.name + ‘;’
    ELSE ”
    END AS ‘SQL_DropStatement’

  8. Hi Alerya,

    I just took a look and the problem is that I was using [is_unique] instead of [is_unique_constraint]. I’ve updated the script to use the appropriate columns.

    Thank you, Alerya, for catching that bug! :)

  9. david hay says:

    Michelle,

    Thanks for the scripts, they work great except in a case sensitive collation. OBJECT_ID, and STATEMENT both need to be lower case.

    The 2nd query I am still having an issue with

    Msg 451, Level 16, State 1, Line 1
    Cannot resolve collation conflict for column 2 in SELECT statement.

    Any thoughts or suggestions?

    Thanks again!

    David Hay

  10. alerya says:

    David. I am not shure, but try to use

    N ‘ abracadabra ‘

    instead of

    ‘ abracadabra ‘

  11. Rob says:

    To solve the “Cannot resolve collation conflict for column 2 in SELECT statement.”, replace
    “AS sql_statement”
    with “COLLATE database_default AS sql_statement”

  12. Pingback: First round of SQLServerPedia syndicated bloggers | Brent Ozar - SQL Server DBA

  13. Pingback: Removing Unused Indexes | SQL Server Performance Blog

  14. MS says:

    Hi Michelle,

    Thanks for this script. One question, and i must be blind or its been a very long day, but in the un-used indexes script, what is the criteria to make them ‘un-used’? Is it a function of how many seeks/scans/lookups/updates have been done on it?

    Thanks!

  15. Thanks for share this excellent post with us is really interesting, keep up the good work

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>