Question: I’m trying to figure out how to calculate when SQL Server wait statistics were last cleared. ?

My goal is to try and understand performance patterns on some SQL Servers. I have some automated processes to gather wait statistics , but it’s not always clear if any other DBAs are running the DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR) command

Is there a dependable way to produce this timing estimate?

Answer: An effective method to estimate the last time wait statistics was cleared is to use the SQLTRACE_INCREMENTAL_FLUSH_SLEEP wait type.

When you execute this sql statement you’ll get back some details.One of the columns is the wait_time_ms column. The wait_time_ms column is the cumulative amount of wait time registered for all tasks encountering this type of wait since the SQL Server services were restarted.

select * from sys.dm_os_wait_stats WHERE wait_type = 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'  

Every time DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR) is executed , the wait_time_ms column value is returned back to zero and then keeps incrementing until the next time it is executed.

That’s some background information , leading to the example query. Using the DATEADD function , take the current date time GETDATE()   and then subtract the  wait_time_ms  of SQLTRACE_INCREMENTAL_FLUSH_SLEEP .

SELECT DATEADD(ms, -wait_time_ms, GETDATE()) AS [Last time wait stats cleared] 
FROM sys.dm_os_wait_stats
WHERE wait_type = 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'