Three Methods for Identifying When the SQL Services Were Last Started

There are at least three methods for easily identifying the last time the SQL Server service was restarted on an instance.  For the methods I'll be showing today it comes down to your choice of two Dynamic Management Views or a System Catalog View.  Why is this information interesting?  For many diagnostic queries I run I want to know the scope of time the performance metrics are based upon.  Certain system performance metadata, namely Dynamic Management Objects, is purged upon service shutdown.  Using the example of reviewing index usage to determine whether or not a particular index is used to satisfy any queries in terms of seeks, scans or lookups I need to know if I'm basing decisions from sys.dm_db_index_usage_stats on whether to dump an "unused" index is based upon a significant range of data.  If the usage metrics are only covering a few days or weeks I may not be making accurate decisions on usage because perhaps a great number of queries are valuable for end-of-month reporting or on tasks that didn't occur during the sampling period.

That being said, let's take a look at three options for determining this information.

Option One: sys.databases

The sys.databases System Compatibility View in SQL Server 2005 and newer provides metadata about each database on your SQL Server instance.  Since tempdb is recreated each time services are restarted we can look at the create time of that database to return the latest service start time.

SELECT create_date 
FROM sys.databases 
WHERE name = 'tempdb';

Option Two: Query sys.dm_exec_sessions

This Dynamic Management View provides information about each session on your SQL Server instance and since the first session is started up when the SQL Server service starts up in order to facilitate system activity internal to running foo you can use the login_time column from this DMV as the latest SQL Server service start time.

SELECT login_time 
FROM sys.dm_exec_sessions 
WHERE session_id = 1;

Option Three:  Query sys.dm_os_sys_info (in SQL Server 2014)

This Dynamic Management View's schema seems to change upon every major release since SQL Server 2005.  (It's also undergone column changes in CU and SP changes too for that matter.)  SQL Server 2014 and newer though includes a column called "sqlserver_start_time" which can be queried for the last service start time (named appropriately.)

SELECT sqlserver_start_time 
FROM sys.dm_os_sys_info;

If we review the output from each of the three scripts we see we have inconsistent information though:

This is easily explained when we look at the sources for each of these results:

The last result is the oldest by almost two seconds.  This is the start time of the services from sys.dm_os_sys_info's sql_server_start_time column and is the true start time of the SQL Server service.

The second result set returns a time that is almost two seconds after the services start.  This result is determined by the first session's start time.  It tells us here there is a lag of about two seconds between service start and the first session acquiring a connection.

Finally, the first result occurs about another two seconds after that first session start time.  This is when tempdb is created.

While there are slight differences between the results and DBAs are normally strict followers of rules it's going to be rare that you're going to care, to the millisecond, what the start time is for the SQL Server services so in my book I'd call any of these valid determination sources for this information.  Typically I'll be using this date in a DATEDIFF() formula (like shown below) to show the number of days the SQL Service has been up so that I can use it as a denominator for averaging out metrics such as index scans/day (from the aforementioned sys.dm_db_index_usage_stats) or perhaps IO load per day coming from one of the many valuable columns from sys.dm_io_virtual_file_stats.  Be sure to avoid a divide by zero error though by using the syntax I show below.

DECLARE @days AS smallint;

SELECT @days =
CASE 
	WHEN DATEDIFF(DAY, sqlserver_start_time, getdate()) = 0 THEN 1
	ELSE DATEDIFF(DAY, sqlserver_start_time, getdate())
END
FROM sys.dm_os_sys_info;


SELECT num_of_reads, 
	num_of_writes, 
	num_of_reads/@days AS reads_daily,
	num_of_writes/@days as writes_daily
FROM sys.dm_io_virtual_file_stats(DB_ID('iDBA'),1);