Sql-server – INDEX REBUILD IMPACT ON sys.dm_db_index_usage_stats

sql server

In which case (or sql versions) are the index statistics reset by index rebuild?
There are a lot of articles which makes me think that perhaps this behavior wasn’t always the case.

Best Answer

Kendra Little breaks this down in detail on her blog here: Index Usage Stats Bug Fixed in SQL Server 2012 SP2 CU12 / SP3 CU3

I've quoted the table from that blog post below:

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

The gist of it is that these stats got reset on index rebuilds in certain builds of SQL Server 2012 and 2014. You should update to the latest SP / CU, or to SQL Server 2016 or higher, to avoid the problem.

Related Question