Finding Bad Indexes Using SQL Server DMVs

Today I want to show you how to go about finding bad indexes. SQL Server indexes are often promoted as the answer to all of our performance optimisation needs. It may then surprise you to hear that indexes can also be bad for performance.

“What are you yapping on about John” I hear you shout. Well let me explain….

You see, if the number of writes to a table (and its underlying indexes) is significantly greater than the number of reads, then it is possible that the indexes may actually be detrimental to performance overall.

This is because each time a write is made to a column that is involved in an index; a corresponding modification must also be applied to the indexes covering the column. Therefore, if the majority of activity is write activity, then it could be worth considering dropping or altering the indexes involved, in an effort to increase performance by reducing the overall amount of write activity.

I am not so crazy after all, well maybe just a little then.

Query to Identify Possible Bad Indexes

The following two queries can be used to assist you in your hunt to finding bad indexes (or rather potentially costly indexes):

--Possible bad Indexes (writes > reads)
SELECT  OBJECT_NAME(s.object_id) AS 'Table Name',
        i.name AS 'Index Name',
        i.index_id,
        user_updates AS 'Total Writes',
        user_seeks + user_scans + user_lookups AS 'Total Reads',
        user_updates - ( user_seeks + user_scans + user_lookups ) AS 'Difference'
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 i.index_id > 1
ORDER BY 'Difference' DESC,
        'Total Writes' DESC,
        'Total Reads' ASC ;
-- Index Read/Write stats for a single table
SELECT  OBJECT_NAME(s.object_id) AS 'TableName',
        i.name AS 'IndexName',
        i.index_id,
        SUM(user_seeks) AS 'User Seeks',
        SUM(user_scans) AS 'User Scans',
        SUM(user_lookups) AS 'User Lookups',
        SUM(user_seeks + user_scans + user_lookups) AS 'Total Reads',
        SUM(user_updates) AS 'Total Writes'
FROM    sys.dm_db_index_usage_stats AS s
        INNER JOIN sys.indexes AS i 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 OBJECT_NAME(s.object_id) = 'AccountTransaction'
GROUP BY OBJECT_NAME(s.object_id),
        i.name,
        i.index_id
ORDER BY 'Total Writes' DESC,
        'Total Reads' DESC ;