Data World

MSSQL Server: Find Bad Indexes

Posted by Pramod Singla on March 4, 2017

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]
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)
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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s