Parameter Sniffing in SQL Server

Introduction

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

Parameter Sniffing in SQL Server 

Parameter Sniffing refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation and es them 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.

How to Create 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 are 280 records for BrandId 1 and 55 records for BrandId 2. I will show the difference between these two BrandIds.

The procedure above will return product info depending on the 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. 

Now, the Stored Procedure will return 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, the procedure is recompiled, or the proc cache is removed because of any other reason.

Now drop the procedure and execute it 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 new query execution plan will be generated. See the following image.

The above execution plan comes with an estimated number of rows, 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 1 and 55 estimated rows for BrandId 2.

Summary

This article taught us about Parameter Sniffing and how it affects the performance of a Stored Procedure in an SQL Server.

Reference

• Stored Procedure in SQL Server