Home Dashboard Directory Help
Search

sys.dm_io_virtual_file_stats.sample_ms returns incorrect values by Jon Seigel


Status: 

Active


6
0
Sign in
to vote
Type: Bug
ID: 795556
Opened: 7/31/2013 9:55:09 AM
Access Restriction: Public
2
Workaround(s)
view
2
User(s) can reproduce this bug

Description

The column sys.dm_io_virtual_file_stats.sample_ms is supposed to return the number of milliseconds since the computer started. It returns this value as an int, which limits the positive range of the value to ~25 days, then flips to negative numbers, etc.

This column should return a bigint just like sys.dm_os_sys_info.ms_ticks.

This occurs on every instance I tried, from 2005-2012.
Details
Sign in to post a comment.
Sign in to post a workaround.
Posted by BPODFW on 12/19/2013 at 10:48 AM
On SQL 2005 you can use SELECT ms_ticks/86400000 FROM sys.dm_os_sys_info to get days since SQL Server Service was started

On SQL 2008 and higher this returns the time up for the Server OS not SQL Server Service uptime
Use SELECT (ms_ticks - sqlserver_start_time_ms_ticks)/86400000 FROM sys.dm_os_sys_info or you can use datediff(hh,sqlserver_start_time, GETDATE())/24

I found that the ms_ticks was the server OS start time and sqlserver_start_time_ms_ticks was the days since OS started that SQL Server service was started.
Posted by Jon Seigel on 7/31/2013 at 9:55 AM
Use sys.dm_os_sys_info.ms_ticks instead.