Checking SQL Server Start Time

When you are troubleshooting query performance, it’s important to consider when SQL Server last restarted. Each time SQL Server restarts, buffer pool and plan cache get wiped out. This can play a big part in how you approach an issue.

You want to know how much history you have to work with. Not seeing much data in plan cache? Maybe SQL Server restarted recently. Parameter sniffing issues that you were seeing earlier today are now suddenly “fixed?” SQL Server could have restarted between then and now. Let’s take a quick look a few ways to review how long SQL Server has been up and running.

Check sys.dm_exec_sessions

The way I typically check how long SQL Server has been up and running is by running a query in sys.dm_exec_sessions, specifically, where session_id is 1:

SELECT login_time
FROM sys.dm_exec_sessions WHERE session_id = 1;
GO

Check sys.dm_os_sys_info

Have you ever checked out sys.dm_os_sys_info? If not, know that this is another DMV that can be used to find the SQL Server start time:

SELECT sqlserver_start_time
FROM sys.dm_os_sys_info;
GO

Create_Date of tempdb

One more interesting way to determine SQL Server start time is to find out when tempdb was created. Since tempdb is recreated every time SQL Server starts, this can be used as an indicator of when SQL Server as a whole last started.

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

If it’s up…

If we run all three of these together, we can expect just a slight variation but not enough to throw you off for performing a general check:

If you’re logging into a SQL Server instance for the first time, it’s worth starting out by seeing how long it’s been running. While these aren’t the only three ways to check SQL Server start time, they are what I’m most familiar with using. I hope these can help you in the future as well.

Thanks for reading!