Index Usage Stats Bug Fixed in SQL Server 2012 SP2 CU12 / SP3 CU3

index-usage-stats-bugMicrosoft has just released a new round of cumulative updates for SQL Server 2012, and the release notes indicate that a fix to the sys.dm_db_index_usage_stats dynamic management view has been included in these updates. This is good news for index tuners using SQL Server 2012.

As of SP2 + CU12 and SP3 + CU3, SQL Server 2012 will persist index usage information even when ALTER INDEX REBUILD is run.

What About SQL Server 2014?

A comment on the Connect Item on sys.dm_db_index_usage_stats says the fix is planned to be released in the upcoming release of SQL Server 2014 SP2.

What About Missing Index DMVs info?

These DMVs still get reset/cleared on index rebuilds. Vote to fix this bug here.

Summary of sys.dm_db_index_usage_stats and Missing Index DMV Behavior

SQL Server Version sys.dm_db_index_usage_stats Missing Index DMVs
SQL Server 2005 & SQL Server 2008R2 Reset on database offline/restart. Reset on any index drop/disable/create on that table. Reset on database offline/restart.
SQL Server 2012 Reset on ALTER INDEX REBUILD of that index until SP2+CU12 or SP3+CU3 . Reset on database offline/restart. Reset on any index drop/disable/create on that table. Reset on database offline/restart. Reset on ALTER INDEX REBUILD of any index on the table.
SQL Server 2014 Reset on ALTER INDEX REBUILD of that index until SP2 (planned as of 5/18) . Reset on database offline/restart. Reset on any index drop/disable/create on that table. Reset on database offline/restart. Reset on ALTER INDEX REBUILD of any index on the table.
SQL Server 2016  Reset on database offline/restart. Reset on any index drop/disable/create on that table. Reset on database offline/restart. Reset on ALTER INDEX REBUILD of any index on the table.

Want the backstory? Read more in my post, “What Resets sys.dm_db_index_usage_stats and Missing Index DMVs.”

Friends don’t let friends release Cumulative Updates without testing.

 

Previous Post
Sabbatical Update: 4 Months of Fun and Reflection
Next Post
Blogger Tip: Easy Free-Use Images with Pablo by Buffer

Related Posts

5 Comments. Leave new

  • What about sys.dm_db_index_operational_stats ?

    Reply
    • I haven’t seen the behavior of index_operational_stats change across SQL Server versions. I’ve found the “How the Counters in the Metadata Cache Are Reset” section in Books Online on the topic to be accurate:

      “The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started. The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available. Dropping an index will cause the corresponding statistics to be removed from memory and no longer be reported by the function. Other DDL operations against the index may cause the value of the statistics to be reset to zero.”

      Reply
  • Theo Ekelmans
    May 25, 2016 1:23 am

    Holy #[email protected]#%^!.

    That means that if you are diligent with index maintenance you’ve gotta damn careful in deciding whether an index has been used or not / disabling “unused” indexes.

    This is a patch level that makes our lives maintaining indexes a little easier.

    Thanks for pointing this one out Kendra 🙂

    Reply
  • Yes tested and confirmed on SQL Server 2012 SP3 + CU3

    Reply
  • […] queries read data at all. Of course, if you’ve rebooted recently, or rebuilt indexes on buggy versions of SQL Server, you might get this warning on indexes that will get used. I can’t fix that, but I can tell […]

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu