SQL Server - Variable substitution slow

Question

I have a table "my_table" with 20 million entries. The id-column is indexed. If i do the following query it takes less than 1 sek.

SELECT * FROM my_table WHERE id='asdf'

If I do the same query but with a temporary variable in the where clause.

declare @ID nvarchar(4)
set @ID = N'asdf'
SELECT * FROM my_table WHERE id=@ID

it takes ~14 sek.

Execution plan with temporary variable:

select <- Clustered Index Scan

Execution plan with constant:

select <- Nested Loops <- Index Seek (Non clustered)
                       <- Key Lookup (Clustered)

First question: Why is the first query taking that much longer to execute?
SQL Server Profiler tells me that the first query does ~800000 reads (cpu 5258) while the second does 25 reads (cpu 0).

Second question: If I apply OPTION(RECOMPILE) to the first query, execution will run fast and the execution plan is like the second but I don't understand why.
Is the variable evaluated for every entry in the seek?

Answer 1

When SQL Server decides between using a Clustered Index Scan or a Index Seek with Key Lookup's it looks at the estimated number of rows that will be returned.

When you provide the value as a constant in the query, SQL Server can use that value and look in the statistics of the index to see roughly how many rows that value will return and in your case the value asdf will be selective enough so the seek/lookup will be the fastest way to get the data.

When you are using a variable to hold the value, SQL Server (when building the queryplan) does not see what value the variable eventually will have on execution. In that case the estimated number of rows will be some average number of rows returned based on the statistics for the index on id. In your case, that estimated number of rows is too high for SQL Server to consider a seek/lookup queryplan.

When you use option (recompile) the value of the parameter is known when the queryplan is created so SQL Server can use the actual value when estimating the number of rows.

If you decided to use stored procedure with @id is a parameter then SQL Server is able to use the value of @id at compile time (parameter sniffing) and give you the best plan for that value. Note that the query plan for the stored procedure is cached so if you later on calls the procedure with a value that would be faster with a scan it will still execute the query with a seek/lookup. And of course if your parameter value to the procedure on first execution will return many rows, the plan for the stored procedure will do a scan and continue to do so as long as the plan is in the cache.