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 ;