Variable Sniffing?

• jesijesijesi

  Right there with Babe

  Points: 761

  More actions

  January 31, 2017 at 2:30 am

  #326472

  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-2 INT

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

  select * from test where c1 = @min-2
select * from test where c1 = @min-2 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?

• Adi Cohn-120898

  SSC-Dedicated

  Points: 33944

  More actions

  January 31, 2017 at 3:36 am

  #1925791

  jesijesijesi - Tuesday, January 31, 2017 2:30 AM

  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-2 INT

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

  select * from test where c1 = @min-2
select * from test where c1 = @min-2 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?

  The creation of a query plan is done before query's execution.  When SQL Server creates the query plan, it creates a plan for every statement that touches the data, and ignores all other statements.  So suppose tha have this batch:
  
DECLARE @v-2 INT;

  SET @v-2 = 10;

  SELECT *
FROM MyTable
WHERE MyCol = @v-2

   SQL Server will create a plan for the batch.  It will ignore the first 2 statements (declaring the variable and giving it a value) because they don't touch the data and it doesn't need to find an optimized way to execute them.  Then it gets to the select statement.  Since it doesn't know the value of @v-2 because it ignored it and the value is set at execution time, it has no idea what would be the value of @v-2 and it uses the density vector.  If now I modify the code and add the option recompile to the select statement, my batch will look like that:
  
DECLARE @v-2 INT;

  SET @v-2 = 10;

  SELECT *
FROM MyTable
WHERE MyCol = @v-2
option (recompile)

   SQL Server will create a query plan for the batch just like before, but now after it will start executing the batch, it will get to the select statement and then it will compile for that statement a new plan.  Since this is done while executing the batch and not before, it now knows the value of @v-2 and it can use this value while it generates the query plan.
  Adi

  --------------------------------------------------------------
  To know how to ask questions and increase the chances of getting asnwers:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/