Finding the bad indexes

Indexes are considered bad if the total writes > total reads



--Find bad indexes for a single table (writes > reads)--find bad indexs SELECT
user_seeks OBJECT_NAME(s.object_id) AS 'Table Name', i.name AS 'Index Name', i.index_id, user_updates AS 'Total Writes', + 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'

--Find bad indexes for a single table (writes > reads)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 sINNER JOIN sys.indexes AS i ON s.object_id = i.object_idAND i.index_id = s.index_idWHERE
OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1AND s.database_id = DB_ID()AND OBJECT_NAME(s.object_id) = 'referrals'GROUP BY OBJECT_NAME(s.object_id),i.name,i.index_idORDER BY 'Total Writes' DESC,'Total Reads' DESC ; ASC ;