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
Dev Nexus Hub by Uma Mahesh
Connecting Developers to Architectural Excellence
Dev Nexus Hub by Uma Mahesh
Connecting Developers to Architectural Excellence