Dynamic Management Objects/ Syndicated

One Of These Things Is Not Like The Others

, , 2 Comments

While examining things you can do with the sys.dm_os_info Dynamic Management View I ran across an interesting outcome concerning SQL Server start time.

Theorhetically, you can determine the last start time for your SQL Server services via any of the three methods:

  1. Create Date of tempdb (because tempdb is recreated each time the SQL services are started.)
  2. Retrieve the login_time from sys.dm_exec_sessions for session_id = 1 (it’s the first session established when services start and it is not re-used.
  3. Query sys.dm_os_sys_info for the sqlserver_start_time column.

Supposedly, either of these give you the correct information (though only to the nearest second as you’ll see below.)  There is one other interesting tidbit too.  Can anyone tell me what that is?

 SELECT
   D.create_date,
   DOSI.[sqlserver_start_time],
   DES.[login_time] FROM sys.[dm_os_sys_info] DOSI
   CROSS JOIN sys.[databases] D
   CROSS JOIN sys.[dm_exec_sessions] DES
WHERE D.[name] = 'tempdb'
   AND DES.session_id = 1;

If you said “Daylight Savings Time” then you’re correct.  The interesting thing is that we can’t say this is a DMV win either since one DMV (sys.dm_exec_sessions) does not convert it’s information between Daylight Savings Time and Standard Time.  Most likely I’m led to believe that sys.dm_exec_sessions uses a cached value for the login_time and sys.dm_os_sys_info is using a calculated value based off of the difference between the system date and the value of the sys.dm_os_sys_info.ms_ticks column.

At any rate, of the three options, using sys.dm_os_sys_info appears to be the only reliable one out of the group (give or take a few milliseconds that is.)