Do values in `sys.dm_os_wait_stats` reset to 0 or stop accumulating when they hit the max value?

When looking at sys.dm_os_wait_stats, the following columns are defined as BIGINT

• waiting_tasks_count

• wait_time_ms

• max_wait_time_ms

If any of those values go above 9,223,372,036,854,775,807, do the counters reset to 0, or simply stop counting?

It's unclear from the table definition what goes on behind the scenes:

sp_helptext 'sys.dm_os_wait_stats'

Returns:

CREATE VIEW sys.dm_os_wait_stats AS  
 SELECT *  
 FROM OpenRowset(TABLE SYSWAITSTATS)

So that's a bit of a black box.

There are some DMVs where large numbers can turn negative. One example of this is the total_elapsed_time column in dm_exec_requests.

According to Docs:


In SQL Server the wait-time counters are bigint values and therefore
are not as prone to counter rollover as the equivalent counters in
earlier versions of SQL Server.

I've seen this with earlier version of SQL Server where rollover happened and you'd get negative numbers because the values are signed.

I took a look at the source code and indeed these are 8-byte signed values. Since this is all C++, I created a simple repro:

#include "stdint.h"
#include 

using namespace std;

int main()
{
    int64_t i8 = 0;
    cout << "Size of integer: " << sizeof(i8) << " bytes." << endl;
    cout << "Max Value: " << INT64_MAX << endl;
    i8 = INT64_MAX + 1;
    cout << "Value of MAX + 1: " << i8 << endl;
    cin;
    return 0;
}

The output is similar to what I experienced in previous versions of SQL Server, the signed value is rolled over. Note that if you compile this, you should get a compiler warning that there will be rollover - I received compiler warning 4307.

Output of above:

Size of integer: 8 bytes.
Max Value: 9223372036854775807
Value of MAX + 1: -9223372036854775808

I'm NOT stating this is exactly how SQL Server works in all and every case as there are many nuances... but this should be a similar approximation.

What does this all really mean?

I highly doubt, as Aaron has said, that you'll ever encounter rollover on the 64-bit values in the current hardware climate. If you did, it would be easy to spot and really be more of reporting inaccuracy than any real issue in my opinion.

StackExchange Database Administrators Q#186437, answer score: 9