What is Parameter Sniffing in SQL Server?

Sometimes in SQL Server, there is a big performance problem like one stored procedure works well for one set of input parameters but it does not execute properly for another set of parameters. This problem could be related to parameter Sniffing.  In this article, we will try to understand What is Parameter Sniffing in SQL Server and How to resolve parameter sniffing.

What is Parameter Sniffing in SQL Server?

Whenever any SQL statement or stored procedure is executed for the first time, the SQL Server optimizer generates an execution plan for that statement but this plan is based on values passed as parameters to the SQL statement or Stored Procedure for the first time. This Plan is stored in the plan cache so that whenever the same query is passed for execution, even if it’s with a different variable value, the same plan could be used. This is done by the optimizer to reduce the consumption of resources required for generating the Plan. This is called Parameter Sniffing.

How does Parameter Sniffing work?

Consider a SQL passed for execution to the optimizer, now if the same SQL statement or stored procedure will be executed again with a different set of parameters. Optimizer will force the already present execution plan on SQL statements/SPs with new parameters. But this is not always good or bad for the system.

Why is Parameter Sniffing important to know?

We must know about the concept of parameter sniffing so that over a period of time, we can evaluate if this is causing any issues or not. It will depend on various factors like indexes created on that table, Cardinality in the column used in the where clause. We will get to know more about it when we will go through an example in the next section. It may cause issues like Slower Query Execution or Increased Resource Consumption.

Issues Caused by Parameter Sniffing

Performance Issues

Due to Parameter Sniffing, there could be major performance issues in the execution of any query. Let’s say a SQL or SP is executed with a certain set of parameters for the first time in the SQL server, the optimizer will create the PLAN based on these values. Let’s consider the below example to understand this.

--Creating a sample table
drop table emp;
create table emp (empid int identity(1,1), empName varchar(10), dept varchar(10))

-- inserting data in the table
declare @i bigint 
   set @i = 1
begin 
   while @i <56246
   begin
       insert into emp (empName,dept) values(concat('emp',@i), 'sales');
       set @i = @i+1;
   end 
end

insert into emp (empName,dept) values('em122', 'hr');
insert into emp (empName,dept) values('em123', 'hr');
insert into emp (empName,dept) values('em124', 'hr');
insert into emp (empName,dept) values('em125', 'hr');
insert into emp (empName,dept) values('em126', 'hr');
insert into emp (empName,dept) values('em223', 'admin');
insert into emp (empName,dept) values('em324', 'admin');
insert into emp (empName,dept) values('em225', 'admin');
insert into emp (empName,dept) values('em226', 'admin');

Now let’s check the records present in the sample table TAB1.

So here we have 56245 records for dept as ‘sales’, 5 records for dept as ‘hr’ and just 4 records for dept as ‘admin’.

Now let’s create an index on column DEPT.

drop index emp.idx_dept;
create clustered index idx_dept on emp(dept); 

Now let’s create a Stored procedure for fetching the data out of this table emp based on dept.

create procedure sp_test 
@param1 varchar(10)
as 
begin 
select * from emp where dept = @param1;
end;

Here comes the most important part, when we will execute this sp. Optimizer will create a plan for this, based on the first value passed as a parameter. Let’s execute the procedure using the option of “Include actual execution plan”.

exec sp_test 'sales';

Here is the actual execution plan.

Let’s look at the Index Seek Scan Details.

So in the index seek-scan, it estimated total rows as 56245, and the actual rows are also 56245. Which is OK. But when we execute the same procedure for a different parameter. SP is executed considering the above plan only.

exec sp_test 'hr';

Here is the actual execution plan.

So basically, it should have read only 5 out of 5 rows, instead, it has read 5 rows out of 56245 rows. That’s the discrepancy, which may cause a huge performance issue if the data set is huge. Now just to understand it in more detail, let’s clear PROCCACHE and rerun the procedure in reverse order.

dbcc freeproccache();
exec sp_test 'hr';

Let’s look at more details.

Let’s run the procedure with second value as parameter.

exec sp_test 'sales';

The difference is very absurd since this time we executed the procedure with ‘hr’ as a parameter. The estimated number of rows is 5 for both cases, even when we have the actual number of rows very different in the result set. Such absurd plans will cause performance issues since the execution plan is way beyond the actual numbers.

Query Plan Caching Issues

Another similar problem could be when multiple plans are stored in the cache, the optimizer will get confused and may pick any random plan and then it may cause very big bottlenecks in the system performance.

How to resolve parameter sniffing Issues?

Let’s move to the most important part i.e. How to resolve Parameter Sniffing Issues? Here are different ways to resolve the issues.

Disable Parameter Sniffing at Database Level

This is one of the most not recommended solutions if we will disable the parameter sniffing for the complete database, the application will remain deprived of its benefits. The most important benefit, which we get out of parameter sniffing is, in case data distribution is equal in the table then the same plan can be used for all such queries and it will result in less resource usage since the optimizer does not need to generate the plan again and again, it will simply pick the last plan and use it for execution.

Option (Recompile)

When we are sure that we need a new plan for every execution of a specific SP, we may simply mention option(Recompile) at the end of SP definition.

create or alter procedure sp_test 
@param1 varchar(10)
as 
begin 
select * from emp where dept = @param1
option(recompile)
end;

Another way of doing the same could be, as mentioned below.

create or alter procedure sp_test 
@param1 varchar(10)
with Recompile
as 
begin 
select * from emp where dept = @param1;
end;

OPTION (OPTIMIZE FOR)

We can also opt for recompiling the sp in case a specific parameter value is passed.

create or alter procedure sp_test 
@param1 varchar(10)
as 
begin 
select * from emp where dept = @param1
OPTION (OPTIMIZE FOR (@param1 = 'sales'))
end;

OPTION (OPTIMIZE FOR UNKNOWN) or using a local dummy variable

This option is like create a generic plan and use for all values. It can be done in 2 ways:

create or alter procedure sp_test 
@param1 varchar(10)
as 
begin 
select * from emp where dept = @param1
OPTION (OPTIMIZE FOR UNKNOWN)
end;

create or alter procedure sp_test 
@param1 varchar(10)
as 
declare 
@localparam varchar(10)
set @localparam=@param1
begin 
select * from emp where dept = @localparam
end;

Conclusion

In the entire article, we have learned all the aspects of parameter sniffing, its benefit, issues, and How to resolve parameter sniffing Issues?

Here is a video that can be helpful in understanding Parameter sniffing.

If you want to read about SQL Windowing function, please read this article.