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

on May 18, 2016

Microsoft 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.

index-usage-stats-bug

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.”