sql server - Please confirm: SYSDATETIME() is slower than GETDATE() in WHERE clause -

i have query returns several thousand records, joined across several tables. in clause, date checked no more 2 months in past. @ first, query had date boundary set variable , used that.

declare @startdate date = dateadd(month, -2, sysdatetime())  select [....] dateinquestion >= @startdate 

this runs fine (returns result expected in under 4 seconds), wanted rid of variable declaration , move assignment clause itself:

select [....] dateinquestion >= dateadd(month, -2, sysdatetime()) 

this runs on minute , eats cpu. killed query stop pushing server, no results given. changing getdate() (i don't need precision sysdatetime() anyway) speeds things up:

select [....] dateinquestion >= dateadd(month, -2, getdate()) 

results similar scenario 1.

i believe because sysdatetime evaluated on per-row-basis because processing row costs several nanoseconds, significant sysdatetime. getdate however, having higher change-threshold, unaffected (or less affected) , not change - or need re-evaluating - on per-row-basis.

can confirm? assumption concerning behaviour correct?

i've searched this, couldn't find except this, concerns assigning sysdatetime() variable, not using in where: does sysdatetime() cost more getdate()?

also this, getdate used in example: tsql datetimes functions in clause

the important difference between getdate , sysdatetime type of returned value. sysdatetime not evaluated each row, same getdate not evaluated each row. runtime constant functions see https://dba.stackexchange.com/questions/18459/does-sql-server-evaluate-functions-once-for-every-row

what type of dateinquestion column?

when used @startdate variable converted result of sysdatetime date. when don't use variable, result of dateadd has different types in examples.

to make both queries getdate , sysdatetime equivalent can cast date explicitly:

compare

select [....] dateinquestion >= cast(dateadd(month, -2, sysdatetime()) date) 

vs

select [....] dateinquestion >= cast(dateadd(month, -2, getdate()) date) 

i'd surprised if these 2 queries ran differently.

ideally should cast them same type dateinquestion has.

you said variant sysdatetime uses lot of cpu. if dateinquestion has type datetime, possible explanation this. seems in variant values dateinquestion column implicitly converted datetime2(7) type before comparison. each row. first, uses cpu. second, may prevent optimizer using index on column (if there index).

to see going on, rather guessing, compare actual execution plan of both variants.

by way, variant variable not equivalent inlined variant optimizer. optimizer doesn't know value of variable, knows value of getdate() , sysdatetime(), cardinality estimations different, can lead different plans , different performance.