SQL SERVER DBA, Linux and Azure: Review bad indexes

Review bad indexes


 -- Review bad indexes


--Possible Bad NC Indexes (writes > reads)  


 -- Taking into connsideration the complete workload, and how long your instance has been running


 -- Advice not to drop or disable  indexes unless the is 100% certainty that it is no loger substantially benefitial


   SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id, 


   is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,


   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


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


 


 

No comments:

Post a Comment