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!