Determine Which Indexes Are Not Being Used

Have you ever had the need to find out which indexes are actually being used?  If you are using SQL 2005 and greater, you are in luck.  SQL Server 2005 introduced a new dynamic management view that gives all the usage statistics, sys.dm_db_index_usage_stats (http://msdn.microsoft.com/en-us/library/ms188755.aspx).  What does this mean?  Well it means we can capture which indexes are being used and which ones are an administrative burden.  What’s the catch?  The catch is the usage statistics do not persist a SQL Server restart. The statistics will not be of much value after a server restart, so an option may be to insert the results of the view into a permanent table, for later analysis.  Let’s look at an example.

SELECT 

    QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS TableName,

    i.name AS IdxName,

    i.type_desc AS IdxType,

    ius.user_seeks,

    (ius.user_seeks*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Seek],

    ius.user_scans,

    (ius.user_scans*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Scan],

    ius.user_lookups,

    (ius.user_lookups*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Lookup],

    ius.user_updates

FROM sys.indexes i

LEFT JOIN sys.dm_db_index_usage_stats ius

    ON  ius.object_id = i.object_id

        AND ius.index_id = i.index_id

        AND database_id = DB_ID()--Current DB

INNER JOIN sys.tables t

    ON t.object_id = i.object_id

INNER JOIN sys.schemas s

    ON t.schema_id = s.schema_id

WHERE

    t.type = 'U'

    AND t.is_ms_shipped = 0

ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC

Note: I have used a left join.  If an index has never been used, it will not have an entry in sys.dm_db_index_usage_stats.

The columns that really drive this view are user_seeks, user_scans, user_lookups, and user_updates.  User_seeks represents the number of seeks on a given index, since SQL Server last started.  Likewise, user_scans and user_lookups represent the number of scans and the number of lookups respectively.  One of the most important columns is user_updates.  User_updates represents the number of inserts, updates, and deletes to the index. If you find your index in a scenario where the number of user_updates is greater than the number of seeks, lookups, or scans, you should consider dropping the index.  There is no magic number to dictate when an index should be dropped because of the maintenance overhead.  You should use your best judgment when dropping an index. 

There you have it a simplistic method to get the index usage details.  How else can this dynamic management view be used?  I leave this to you to find out, but I will leave you with one other use.

The other use for this view is to identify the most queried tables.  By looking at the index usage stats, we can deduce how often the table is queried. 

--Most Accessed Tables

SELECT 

    DB_NAME(ius.database_id) AS DBName,

    OBJECT_NAME(ius.object_id) AS TableName,

    SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS TimesAccessed    

FROM sys.indexes i

INNER JOIN sys.dm_db_index_usage_stats ius

    ON  ius.object_id = i.object_id

        AND ius.index_id = i.index_id

WHERE

    ius.database_id = DB_ID()

GROUP BY 

    DB_NAME(ius.database_id),

    OBJECT_NAME(ius.object_id)

ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC

That is it.  I have provided two uses of sys.dm_db_index_usage_stats.  I hope this post will help you identify poorly performing indexes or you most active tables. 

Happy Coding.