Figuring Out When Wait Statistics Were Last Cleared

One of the topics covered during IE2 is Waits and Queues, and this week one of the attendees asked if there was a way to know when wait statistics were last cleared. It turns out there is a post about it on the SQLOS & Cloud Infrastructure Team Blog, and what you do is look for the SQLTRACE_INCREMENTAL_FLUSH_SLEEP wait_type in sys.dm_os_wait_stats. The value for wait_tims_ms is the time since wait stats were last cleared.

Since I talk about clearing wait stats in my DBCC session I decided this information would be useful to include, and then decided to just write a query to figure it out.  The query is below, as is a query to show the last time the instance was restarted.  Edit: Note that this query is only for SQL Server 2008R2 and higher.  The SQLTRACE_INCREMENTAL_FLUSH_SLEEP wait_type did not exist in previous versions of SQL Server.  If I can find an alternate wait_type for use, I will post back.

Just to be clear, I am not advocating that you immediately clear wait stats on your server.  In fact, I’d be more interested to know whether wait stats have been cleared since the instance was last restarted.  Let me back up a bit…  If you’re not familiar with wait statistics, I suggest starting with Paul’s post, Wait statistics, or please tell me where it hurts.  Whether you have performance issues on your server or not, I highly recommend looking at wait stats regularly to understand what your “normal” waits are.  If you are having issues, waits can be a starting point to sorting out what’s causing the problem.  But wait stats should not be viewed in a vacuum – always use other counters or information to correlate findings.  Once you have a basic understanding of your wait stats, you may decide to capture them on a regular basis.  But wait stats are cumulative, and will only reset when you restart the instance, or if you clear them manually.  They can be cleared using:

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

There are a variety of reasons to clear wait stats, but it shouldn’t be done arbitrarily.  You may clear them because you’ve made a change to the system and want to understand if and how that change affects wait statistics.  You may clear wait stats every morning at 5 AM because backups and other maintenance tasks run at night, but you want to understand performance during business hours when users are the system and therefore decide to filter out (or investigate separately) waits from those tasks.  In the latter case, you probably have a scheduled job to clear the wait stats, and other DBAs may or may not know about this job.  If another DBA decides to clear wait stats for some reason, without telling you, they may affect the wait information you’ve been capturing.  As such, I would always verify with colleagues whether or not wait stats are cleared regularly and this script will give you an idea of whether are being cleared or not.  However, even if they haven’t been cleared since the last restart, I would still perform some due diligence and verify it won’t alter anyone else’s data.  I am interested to hear different ideas for how this information could be used, so feel free to post back or email me.  And if you’re not currently looking at your wait stats regularly, please start!

/* when were wait stats last cleared? */
SELECT
[wait_type],
[wait_time_ms],
DATEADD(ms,-[wait_time_ms],getdate()) AS [Date/TimeCleared],
CASE
WHEN [wait_time_ms] < 1000 THEN CAST([wait_time_ms] AS VARCHAR(15)) + ' ms'
WHEN [wait_time_ms] between 1000 and 60000 THEN CAST(([wait_time_ms]/1000) AS VARCHAR(15)) + ' seconds'
WHEN [wait_time_ms] between 60001 and 3600000 THEN CAST(([wait_time_ms]/60000) AS VARCHAR(15)) + ' minutes'
WHEN [wait_time_ms] between 3600001 and 86400000 THEN CAST(([wait_time_ms]/3600000) AS VARCHAR(15)) + ' hours'
WHEN [wait_time_ms] > 86400000 THEN CAST(([wait_time_ms]/86400000) AS VARCHAR(15)) + ' days'
END [TimeSinceCleared]
FROM [sys].[dm_os_wait_stats]
WHERE [wait_type] = 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP';

/* check SQL Server start time - 2008 and higher */
SELECT
[sqlserver_start_time]
FROM [sys].[dm_os_sys_info];

8 thoughts on “Figuring Out When Wait Statistics Were Last Cleared

  1. I tried this on a couple of SQL 2005 servers and they don’t return the ‘SQLTRACE_INCREMENTAL_FLUSH_SLEEP’ wait type. The only wait types with FLUSH in the name are listed below.

    wait_type wait_time_ms
    SLEEP_BPOOL_FLUSH 3340843
    SQLTRACE_BUFFER_FLUSH 766717359
    LOGMGR_FLUSH 0

  2. Hm, ok, that would suggest that wait_type did not exist in 2005. Let me research and post back, thanks for letting me know!

  3. I tried this on a server that hasn’t been rebooted in a while and got an overflow error converting to int. If the server hasn’t been rebooted in more than 2,147,483,647ms it can’t convert it to an int for the dateadd function. Reducing the precision down to seconds took care of it:

    DATEADD(SS,-wait_time_ms/1000,getdate()) as "Date/TimeCleared"

  4. Nice trick.

    For SQL Server 2005 you could similarly leverage the SQLTRACE_BUFFER_FLUSH wait type. In my tests I don’t show it to be nearly that exact, but fairly close.

    Also, the default trace might have much more information than just the time elapsed – use something like

    SELECT * FROM ::fn_trace_gettable(‘<path_to_default_trace>’, default)
    WHERE TextData LIKE ‘%DBCC%SQLPERF%’

    Obviously, this would only work if the default trace has not rolled over more than the threshold since the last wait stats clear.

  5. Found your query very helpful, however on one server (@@VERSION = Microsoft SQL Server 2012 – 11.0.5058.0 (X64)
    May 14 2014 18:34:29
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.3 (Build 9600: ))

    It always returns 36 seconds. Below are a couple of examples run a few seconds apart.
    SQLTRACE_INCREMENTAL_FLUSH_SLEEP 36013 2017-01-13 16:20:21.233 36 seconds
    SQLTRACE_INCREMENTAL_FLUSH_SLEEP 36013 2017-01-13 16:20:45.620 36 seconds

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.