Variable Sniffing?

Question

This might be dumb and feel like I am going back trying to understanding basics.

So I create a test table like below and create a clustered index on it

create table test( c1 int)

DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT
SET @Lower = 1 
SET @Upper = 10000 
while 1=1
begin
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
insert into test SELECT @Random
end 

create clustered index cidx on test(c1)

Now Im running the below query with actual execution plan on

DECLARE @Min INT

SET @Min = 216 --selected this cause this was a histogram step

select * from test  where c1 = @Min
select * from test  where c1 = @Min option(recompile)

So for the first query the behavior is as expected, the estimated number of rows is calculated from density vector.

Seek Predicates - Seek Keys1: Prefix: [db].[dbo].[test].c1 = Scalar Operator([@Min])

But for the second query it looks like sql server can sniff the value with option(recompile). I thought SQL Server cannot sniff variables even if we use option recompile?

Seek Predicates - Seek Keys1: Prefix: [DB].[dbo].[test].c1 = Scalar Operator((216))

So as you can see from the estimated number of rows, the first one is 3.2511 which came from the density vector and for the second one the estimated number of rows of 7 comes from the histogram.

So is it true that SQL Server can sniff the variable when we recompile adhoc query or is it something that I do not understand?

Answer 1

This behavior is document in the Query Hints topic:

When compiling query plans, the RECOMPILE query hint uses the current values of any local variables in the query and, if the query is inside a stored procedure, the current values passed to any parameters.