When sys.dm_db_index_usage_stats can be misleading

A while ago, I was looking at a particular index which was badly fragmented in development and I decided the best course of action was to rebuild the index. When I then ran my index fragmentation & usage query, the data looked odd. The fragmentation was gone, but the numbers didn’t seem right. It was then I noticed a really low number of index seeks being returned.

As it transpires, an index rebuild will reset the counters for this index within the sys.dm_db_index_usage DMV and this is potentially very dangerous unless you are aware of this. Normally, we determine whether or not an index is in use by looking at this information, but if you perform regular maintenance on this table, you could be resetting the data which you rely on for an accurate decision.

I’d strongly recommend that when you use this, you also have an understanding of what indexes have been maintained recently to make sure you don’t delete an important index which has just been rebuilt – It will give the impression it is rarely used.

This does not apply to an index reorganization.

Leave a Reply