Why is a T-SQL variable comparison slower than GETDATE() function-based comparison?

Question

I have a T-SQL statement that I am running against a table with many rows. I am seeing some strange behavior. Comparing a DateTime column against a precalculated value is slower than comparing each row against a calculation based on the GETDATE() function.

The following SQL takes 8 secs:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
DECLARE @TimeZoneOffset int = -(DATEPART("HH", GETUTCDATE() - GETDATE()))
DECLARE @LowerTime DATETIME = DATEADD("HH", ABS(@TimeZoneOffset), CONVERT(VARCHAR, GETDATE(), 101) + ' 17:00:00')
SELECT TOP 200 Id, EventDate, Message 
FROM Events WITH (NOLOCK)
WHERE EventDate > @LowerTime
GO

This alternate strangely returns instantly:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
SELECT TOP 200 Id, EventDate, Message 
FROM Events WITH (NOLOCK)
WHERE EventDate > GETDATE()-1
GO

Why is the second query so much faster?

EDITED: I updated the SQL to accurately reflect other settings I am using

Answer 1

After doing a lot of reading and researching, I've discovered the issue here is parameter sniffing. Sql Server attempts to determine how best to use indexes based on the where clause, but in this case it isnt doing a very good job.

See the examples below :

Slow version:

declare @dNow DateTime  
Select @dNow=GetDate()  
Select *  
From response_master_Incident rmi  
Where rmi.response_date between DateAdd(hh,-2,@dNow) AND @dNow  

Fast version:

Select *  
From response_master_Incident rmi  
Where rmi.response_date between DateAdd(hh,-2,GetDate()) AND GetDate()  

The "Fast" version runs around 10x faster than the slow version. The Response_Date field is indexed and is a DateTime type.

The solution is to tell Sql Server how best to optimise the query. Modifying the example as follows to include the OPTIMIZE option resulted in it using the same execution plan as the "Fast Version". The OPTMIZE option here explicitly tells sql server to treat the local @dNow variable as a date (as if declaring it as DateTime wasnt enough :s )

Care should be taken when doing this however because in more complicated WHERE clauses you could end up making the query perform worse than Sql Server's own optimisations.

declare @dNow DateTime

SET @dNow=GetDate()

Select ID, response_date, call_back_phone 
from response_master_Incident rmi
where rmi.response_date between DateAdd(hh,-2,@dNow) AND @dNow

-- The optimizer does not know too much about the variable so assumes to should perform a clusterd index scann (on the clustered index ID) - this is slow

-- This hint tells the optimzer that the variable is indeed a datetime in this format (why it does not know that already who knows)
OPTION(OPTIMIZE FOR (@dNow = '99991231'));