Upon further investigation, we discovered that it was a cardinality issue. The data this customer had is such that there was no date beyond today. All the dates are in the past (as it for most scenarios).

SQL Server has many built-in/intrinsic functions. During query compilation, optimizer can actually ‘peek’ the value by ‘executing’ the function to provide better estimate. For example, if you use getdate() like (“select * from t where c1 > getdate()”), optimizer will be able actually get the value of getdate() and then use histogram to obtain accurate estimate.

DateAdd is another intrinsic function that optimizer can do the same trick.

But switchoffset is not one of those intrinsic functions and optimizer can’t ‘peek’ the value and utilize histogram.

But “select * from t o where c1 > convert (datetimeoffset, dateadd (dd, 0, getdate()))” shows correct estimate. Note that the two queries are identical. But I used them to illustrate the difference in terms of cardinality estimate.

Solution

When you use switchoffset together with getdate(), it’s best when you ‘precompute’ the value and then plug it in your query. Here is an example: