I have a table with roughly 200 million rows. The table contains a number of columns, but at the moment only the primary key and a non clustered index based on the datetime column for indexes.
This first query will return zero rows, in less than a second.
WHERE GenericDate > '01-01-1753' AND GenericDate <= '01-29-1753'
DECLARE @startDate DATETIME, @endDate DATETIME
SET @startDate = '01-01-1753'
SET @endDate = '01-29-1753'
WHERE GenericDate > @startDate AND GenericDate <= @endDate
Select <- Nested Loops (Inner Join) - 0% <- Index Seek (NonClustered) - 0%
<- Key Lookup (Clustered) - 100%
Select <- Parallelism (Gather Streams) - 10% <- Clustered Index Scan (Clustered) - 90%
It's a well known issue with execution plan caching and parameter sniffing.
Quoting from this article
A while back I wrote about parameter sniffing, the situation where SQL compiles and caches an execution plan that is appropriate for a certain value of a parameter, but is non optimal for other values. There’s another side to parameter sniffing though – when the optimiser can’t sniff at all.
When a batch is submitted to the optimiser, the value of any parameters (from stored procedure, auto parametrisation or sp_executesql) are known to the optimiser. So are the value of any constants used in the SQL statement. The value of local variables, however, are not.
When the value of a variable used in the where clause is not known, the optimiser has no idea what value to use to estimate the number of affected rows. Hence, it has no idea how many rows from the table will satisfy the condition.