Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.
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.
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.”