First, these start values are not measuring the same thing.
sys.dm_os_sys_info
:
sqlserver_start_time - datetime - Specifies the date and time SQL Server
last started. Not nullable.
sys.dm_io_virtual_file_stats
:
sample_ms - bigint- Number of milliseconds since the computer was
started. This column can be used to compare different outputs from
this function.
Does it mean the statistic values (num_of_reads, num_of_writes etc..)
in DMF (sys.dm_io_virtual_file_stats) returns cumulative reads since
the computer restart or since the SQL Instance restart or there is
different interval time that we need to consider?
It should be cumulative reads/writes on each file ("Total number of bytes read on this file") for the current instantiation of the instance, i.e. since it was last started. The original function that this dmf replaces, sys.fn_virtualfilestats
returned a timestamp
column to help compare results received over time ("Database timestamp at which the data was taken").
Now that this is a dmf, it appears as though sample_ms
fills that void based on its description:
This column can be used to compare different outputs from
this function.
In most realistic cases the only thing reading/writing to SQL Server's data/log files is itself, so these would not be incrementing unless the instance was started anyways. Regardless, it does not appear the intent is to use this counter for any purpose other than comparing multiple outputs over time.
I was unable to find a canonical reference saying that all dmvs reset on instance restarting, but some of the index based dmvs' documentation do specify this:
The counters are initialized to empty whenever the SQL Server
(MSSQLSERVER) service is started.
Many of the dmvs do not return information that is cumulative, so it doesn't make sense to describe them as being reset on an instance restart. However, this one does appear to fall into that category and should be noted as such.
I've made a pull request to updated the documentation to reflect the fact that it resets on instance startup.
datetime
while other one returnsinteger
, see BOL. You should cast/convert to similar datatype and then compare. Just an observationsqlserver_start_time
result when it's likeDATEDIFF (SECOND, sqlserver_start_time, SYSDATETIME())
(as mentioned in query section of my question). In sample screenshot, i'm comparing 2nd and 3rd