Sql-server – What can cause an Index “total writer” to be the same in every index

indexsql-server-2008

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.

Same writes

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.

Thanks

Best Answer

Unless an index is filtered, they should all be written to the same number of times under normal DML activity.

If a row is updated or added, any columns in any index must also be added/updated.

So you should not expect some indexes to be written to a different number of times, unless you are reorganizing or rebuilding individual indexes specifically.

Also, please read this post over on SQLPerformance.com:

Related Question