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.)