Find index usage against a particular table, & find all unused indexes in the database

The environment: SQL Server 2005 or SQL Server 2008

Maybe you have a large database, and you're looking for ways to save space.  Maybe you're seeing a large, heavily-used table with many indexes, and you're wondering if you could improve write performance by getting rid of some of the indexes.

Before you start dropping indexes left and right, you should start by checking to see if, and how often, you'd obviously like to check which indexes are actually being used.  Starting in SQL Server 2005, there are DMV's (Dynamic Management Views) that make this possible - namely, sys.dm_db_index_usage_stats.  This DMV holds a row for each index which has been used since the last time the SQL Service starts up.  This information does not persist across service restarts, so I generally like to wait at least a month before I start looking at this DMV to get an idea of which indexes are being used, and how.

For more information on the SQL DMV's, check out Books Online:

SQL Server 2005 Dynamic Management Views and Functions

SQL Server 2008 Dynamic Management Views and Functions

Personal note: I've found that a month's worth of data is reasonably representative of your actual index usage - for example, the table in question may have a large index which is only used once a month - but it happens to be used by a query that is critical to your monthly billing system.

1. Check all of the indexes on a given table:

    select t.[name],
        i.[name],
        s.*
    from sys.tables t
        left outer join sys.indexes i on t.object_id = i.object_id
        left outer join sys.dm_db_index_usage_stats s on i.index_id = s.index_id
                                    and i.object_id = s.object_id
                                    and s.database_id = db_id()
    where i.is_hypothetical = 0    -- discard stats from consideration
        and t.[name] = 'TableName'                                   
    order by t.[name], i.[name]   

       

2. Check all tables in the database for small numbers of total seeks, scans and updates against any of their indexes:

    select t.[name],
        i.index_id,
        i.[name],
        s.*
    from sys.tables t
        left outer join sys.indexes i on t.object_id = i.object_id
        left outer join sys.dm_db_index_usage_stats s on i.index_id = s.index_id
                                    and i.object_id = s.object_id
                                    and s.database_id = db_id()
    where i.is_hypothetical = 0        --    not stats
        and
        (      --    has no index activity
            s.index_id is null              

            --    or has very little index activity       
            or ( s.user_seeks + s.user_scans + s.user_lookups ) < 100  
        )
        and i.[name] is not null       --    has indexes               
        and i.index_id <> 1            --    not a primary key           
    order by t.[name], i.[name]