0

The 'Op stats' are the numbers that give us the best information about index usage but can be reset at any time. If I look to the number of writes on the Clustered index (20.716), and I have approximately the same database usage every day, Is it then safe to conclude that numbers in the 'Op stats' haven't been reset for about 6 days?

My argumentation for this is:enter image description here

Writes on the clustered index happen on insert, updates and deletes. (20.716)
My server has been up for 16.67 days.
I have 7590 updates in the 'Op stats' (insert, updates, deletes)
16.67/20716*7590 = 6.1

So, my conclusion is, the 'Op stats' haven't been reset for the last 6 days. Would you tend to agree with me on this or am I missing something?

Bart
  • 21
  • 2

1 Answers1

1

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-operational-stats-transact-sql?view=sql-server-ver16

The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used. For information about this, see sys.dm_db_index_usage_stats (Transact-SQL).

The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started. The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available. Dropping an index will cause the corresponding statistics to be removed from memory and no longer be reported by the function. Other DDL operations against the index may cause the value of the statistics to be reset to zero.

The comparison of both columns are different. See also Brent's explaination where both come from:

Usage statistics come from sys.dm_db_index_usage_stats, which tracks the number of execution plans that include an operator touching that index. It's reset on SQL Server service restart, or when the index is modified.

Operational statistics come from sys.dm_db_index_operational_stats, which track the number of times the index has actually been touched. It's reset on a different schedule - when that object's metadata disappears from cache.

Why are Op Stats all 0's and yet Usage Stats show 29 seeks

So I think your assumption is right.

Peter
  • 2,530
  • 2
  • 6
  • 20