Friday, December 3, 2021

SQL Server - Query for Possible bad Indexes

 --Possible bad Indexes (writes > reads)

SELECT  OBJECT_NAME(s.object_id) AS 'Table Name',sca.name  , i.name AS 'Index Name',

'DROP INDEX ' + i.name + ' ON ' + sca.name + '.' + OBJECT_NAME(s.object_id)  ,

        i.index_id, user_updates AS 'Total Writes',

        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

INNER JOIN sys.tables AS t ON s.object_id = t.object_id

INNER JOIN sys.schemas As sca on t.schema_id = sca.schema_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 ;

No comments: