Essentially, the problem is that a poor estimate can be made not simply when
SYSUTCDATETIME()) appears, as Erland originally reported, but when any
datetime2expression is involved in the predicate (and perhaps only when
DATEADD()is also used). And it can go both ways – if we swap
<=, the estimate becomes the whole table, so it seems that the optimizer is looking at the
SYSDATETIME()value as a constant, and completely ignoring any operations like
DATEADD()that are performed against it.
Paul shared that the workaround is simply to use a
datetimeequivalent when calculating the date, before converting it to the proper data type. In this case, we can swap out
SYSUTCDATETIME()and change it to
I suppose switching to GETUTCDATE isn’t too much of a loss, but it looks like (according to Paul White in the second linked Connect item) this appears to have been fixed in SQL Server 2014.