Index Clean-Up Scripts

January 20, 2009 by Michelle Ufford
Filed under: Performance & Tuning, Syndication, T-SQL 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;

Tags: , , , ,

Comments

12 Comments on Index Clean-Up Scripts

  1. Sean Decker on Fri, 23rd Jan 2009 1:27 pm

    2. 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. Michelle Ufford on Fri, 23rd Jan 2009 1:47 pm

    3. 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 on Sat, 24th Jan 2009 8:24 am

    4. 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. SQL Queries That Identify Unused Indexes - Finders Keepers! « Sean Decker - SQL Server DBA on Fri, 30th Jan 2009 7:10 am

    5. [...] The query below was posted by Michell Ufford - a.k.a SQLFool [...]

  5. Micchael B on Fri, 30th Jan 2009 7:58 am

    6. 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. Weekly Link Roundup 2009-02-04 | codegumbo on Wed, 4th Feb 2009 7:26 pm

    7. [...] Index Clean-Up Scripts:  It seems like I’ve beeen reading a lot about performance tuning and maintenance issues; you’re right.  I’m sudying for the upgrade to my MCDBA (MCITP), and a lot of my maintenance skills are rusty.  Posts like these are very interesting and help to keep the skills in check. [...]

  7. alerya on Tue, 14th Apr 2009 11:03 pm

    8. 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. Michelle Ufford on Thu, 16th Apr 2009 6:54 am

    9. 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 on Mon, 8th Jun 2009 8:10 am

    10. 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 on Wed, 17th Jun 2009 11:01 pm

    11. David. I am not shure, but try to use

    N ‘ abracadabra ‘

    instead of

    ‘ abracadabra ‘

  11. Rob on Wed, 26th Aug 2009 3:29 am

    12. 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. First round of SQLServerPedia syndicated bloggers | Brent Ozar - SQL Server DBA on Sat, 5th Sep 2009 10:52 am

    13. [...] Index clean-up scripts – before you make new indexes to improve performance, are you sure you need the ones you’ve already got? [...]

Tell me what you're thinking...
and oh, if you've enjoyed the post, please consider subscribing to my blog.