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


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


Comments (3)
  1. Jeremiah Peschka says:

    Couldn't we just do something like <code>SELECT CAST(total_elapsed_time & 0x7fffffffffffffff AS DECIMAL(38,0))</code> rather than using a CASE?

  2. Not exactly, ANDing a bitmask which only has its leftmost (sign) bit set to zero to either 2147483648 or 0, would produce zero as a result which is not exactly what I'm pursuing with the CASE above.

  3. Nicole Garris says:

    Can you indicate what versions of SQL Server this impacts?

Comments are closed.

Skip to main content