Parameter Sniffing in SQL Server

This article is an attempt to explain what Parameter Sniffing is all about and how it affects the performance of a Stored Procedure.

"Parameter Sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and es it along to the query optimizer so that they can be used to generate potentially faster query execution plans.

Do you know that the query execution plan generated by the query optimizer depends on many factors and Parameter Sniffing is just one of them? So the execution plans I show here might not be the execution plan you get if you run the same query on your server.

Creating a Stored Procedure

The following will create a Stored Procedure:

create procedure GetTestProduct(@BrandId int)

as

begin

select * from mProduct

where BRANDID = @BrandId

end

Note: Here 280 records for BrandId 1 and 55 records for BrandId 2. I will show the difference in these two BrandIds.

The procedure above will return product info depending on their brandid.

Remember that the query execution plan is not generated when you create the procedure. It is created and cached the first time you run it.

exec GetTestProduct 1 -- here 1 is Brandid with 280 rows.

Since this is the first time the Stored Procedure is called, it will create an optimized query execution plan and execute it.

Now, the Stored Procedure will return me 280 rows. Look at the query execution plan in the following image:



Now the exec with BrandId 2:

exec GetTestProduct 2 -- here 2 is Brandid with 55 rows.

Look at the query execution plan again in the following image:



Did you notice that the estimated number of rows is still 280? This plan was optimized for retrieving 280 rows (in the first run) and the plan stays in the cache for reuse until the server is restarted or the procedure is recompiled or if the proc cache is removed because of any other reason.

Now just drop the procedure and execute again.
 

drop proc GetTestProduct

create procedure GetTestProduct(@BrandId int)

as

begin

select * from mProduct

where BRANDID = @BrandId

end

Execute again with BrandId 2

exec GetTestProduct 2

Now that the plan cache is cleared, a fresh query execution plan will be generated. See in the following image:



The above execution plan comes with an estimated number of rows of 55.

This scenario exists only for Parameter Sniffing.

The same Stored Procedure can be written this way to avoid Parameter Sniffing:

create procedure GetTestProduct(@BrandId int)

as

begin

declare @LocBrandId varchar(20)

set @LocBrandId = @BrandId

 

select * from mProduct

where BRANDID = @LocBrandId

end

Now, when you run the Stored Procedure with the inputs above it will return correctly, in other words 280 estimated rows for BrandId 1and

55 estimated rows for BrandId 2.