SQL Server index performance hindered by variable

Question

I am using a SQL Server 2008 R2 database with a table that contains on the order of a billion rows. I want to get the distinct values of one column during the last 24 hours, so I did this (Query 1):

SELECT DISTINCT SomeField FROM SomeTable WHERE CreatedOn > '2014-01-28 12:24:00'

Note there is an index on CreatedOn, but it does not include SomeField. This returned immediately. Now, since this is a query I run often, I decided to make it dynamic, so I changed it to (Query 2):

DECLARE @StartDate DATETIME = DATEADD(DAY, -1, GETDATE())
SELECT DISTINCT SomeField FROM SomeTable WHERE CreatedOn > @StartDate

I was surprised that this query took a long time. (I stopped it after a minute or so.) Then I tried putting the variable inline like this (Query 3):

SELECT DISTINCT SomeField FROM SomeTable WHERE CreatedOn > DATEADD(DAY, -1, GETDATE())

That was fast again. Looking at the execution plans, Query 1 and 3 are identical and use an Index Seek. But Query 2 does an Index Scan and suggests that I am missing an Index on CreatedOn including SomeField.

Why does checking against a variable suddenly change the effectiveness of the Index?

Answer 1

The most likely reason is that when a value is hard coded, the compiler can use the statistics to figure out the best query to run. When using a variable, it doesn't and has to do a scan. You might see better performance if you try to create a stored proc out if it and run it that way so the server can make use of "parameter sniffing".

You can find some more info with others who have seen this issue here, here and here.