(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) 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); *****************************************************