Sdílet prostřednictvím

Index Usage DMV behavior updated

  • Článek

Cross post with https://aka.ms/sqlserverteam

Up until SQL Server 2008 R2, using  index usage stats entries in sys.dm_db_index_usage_stats to make some assumptions over index design and workload patterns was common.

However, in SQL Server 2012 and higher versions, entries in this DMV were reset with every index rebuild, so this was no longer a viable way of tracking index usage patterns. This was clearly voiced out in Connect items, such as 739566 and direct customer feedback.

In SQL Server 2016 (starting with RC0), we are restoring the expected behavior with usage stats tracking in sys.dm_db_index_usage_stats, where entries will not be removed. This was documented in What's New in Database Engine.

We are also addressing this in an upcoming SQL Server 2014 Service Pack, and as usual deliver added value to in-market versions.

Pedro Lopes (@sqlpto) – Senior Program Manager