sys.dm_exec_requests showing negative values for total_elapsed_time, wait_time, or any other column it exposes as an integer (int) data type

  • Article

In the case of that DMV (and probably in many other) the problem is that the internal value which populates the column (total_elapsed_time, or open_transaction_count, or open_resultset_count for that matter) is returned as a ULONG (32bit unsigned integer covering a range of values spanning 0 to 4294967295 decimal.)

However, the GetRow method of the internal table valued function sysrequests grabs that value and stores it into a CXVariant of type XVT_I4 which corresponds to a 32 signed integer covering the range from -2147483648 through 2147483647.

Therefore the following sequence of bits
01111111 11111111 11111111 11111111
both as a ULONG or a LONG represents the same decimal value: 2147483647.

But adding one to it results in
10000000 00000000 00000000 00000000
which as ULONG represents decimal 2147483648, but as a LONG represents -2147483648.

So, as a workaround, for columns with that problem you can refer to them like this:

case when total_elapsed_time < 0 then 2147483647+(2147483649+total_elapsed_time) else total_elapsed_time end as total_elapsed_time

and that way you always get the correct unsigned positive integer representation of whatever value, even when the DMV returns a negative signed representation.

Thanks,

Nacho