A prior student emailed me yesterday about some strange behavior of the sample_ms column in sys.dm_io_virtual_file_stats. It’s supposed to be the number of milliseconds since the SQL Server instance has been started. He has a SQL Server 2016 instance that’s been running since August 2019, and it shows the following:
- ms_ticks from sys.dm_os_sys_info: 51915112684 (which works out to be August 28, 2019)
- sample_ms from sys.dm_io_virtual_file_stats: 375504432 (which works out to be about 4.5 days)
Clearly the variable inside SQL Server has wrapped/reset a bunch of times, either at 2^31 or 2^32, even though sample_ms is supposed to be a bigint from SQL Server 2016 onward.
We checked on a client system running 2017 and found:
- ms_ticks from sys.dm_os_sys_info: 7540084931
- sample_ms from sys.dm_io_virtual_file_stats: 3245117407
In this case, sample_ms is higher than 2^31, but has clearly wrapped/reset, so the wrap/reset point must be 2^32 (and 3245117407 plus 2^32 = 7540084703, almost exactly the ms_ticks value).
I reported this to the SQL Server team and they confirmed that it is a bug in SQL Server 2016 onward that they will fix, and also update the documentation to make it clear that in versions prior to SQL Server 2016, where sample_ms is an int, that the value will reset to zero periodically.
Not a major bug, because if you’re doing calculations using sample_ms, you can simply substitute ms_ticks from sys.dm_os_sys_info instead, but something some of you may want to be aware of.