Parameter Caching: Parameter Sniffing with Adhoc Select Query

Read Previous Post: Plan Caching VI - Forced Parameterization in Adhoc Queries

Here we will see, how a query makes a difference in Execution plan when it has Parameter passed in where predicate. Whenever you are firing any query the plan is generated and Cached in Memory, but it doest not store the context of the execution i.e (plan based on Parameter passed).

Sometime when we use parameter to pass in where condition, the actual value of parameter is not known at compile time due to which the optimal Execution plan is not selected by SQL Optimizer, this is because query is compiled before actual value of parameter is known.

Lets check out with a small practical

create table ParameterSniffingDemoTB

(id int identity(1,1) primary key,

col2 datetime,

col3 numeric(28,10) not null)  

Command(s) completed successfully.

Lets insert some rows in the table

declare @i int = 0

begin tran

while @i < 50000

begin  insert into ParameterSniffingDemo_TB(col2,col3)

     select '20140616', 1000*rand()

     set @i=@i+1

end  

set @i=0

while @i < 5

begin

     insert into ParameterSniffingDemo_TB(col2,col3)

     select '20140617', 1000*rand()

     set @i=@i+1

end

commit tran 

Now since our table is populated with data, Lets create a non clustered index on col2

create nonclustered index [ix_col2]

on [ParameterSniffingDemo_TB] ([col2] asc)

Command(s) completed successfully.

Non Clustered Index is on place now. Lets query on the basis of col2 and see whether Non Clustered index participate in Execution plan.

select SUM(col3) FROM ParameterSniffingDemo_TB WHERE col2='20140617'

Here we can see Non cluster Index participated in the execution plan.

Now lets declare a parameter variable and check whether it will use the existing Non Clustered index.

declare @mydate datetime = '20140617'

select sum(col3) FROM ParameterSniffingDemo_TB WHERE col2=@mydate

Here we can see the Non clustered index is not taken up by Optimizer while creating Plan for the Query.

Lets check the comparative cost of the query, for this we are running both query in a batch

--First Query with out Parameter Variable

select sum(col3) FROM ParameterSniffingDemo_TB WHERE col2='20140617'

--Second Query with local Parameter Variable

declare @mydate datetime = '20140617'

select sum(col3) FROM ParameterSniffingDemo_TB WHERE col2=@mydate  --option(recompile)

Here we can see the Cost of query with value as parameter choose optimal plan with just 6% of total to execute whereas Query with Parameter variable choose non optimal plan.  

This problem is called Parameter Sniffing.

Workaround Solution :- Option (Recompile) Hint

declare @mydate datetime = '20140617'

select sum(col3) FROM ParameterSniffingDemo_TB WHERE col2=@mydate  option(recompile)

It’s your smile which makes me smile, let me smile and do my duty towards my code, Keep smiling my dear! J

 

Post Reference: Vikram Aristocratic Elfin Share