Monday, October 5, 2009

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 (  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.

    QUOTENAME( + '.' + QUOTENAME( AS TableName, AS IdxName,
    i.type_desc AS IdxType,
    (ius.user_seeks*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Seek],
    (ius.user_scans*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Scan],
    (ius.user_lookups*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Lookup],
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
    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
    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
    ius.database_id = DB_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.


Jeff Stevenson said...

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

Should one include user_lookups if they are going to roll up the index totals to the table level?

My understanding is that when bookmark lookup occurs the non-clustered indexes are using the clustered index to point to the base row.

If you are totaling by table the bookmark lookup also shows up in the user_seek totals for the non-clustered index. If one includes the bookmark lookup you end up counting that access twice.

Andrew Smith said...

why is it important to join a table?

Smith | corporate shirts

Anonymous said...

I think you mean why he is using an LEFT Join?

This is because you get all Data from the LEFT Table when you use a left join

With an inner join you only get data, where the join condition meets both Tables..