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