4

Why are Op Stats all 0's and yet Usage Stats show 29 seeks. This was returned from Brent Ozar's sp_blitzindex.

Usage Stats                         Op Stats
Reads: 29 (29 seek) Writes:14    0 singleton lookups; 0 scans/seeks; 0 deletes; 0 updates; 
  • 2
    Have you tried finding the answer by looking into the source code to see how the SP retrieves both groups of stats? – Andriy M May 26 '16 at 6:29
6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.