I have a table, with some Indexes ( created by me, others by SQL Server Profiler (SqlServer 2008)).
I use this query to analyze unused Indexes:
--Possible bad Indexes (writes > reads) SELECT OBJECT_NAME(s.object_id) AS 'Table Name', i.name AS 'Index Name', 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 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 ;
But what I see, is that 14 Indexes ( yes i'm studing to disable them ) have the same "total writes". If I hold F5, i can se that they're used in "writes" at the same time.
Total reads are different.
What is the reason of this? why every index is updated ( write ) at the same time and same ammount, But with different reads?
Didn't found anything like this on web or stack.