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?