In Azure SQL Database, no one can
hear you scream query common system objects that tell you when a restart happened. You don’t get the access you need to sys.dm_os_sys_info, sys.dm_exec_sessions, sys.traces, or sys.databases.
The closest I’ve been able to get is to query sys.dm_os_wait_stats for several common wait types that seem to correspond with clock time – meaning, for every minute on the clock, we get about one minute of these waits.
If we subtract those waits from today’s date, we can get a rough idea of when the server restarted:
SELECT DATEADD(ms, AVG(-wait_time_ms), GETDATE()) AS approximate_restart_date
FROM sys.dm_os_wait_stats w
WHERE wait_type IN ('DIRTY_PAGE_POLL','HADR_FILESTREAM_IOMGR_IOCOMPLETION','LAZYWRITER_SLEEP','LOGMGR_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_DISPATCHER_WAIT','XE_TIMER_EVENT')
I’m using an average here because each of those waits actually reports slightly different numbers as shown here:
Best I can tell, this SQL Server restarted on July 14th at around 3:09-3:16AM, and the average reports 2015-07-14 03:11:51.570.
Who cares when the server restarted? sp_BlitzFirst® cares – because folks have started asking me why their Azure SQL Database is slow, so I made it Azure-friendly. Now when you use the @Seconds = 0 parameter, you can see overall wait stats since server restart – just like your on-premise SQL Servers. (ASD also has sys.dm_db_wait_stats, but I like seeing what the server’s overall bottleneck is too.)
Updated version from Henrik Stein Poulsen
Henrik shared this newer version that uses quartiles:
-- median in SQL Server:
select dateadd(s, (- max(my_column) / 1000), GETDATE()) AS Median_restart_date, decile
from (select wait_time_ms as my_column, ntile(10) over (order by wait_time_ms) as decile
where wait_type in (N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'LAZYWRITER_SLEEP',N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',N'REQUEST_FOR_DEADLOCK_SEARCH',N'HADR_TIMER_TASK',N'CHECKPOINT_QUEUE',N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',N'XE_TIMER_EVENT',N'PVS_PREALLOCATE',N'QDS_ASYNC_QUEUE',N'SP_SERVER_DIAGNOSTICS_SLEEP',N'PWAIT_EXTENSIBILITY_CLEANUP_TASK',N'DIRTY_PAGE_POLL',N'FT_IFTS_SCHEDULER_IDLE_WAIT')
where decile between 3 and 7
group by decile