How to calculate last time Wait Statistics were cleared

08 February,2018 by Jack Vamvas

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'


 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to calculate last time Wait Statistics were cleared


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer