0

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

  • 1
    Do all indexes belong to the same table? Unless an index is filtered, they should all be written to the same number of times under normal DML activity. Why would you expect some indexes to be written to a different number of times? If a row is updated or added, any columns in any index must also be added/updated. – Aaron Bertrand Mar 25 '15 at 13:54
  • 1
    Also please, please, please read this post - DTA is terrible. – Aaron Bertrand Mar 25 '15 at 13:56
  • @AaronBertrand Ha yeah I know it's terrible. I'll fix these issues here. Programmers were adding indexes with queries. Now i'm taking control here. Thanks a lot for you comment/answer. WIll pay attention on this. – Racer SQL Mar 25 '15 at 14:00
3

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.