1

As per MS-Docs it returns the Number of milliseconds since the computer was started, but i'm getting different result when i compare with actual server start time in sys.dm_os_sys_info.

Here are the queries:

select sqlserver_start_time from sys.dm_os_sys_info;

select (sample_ms/1000) sample_sec, file_id from sys.dm_io_virtual_file_stats (DB_ID(), NULL);

select (DATEDIFF (SECOND, sqlserver_start_time, SYSDATETIME()) ) as Server_start_since_sec from sys.dm_os_sys_info;

This is the result:

enter image description here

If sample_ms reflects computer/server start-time not the SQL Instance's start-time (comparing 2nd vs 3rd in result screenshot). 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?


Before restart SQL Service

Before restart SQL Service

After restart SQL Service (NO OS restart) enter image description here

2
  • 2
    The values you are comparing are not of same datatype, the one returned by first query is datetime while other one returns integer, see BOL. You should cast/convert to similar datatype and then compare. Just an observation – Shanky Nov 20 '19 at 8:57
  • Hi @Shanky, yes, i mean to compare sqlserver_start_time result when it's like DATEDIFF (SECOND, sqlserver_start_time, SYSDATETIME()) (as mentioned in query section of my question). In sample screenshot, i'm comparing 2nd and 3rd – Shekar Kola Nov 20 '19 at 9:38
1

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.

3
  • Thanks for your post, after posting the question, i had test (enclosing screenshots now), this is what confusing me. if values cumulative, should not be reset upon SQL service or computer restart. It seems sample_sec is in sync with server start-time, but other stat values resetting. pls have a look at the screenshots. – Shekar Kola Nov 21 '19 at 4:36
  • Could you pls clarify bit more on cumulative over the lifetime of the file – Shekar Kola Nov 21 '19 at 4:36
  • 1
    @ShekarKola You're correct - I've edited to reflect that. – LowlyDBA - John M Nov 21 '19 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.