MSSQL Server: Find Bad Indexes

Problem Statement: Script to find the Bad indexes
Solution: The indexes returned by the below query degrade the performance of the write queries. Consider deleting them.

–Find the bad indexes using following DMV:
— Possible Bad NC 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],–number of updates by user queries
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
OPTION (RECOMPILE);  

Leave a Reply

Your email address will not be published. Required fields are marked *