switchoffset built-in function can cause incorrect cardinality estimate

Recently, we received a call from a customer reported that a query was slow.  Upon further investigation, his query has a predicate that look like this:

select * from t o where c1 >switchoffset (Convert(datetimeoffset, GETDATE()), '-04:00')

 

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.

 

Just to compare the difference, query “select * from t o where c1 >switchoffset (Convert(datetimeoffset, GETDATE()), '-04:00')” shows incorrect estimate (74397 rows).

 

 

 

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:

declare @dt datetimeoffset = switchoffset (Convert(datetimeoffset, GETDATE()), '-04:00')
select * from t  where c1 > @dt option (recompile)

Complete demo script

 

if object_id ('t') is not null
drop table t
go
create table t (c1 datetimeoffset)
go
declare @dt datetime, @now datetime
set @dt = '1900-01-01'
set @now = SYSDATETIMEOFFSET()
set nocount on
begin tran
while @dt < @now
begin
insert into t values (@dt)
insert into t values (@dt)
insert into t values (@dt)
insert into t values (@dt)
insert into t values (@dt)
insert into t values (@dt)
set @dt = dateadd (dd, 1, @dt)
end
commit tran
go
create index ix on t (c1)
go

set statistics profile on
go
--inaccurate estimate
select * from t  where c1 >switchoffset (Convert(datetimeoffset, GETDATE()), '-04:00')
--accurate estimate
select * from t  where c1 > convert (datetimeoffset, dateadd (dd, 0, getdate()))
--accurate estimate
declare @dt datetimeoffset = switchoffset (Convert(datetimeoffset, GETDATE()), '-04:00')
select * from t  where c1 > @dt option (recompile)

go
set statistics profile off

 

 

Jack Li | Senior Escalation Engineer |Microsoft SQL Server Support