Why is sqlserver_start_time not equal to sqlserver_start_time_ms_ticks - ms_ticks in sys.dm_os_sys_info?

Question

Why do these two queries produce different results (but within about a minute of each other)?

SELECT  StartTime   =   sqlserver_start_time
  FROM  sys.dm_os_sys_info;

SELECT  StartTime   =   DATEADD(MILLISECOND, sqlserver_start_time_ms_ticks - ms_ticks, GETDATE()) 
  FROM  sys.dm_os_sys_info;

Which is the more accurate?

Answer 1

Which is the more accurate?

Neither are the MOST accurate [A], however it does seem that the dubious calculations of date can be somewhat more accurate [C] if those are your only two options.

Why do these two queries produce different results (but within about a minute of each other)?

They seemed to be stored two different ways [B], and without going in super deep (but deep enough) the extra processing time along with where they are set, and time spent in 3rd party modules is making up the difference.

Deep Dive

[A] The most accurate process start time is what Windows has for the process structure, which can easily be queried via powershell or WinAPI. I start SQL Server under a debugger to see if there was an easy way to figure this out as I could add in any time delay that I would like. Given that something such as start time/ticks would make sense to be a more global item to track, I attempted to use public symbols to see if I could find it and artificially introduce a decent time gap. The picture below is when the process was started under the debugger, according to Windows, which is the most accurate time - 11/22/2022 11:46:23 AM, we'll refer back to this value.

Get-Process -Name sqlservr | Select Name, StartTime

[B] I then trolled the public symbol exports for anything in sqldk since that's the dll that's essentially doing the initialization of SQL OS, which would make the most sense as a place to get/set the startup information or time.

x sqldk!*boot*

There are in fact to hits for this that seem to be interesting, along with some functions that might be good places to set breakpoints. Note that the highlighted values seem to match the DMV fairly well.

[C] Restart the process under the debugger, setting ba items for the two seemingly global values and setting a break on the sqldk module load. The Ticks and Time are set at essentially the same area in the sqldk!SOS_OS::InitOs function, however for the TimeWhenSOSBooted value, a call is made into the kernel for GetSystemTimeAsFileTime which will take extra processing time and additionally can be detoured, among other methods that anti* programs use.

Since I was able to add in delays, the end result is the following.

Windows Process Creation Time: 11/22/2022 11:46:23 AM
sql_server_start_time (DMV): 2022-11-22 11:48:08.550 AM
sql_Server_start_time_ticks (DMV, Calculated): 2022-11-22 11:47:34.240 AM

Thus, it seems ticks is set first and the more accurate between the two values (even accounting for rounding and math issues) but is not accurate in the overall scheme of things, as this belongs to the Windows process creation time value which I introduced a full 1+ minutes of time difference.