Date Parameter causing Clustered Index Scan

Question

I have the following query

DECLARE @StartDate DATE = '2017-09-22'
DECLARE @EndDate DATE = '2017-09-23'

SELECT a.col1,
       a.col2,
       b.col1,
       b.col2,
       b.col3,
       a.col3
FROM   TableA a
       JOIN TableB b
           ON b.pred = a.pred
WHERE  b.col2 > @StartDate AND b.col2 < @EndDate

When I run this and inspect the actual execution plan, I can see that the most costly operator is a clustered index scan (The index is on a.pred)

However, if I change the query as follows

SELECT a.col1,
       a.col2,
       b.col1,
       b.col2,
       b.col3,
       a.col3
FROM   TableA a
       JOIN TableB b
           ON b.pred = a.pred
WHERE  b.col2 > '2017-09-22' AND b.col2 < '2017-09-23'

The index scan is eliminated and an index seek is used.

Can someone explain why this is? In my mind, this is something to do with the fact that the value in the variable could be anything so SQL doesn't know how plan the execution.

Is there any way I can eliminate the table scan but still have use of the variable? (PS, this will be converted to a stored procedure which takes @StartDate and @EndDate as parameters)

EDIT

col2 is DATETIME, however, if I make my variable DATETIME the problem still persists

Try your query with `OPTION(RECOMPILE)` and do you get the index seek? — SQLChao, Oct 20 '17 at 16:09
Please take a look at this [Index Scan on Parameterized values](https://stackoverflow.com/questions/27564852/why-is-sql-server-using-index-scan-instead-of-index-seek-when-where-clause-conta) — DataWrangler, Oct 20 '17 at 16:13
Thanks @SQLChao that has fixed it. I'll have a read of the link from Joby as that seems to cover how it works in detail. Thanks — SE1986, Oct 20 '17 at 16:15
Yes, if you search for parameter sniffing there's a lot of articles that go into great detail which can help especially since you will be using a stored proc. — SQLChao, Oct 20 '17 at 16:16
Possible duplicate of [Why is SQL Server using index scan instead of index seek when WHERE clause contains parameterized values](https://stackoverflow.com/questions/27564852/why-is-sql-server-using-index-scan-instead-of-index-seek-when-where-clause-conta) — Paul Maxwell, Oct 21 '17 at 00:11

pkuderov · Accepted Answer · 2017-11-20T17:01:44.673

SQL makes plans reusable for variables.

When you use variables - it compiles query without knowing actual values you'll pass. Even in this sql batch values are known. But it won't need to recompile query for another set of passing arguments.

So if you hardcode values - DB compiles it chosing the plan optimized for these particular values (e.g., it guesses expected number of rows passed date check). It'd be 'at least not worse' than when you use variables. But DB needs to recompile it for another set of hardcoded values (because text of the query is changed), which takes time and litters compiled query cache storage superseding the other useful queries.

As of:

Is there any way I can eliminate the table scan but still have use of the variable? (PS, this will be converted to a stored procedure which takes @StartDate and @EndDate as parameters)

I think non-clustered index on b.col2 maybe be the solution. The key of this index may also contain b.pred as a part of surrogate key or as including (with include(pred)).

score 0 · Answer 2 · answered Nov 20 '17 at 14:57

This query has variables, the advice in this question about sql server not knowing the value of your variables and therefore has to develop a plan based on guessed size of the result set is relevant to your question.

Why is SQL Server using index scan instead of index seek when WHERE clause contains parameterized values

However, you mentioned that you are going to convert this code to a stored procedure. At the time of converting it to a stored procedure the query optimizer should be able to sniff the value of the variables and develop and execution plan off of them. Try converting it to a sproc and executing it. The query plan should improve under those conditions.

Date Parameter causing Clustered Index Scan

2 Answers2

Linked