ALTER INDEX REBUILD clears sys.dm_db_index_usage_stats

05 September,2016 by Jack Vamvas

Question: I’m experiencing some strange behaviour with response from the SQL Server DMV sys.dm_db_index_usage_stats.   I understand the sys.dm_db_index_usage_stats  are refreshed every time there is a SQL Server restart. This is documented behaviour , which I build in to my understanding of how to interpret these statistics

The strange behaviour is when I execute a ALTER INDEX REBUILD and then check the sys.dm_db_index_usage_stats, the usage stats for the index disappear.

I’m using SQL Server 2012 Enterprise SP2

Answer: It sounds like you’re experiencing a bug which appeared in SQL Server 2012 and was fixed in SQL Server 2012 Service Pack 3 + CU3.

Download the latest service pack and use these instructions How to Install SQL Server service pack silently (SQL Server DBA)

The bug is when a ALTER INDEX myIndex ON myTable REBUILD  is executed, it deletes the usage stats for that index.  It deletes them without a SQL Server restart.    

This problem only relates to an INDEX rebuild.

This will have an impact on how you think about analysing index usage stats straight after an index maintenance plan. In the index maintenance phase the program will rebuild indexes. For this version of SQL Server , the index usage stats will flatten to 0.

In case you thought you were going crazy , you’re not!But I’d recommend you apply the relevant Service Pack and cumulative update.

 

SQL Script - How to get the table size, index size and last user update

Calculate the Size of Index (SQL Server DBA)

Missing Indexes. Equality and Inequality on sys.dm_db_missing_index_details


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on ALTER INDEX REBUILD clears sys.dm_db_index_usage_stats


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer