Post reply

sys.dm_db_index_usage_stats user_updates higher for a non-clustered index than the PK

  • March 15, 2016 at 8:44 am

    #314110

    I'm trying to understand the output of sys.dm_db_index_usage_stats for a couple of tables on a SQL 2012 installation.

    This table has a clustered index (index id 1), with column 1 as the only key field, and a non-clustered index (index id 2), with column 2 as the key field (and no included fields).

    When I look at the output from sys.dm_db_index_usage_stats for this table, it is showing the following :

    Index_id ----------- User_updates

    1 --------------------- 826

    2 --------------------- 131,621

    How is it possible for the number of updates on a non-clustered index to be higher than the number of updates on the clustered index? Surely any changes to columns covered by the non-clustered index would also require a corresponding change to the clustered index?

  • March 15, 2016 at 8:47 am

    #1865265

    Stats are reset when the index is rebuilt.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 15, 2016 at 8:55 am

    #1865270

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply