Rebuilding an index clears stats from sys.dm_db_index_usage_stats - by Joseph Sack

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


116
0
Sign in
to vote
ID 739566 Comments
Status Resolved Workarounds
Type Bug Repros 23
Opened 4/27/2012 12:08:27 PM
Access Restriction Public

Description

Rebuilding an index clears any existing row entry from sys.dm_db_index_usage_stats for that index.  This was not the behavior in SQL Server 2008.
Sign in to post a comment.
Posted by Pedro [MSFT] on 4/12/2016 at 4:48 PM
Fixed for SQL Server 2016, upcoming SQL Server 2014 SP2, and upcoming CU3 for SQL Server 2012 SP3.

Pedro Lopes - Senior Program Manager
Posted by David Schanzer on 3/23/2016 at 7:05 PM
Good news guys! As Microsoft has documented (https://msdn.microsoft.com/en-AU/library/ms188755.aspx), "Beginning with SQL Server 2016 Release Candidate (RC0), entries are retained as they were prior to SQL Server 2008 R2."

I've tested an ALTER INDEX...REBUILD on SQL 2016 RC1, and it's true - the values in sys.dm_db_index_usage_stats are persisted.
Posted by Torbenmikkelsen on 7/30/2015 at 11:41 PM
The problem is still i SQL2014 RTM.
Posted by Nizamettin Özpolat on 5/22/2014 at 1:00 AM
This is something not acceptable. sys.dm_db_index_usage_stats must be persistant. Please reopen this as bug and fix it.
Posted by Kalen Delaney on 1/27/2014 at 10:18 AM
What if they just added a column telling us when the stats were last reset/cleared?
Posted by RobNicholson, MCSM on 5/4/2013 at 12:04 AM
This problem still persists in 2012 SP1 CU3. This issue makes it very hard to know which indexes are not being used. A similar behavior occurs with dm_db_index_operational_stats (this is stated in Books Online though).
Posted by Patrick Keisler on 12/18/2012 at 6:38 PM
Nothing has changed in SQL 2012 SP1 (11.0.3000). The stats are not persistent.
Posted by Phil Brammer on 10/31/2012 at 10:54 AM
Confirmed that this is still the case after applying SQL 2012 CU4 (11.0.2383).
Posted by Timothy Ford on 8/16/2012 at 12:09 PM
This is really bad for all those proactive SQL Professionals that use the usage stats when doing performance tuning and pruning of unused/low-use indexes. Please remove this new "functionality" from SQL Server 2012.
Posted by Kendra D Little on 8/16/2012 at 11:25 AM
I have been able to reproduce this on SQL Server 2012 RTM.