01/31/2011
Finding Possible Bad Indexes in SQL Server
CREATE PROCEDURE FindPossibleBadIndex AS BEGIN -- Possible bad Indexes (writes > reads) DECLARE @dbid INT SELECT @dbid = DB_ID() SELECT 'Table Name' = OBJECT_NAME(s.object_id), 'Index Name' = i.name, i.index_id, 'Total #of Writes' = user_updates, 'Total #of Reads' = user_seeks + user_scans + user_lookups, 'Difference' = user_updates - ( user_seeks + user_scans + user_lookups ) 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 = @dbid AND user_updates > ( user_seeks + user_scans + user_lookups ) ORDER BY 'Difference' DESC, 'Total #of Writes' DESC, 'Total #of Reads' ASC ; END