Small bug with sample_ms in sys.dm_io_virtual_file_stats

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.

One thought on “Small bug with sample_ms in sys.dm_io_virtual_file_stats

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.