sqlskills-logo-2015-white.png

Exceptions–what sys.dm_db_index_usage_stats doesn’t tell you (Part II)

Last November I blogged about how index usage stats don’t get updated when the associated index statistics (but not index) are used.

This post will describe another scenario that you should be aware of (the topic came up today in class while Kimberly was teaching – as we were trying to recall tricks to clearing stats for sys.dm_db_index_usage_stats)…

Imagine that I’ve queried a specific table as follows:

SELECT member_no, lastname, firstname, middleinitial, street, city, state_prov, country
FROM dbo.member
WHERE member_no = 1;

If I check sys.dm_db_index_usage_stats for any reference to the member table, I’ll see the following:

SELECT i.index_id, i.name, u.user_seeks, u.user_lookups, u.user_scans
FROM sys.dm_db_index_usage_stats u
INNER JOIN sys.indexes i ON
     u.object_id = i.object_id AND
     u.index_id = i.index_id
WHERE u.object_id=object_id('dbo.member')

This returns:

image

Now let’s say that you have a weekly rebuild of specific indexes (for example):

ALTER INDEX member_ident
ON dbo.member REBUILD

If I check for usage stats after rebuilding the query (and before anyone has accessed the member table specifically) – the stats have been cleared out for that table. 

image

Why does this matter? 

If you’re using the sys.dm_db_index_usage_stats to determine which indexes should be removed, you’re running the risk of making decisions based on recently cleared out statistics.  This is similar to the case where a SQL Server instance has been recently restarted.  You should not be dropping indexes without knowing whether the accumulated statistics represent the full set of critical workloads. 

For tables with frequent index rebuilds, be sure to capture data from sys.dm_db_index_usage_stats before these jobs run.  This DMV is definitely a useful tool, but if you’re not careful, you could be dropping indexes based on missing information.

A few other noteworthy items:

  • Rebuilding an index only clears the stats for the index involved.  For example – if I have two rows in the DMV for two different indexes on the same object, rebuilding one index will only clear stats for that object.
  • Reorganizing the index does NOT clear the stats from sys.dm_db_index_usage_stats.
  • This is not the only circumstance where the stats will clear.
  • I have only tested this recently on SQL Server 2008 R2 and SQL Server 2012.  At one point when I was at Microsoft I blogged about seeing the stats persist after a REBUILD (more in the context of REBUILD itself – and I had mentioned the DMV in passing).  Which makes me think that perhaps this behavior wasn’t always the case.  If you’ve found a different behavior on different versions, please post your comment here.

Thanks!

12 thoughts on “Exceptions–what sys.dm_db_index_usage_stats doesn’t tell you (Part II)

  1. Just tested on SQL Server 2008 SP2, the Statistics are persisted when the Index is Rebuilt.
    Could be a bug?

  2. Thanks for testing this Paul! Yes – it sure seems to be. When I blogged about it at Microsoft, I seem to recall it persisted in 2005, but I didn’t have a test instance (at the moment) to validate.

    I’m going to file a Connect and see whether this is a known bug (or considered a behavior).

    Cheers

  3. I tested on SQL 2005 and it persisted. Can you post the connect item when it’s up? I would think a reboot would clear it, like most things, but a rebuild should keep it.

  4. Hi Joe,
    I tested on SQLServer 2008 R2 RTM and reindex didn’ reset the usage stats.
    Thanks

  5. Hi Farhan,

    Thanks for testing it! Seems this is indeed a bug. I’ve opened up a Connect item accordingly (and now we have a bit more information on versions where it used to work, and where it changed). I’m not sure I’ll get to testing the SPs and CU variances, but as others test on their versions, this post will be a good place to track.

    Best Regards,

    Joe

  6. FYI – I tested the following:

    Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (X64) and the row was retained

    Microsoft SQL Server 2012 – 11.0.2316.0 (X64) and the row was not retained

    I seem to recall an SP2 version where this happened, but I’ll hunt for it (although perhaps this was on a pre-release version of 2012 that I saw this instead).

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.