Parameter Sniffing is a typical problem when using stored procedures which could result in bad query plans. So, when the procedure is executed for the first time, a query plan is created and stored in procedure cache for reuse. The initial plan generated will be in perspective of parameter passed in the first execution and this plan is saved in the procedure cache and reused for subsequent procedure calls until the plan is invalidated by the update statistics or when procedure cache is cleared or code recompile happens.
So,once the plan is created, it could be reused even for different parameters which is where, it can cause problems because there is no guarantee that the plan generated for first parameter is also good plan for the subsequent parameters. This is called as Parameter Sniffing.
--Below is the scenario for Parameter Sniffing.
Create Procedure usp_PersonNamePhone(@Id int)
as
Begin
Select A.FirstName,A.Lastname, B.PhoneNumber
from [Person].[Person] A
INNER JOIN [Person].[PersonPhone] B on A.BusinessEntityID=B.BusinessEntityID
where A.BusinessEntityID<@Id
End
--You can see same plan being used in all three procedure calls.
usp_PersonNamePhone @Id= 2
usp_PersonNamePhone @Id= 200
usp_PersonNamePhone @Id= 20000
There are several ways parameter sniffing problem can be addressed and below some of the commonly used methods.Keep in mind that each method has it pros and cons.
1.Recompile every time the procedure is called and hence, generates query plan as per the input parameter.
Create Procedure usp_PersonNamePhone_V1(@Id int) WITH RECOMPILE
as
Begin
Select A.FirstName,A.Lastname, B.PhoneNumber
from [Person].[Person] A
INNER JOIN [Person].[PersonPhone] B on A.BusinessEntityID=B.BusinessEntityID
where A.BusinessEntityID<@Id
End
--Different Query plans is generated based on Input.
usp_PersonNamePhone_V1 @Id= 2
usp_PersonNamePhone_V1 @Id= 200
usp_PersonNamePhone_V1 @Id= 20000
2.Use Statement level recompile. This is better option, if the procedure has several statements and only some statements are having issues.
Create Procedure usp_PersonNamePhone_V2(@Id int)
as
Begin
Select A.FirstName,A.Lastname, B.PhoneNumber
from [Person].[Person] A
INNER JOIN [Person].[PersonPhone] B on A.BusinessEntityID=B.BusinessEntityID
where A.BusinessEntityID<@Id Option (Recompile)
End
--Different Query plans is generated based on Input.
usp_PersonNamePhone_V2 @Id= 2
usp_PersonNamePhone_V2 @Id= 200
usp_PersonNamePhone_V2 @Id= 20000
3.Optimize for unknown value by assigning a parameter value to a variable.
Create Procedure usp_PersonNamePhone_V3(@Id int)
as
Begin
declare @EntityID int
Set @EntityID=@Id
Select A.FirstName,A.Lastname, B.PhoneNumber
from [Person].[Person] A
INNER JOIN [Person].[PersonPhone] B on A.BusinessEntityID=B.BusinessEntityID
where A.BusinessEntityID<@EntityID
End
--Generalized Query plan.
usp_PersonNamePhone_V3 @Id= 2
usp_PersonNamePhone_V3 @Id= 200
usp_PersonNamePhone_V3 @Id= 20000
4.Creates plan for the specified 'optimize for' value.
Create Procedure usp_PersonNamePhone_V4(@Id int)
as
Begin
Select A.FirstName,A.Lastname, B.PhoneNumber
from [Person].[Person] A
INNER JOIN [Person].[PersonPhone] B on A.BusinessEntityID=B.BusinessEntityID
where A.BusinessEntityID<@Id Option (Optimize for (@Id=200))
End
--Query plan is created for @id=200.
usp_PersonNamePhone_V4 @Id= 2
usp_PersonNamePhone_V4 @Id= 200
usp_PersonNamePhone_V4 @Id= 20000
5.Creates plan for a Unknown value. The unknown value is calculated based on statistical data.More on this here.
Create Procedure usp_PersonNamePhone_V5(@Id int)
as
Begin
Select A.FirstName,A.Lastname, B.PhoneNumber
from [Person].[Person] A
INNER JOIN [Person].[PersonPhone] B on A.BusinessEntityID=B.BusinessEntityID
where A.BusinessEntityID<@Id Option (Optimize for unknown)
End
--Query plan is created for a unknown value.
usp_PersonNamePhone_V5 @Id= 2
usp_PersonNamePhone_V5 @Id= 200
usp_PersonNamePhone_V5 @Id= 20000
SQL Server Repository 