DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 121--------------------------------------------------
       Possible Bad NC Indexes (writes > reads)  
       By: srinivasma_exceldbp Date: December 25, 2014, 4:20 am
       ---------------------------------------------------------
       -- Possible Bad NC Indexes (writes > reads)
       -- Look for indexes with high numbers of writes and zero or very
       low numbers of reads
       -- Consider your complete workload, and how long your instance
       has been running
       -- Investigate further before dropping an index!
       -- Select Database in dropdown in SSMS and run
       SELECT OBJECT_NAME(s.[object_id]) AS [table Name], i.name AS
       [Index Name], i.index_id,
       i.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);
       *****************************************************