SQL Server start time, wait stats reset

The values for number of waiting jobs and accumulated wait time for each sys.dm_os_wait_stats wait_type continue to grow over time.  The value resets with a SQL Server restart or with an explicit command:

DBCC SQLPERF("sys.dm_os_latch_stats" , CLEAR)

When an AlwaysOn Availability Group replica changes status, it also means a shift in the wait stat start time and the wait stats themselves (difference SQL Server instance may have a very different profile).  

There are various ways to identify the SQL Server instance start time.  The query below retrieves data from sys.dm_os_sys_info and from tempdb - two common estimates of SQL Server instance start.  I've heard that sys.dm_os_sys_info isn't available in Azure - I'm personally too timid to be cloudin' right now :)  I added to the query an estimate I'm fond of - the (minimum) SQL Server transaction log writer start time.

SELECT subQ.currentTime,
       subQ.sys_info__start,
       subQ.Lgwr_start,
       subQ.tempdb_crdate,
       dateadd(second,
               0 - subQ.sqltrace_wait_ms/1000,
               subQ.currentTime)              AS sqltrace_wait_est_start
FROM   (SELECT getdate()                      AS currentTime,
               (SELECT sqlserver_start_time
                FROM sys.dm_os_sys_info)      AS sys_info__start,
               (SELECT crdate
                FROM sysdatabases
                WHERE name = 'tempdb')        AS tempdb_crdate,
               (SELECT MIN(start_time)
                FROM sys.dm_exec_requests
                WHERE COMMAND = 'LOG WRITER') AS Lgwr_start,
               ws.wait_type, 
               ws.wait_time_ms                AS sqltrace_wait_ms
        FROM   sys.dm_os_wait_stats           AS ws
        WHERE  wait_type = 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
                                              AS subQ

There's another estimate in there: sqltrace_wait_est_start.

A clever idea - find a wait type exclusive to a system process that spends almost all of its time sleeping.  The accumulated wait time for that wait type is then approximately the amount of time since wait stats were last reset - whether due to a SQL Server restart or due to an explicit command to clear the wait stats.

This is an idea I owe to the following blog post.

When was sys.dm_os_wait_stats last cleared?

http://blogs.msdn.com/b/sqlosteam/archive/2011/11/12/when-was-sys-dm-os-wait-stats-last-cleared.aspx

Now - this doesn't directly help to identify a status change in an Availability Group.  But if grabbing wait stats at regular intervals, the estimated instance and wait stats start times can be retrieved, too.  Changes in these estimated start times can then be responded to accordingly when reviewing wait stats.