SYSDATETIME going backwards

Question

First up, I have solved the actual problem that I had, by moving to using a datetime2, and DATEDIFFing at the MICROSECOND scale. But I'm still surprised by the behaviour I was seeing, so ....

---

I'm trying to diagnose a perf issue with some SQL, and I have code that loops like this to test it:

DECLARE @timer datetime = NULL
DECLARE @diff int = 0
DECLARE @total int = 0

WHILE(<condition>)
BEGIN
    SET @timer = SYSDATETIME()
    <select statement under test>
    SET @diff = DATEDIFF(MILLISECOND, @timer, SYSDATETIME())
    SET @total = @total + @diff
END

SELECT @total

I'm getting NEGATIVE totals, though !?

How the hell is that a thing? I would understand ending up with @total = 0; that's saying that the increments are <1 ms (or close enough that the resolution of the timer reports that, anyway) so that I end up summing lots of 0s.

But it seems enormously bugged for it to possible for the DATEDIFF to return negative!

I checked further and confirmed that the @diff variable is indeed returning -1 and even -2.

What gives?

Answer 1

SYSDATETIME returns a datetime2 with 7digits of precision.

You are assigning it to a datetime variable with only precision of approx 1/300 per second. So it can get rounded up (or down but up is the cause here).

Try

DECLARE @SYSDATETIME DATETIME2 = '2017-04-15 14:49:36.001999'
DECLARE @timer DATETIME = @SYSDATETIME;
DECLARE @diff INT = DATEDIFF(MILLISECOND, @timer, @SYSDATETIME);

SELECT @timer AS [@timer],
       @diff  AS [@diff]; 

This returns

+-------------------------+-------+
|         @timer          | @diff |
+-------------------------+-------+
| 2017-04-15 14:49:36.003 |    -2 |
+-------------------------+-------+

The .001999 gets rounded to .003 and that crosses two milisecond boundaries so DATEDIFF(MILLISECOND returns -2 when comparing the rounded and non rounded values.