On occasion you may ask yourself if there are any under used indexes in your database.  If not you, then possibly a manager or client.  Usually this comes up when evaluating for missing indexes or better indexes.

SQL Server provides a means to find the information we seek.  We can query the sys.dm_db_index_usage_stats DMV  to garner much of the information we want.  You can read about this DMV here.

The columns we want to take a look at are the seeks, scans and lookups columns.  In conjunction with that, we want to compare those columns to the updates related columns.

Here is an example query.

Code block

SELECT TableName = OBJECT_NAME(s.OBJECT_ID),IndexName = i.name, i.index_id
        ,TotalUserWrites = user_updates
        ,TotalUserReads = user_seeks + user_scans + user_lookups
        ,[DIFFERENCE] = user_updates - (user_seeks + user_scans + user_lookups)
,TotalSystemReads = s.system_seeks + s.system_scans + s.system_lookups
,TotalSystemWrites = s.system_updates
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[OBJECT_ID] = i.[OBJECT_ID]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
And (user_seeks + user_scans + user_lookups + s.system_seeks + s.system_scans + s.system_lookups) = 0
AND i.index_id > 1
ORDER BY [DIFFERENCE] DESC, TotalUserWrites DESC, TotalUserReads ASC OPTION (RECOMPILE);

In this query, I am looking at two main components for comparison.  I want to determine where there are more updates on an index than reads.  I also want to see only those indexes that have no reads.

Using a query like this can help you to narrow your search for under-performing indexes.  Indexes returned by this query tend to be more costly to maintain than the benefit they may be providing.  Starting with the indexes returned by this query, you can test and confirm the findings.