SQL Server maintains a variety of stats about all sort of performance items.
- Index usage (or missing indexes)
- Query performance
- Corrupt pages
- Disk IO performance
- Way more than I care to list here
I was trying to troubleshoot some TerriBad * tempdb write performance…almost 3000ms per write, on a server that we recently migrated to.
Our data center vendor of course said the storage was perfect and that we should try troubleshooting SQL Server. (Duh?)
These stats are stored in the sys.dm_io_virtual_file_stats DMF, and I found this gem under the ‘sample_ms’ column description in the result set:
“The data type is int for SQL Server 2008 through SQL Server 2014 (12.x). In these versions, the value will reset to 0 after approximately 25 days of continuous database engine uptime.”
Now, this is a SQL 2017 box, but this tells me that these stats are going to reset at some point.
When I re-ran my IO stalls query…the writes for all 8 tempdb data files are at 7ms. The sample_ms is currently 3,097,351,985 which is about 36 days if I’ve done my math right. Server uptime is approaching 90 days.
The takeaway for you, dear reader, is to know where to find the stats but also to know what timeframe they represent.
* Thanks to Brent Ozar (b|t) for this highly amusing term 🙂
Thanks for reading!
Jeff Moden says
I have to ask… why does anyone care how old stats are? If you consider the plight of a static lookup/reference table, it could be YEARS between data changes in such tables.
Correct me if I’m wrong but the age of the stats doesn’t seem like it should matter. The only thing that should matter enough to rebuild stats is the ol’ RowModCtr column (which is what MS still uses from the deprecated sys. sysindexes dmv for their sp_updatestats proc for everything except Hekaton indexes even as late as SQL Server 2017). And the code for that is another “Scream Alert”. 😀 Lordy.
The replacement for the RowModCtr is located in the “modification_counter” column of the sys.dm_db_stats_properties function.
Hiya Jeff! This post was all about the system stats, not index stats.
In this case the IO stall stats changed dramatically without a restart…apparently having cleared themselves at some point 🙂