Parameter Sniffing Problem and Possible Workarounds
SQL Server compiles the stored procedures using (sniffing) the parameters send the first time the procedure is compiled and put it in plan cache. After that every time the procedure executed again, SQL Server retrieves the execution plan from the cache and uses it (unless there is a reason for recompilation). The potential problem arises when the first time the stored procedure is executed, the set of parameters generate an acceptable plan for that set of parameters but very bad for other more common sets of parameters.
There are some workarounds to overcome this problem.
- OPTION (RECOMPILE)
- OPTION (OPTIMIZE FOR (@VARIABLE=VALUE))
- OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN))
- Use local variables
We will be examining first 4 workarounds with below scripts.
/*
Turgay Sahtiyan - Microsoft SQL Server Premier Field Engineer
Blog : www.turgaysahtiyan.com
Twitter : @turgaysahtiyan
*/
--Parameter Sniffing
Use AdventureWorks2012
GO
--Hit ctrl+M to Include Actual Execution Plan
--Here our query
--Select * from Person.Address where City=@City
--If you run this query for "Seattle" you got Clustered Index Scan
Select * from Person.Address
where city='Seattle'
--If you run it for "Bothell" you got Index Seek+Key Lookup
Select * from Person.Address
where city='Bothell'
--SQL Server uses the statistics to determine which index and method should be used
--ok now, we can turn to the main topic. Parameter sniffing
--Create a stored procedure
create proc my_AddressSP (@city nvarchar(30))
as
select *
from Person.Address
where city=@city
--Call SP first time with "Seattle" parameter
exec my_AddressSP 'Seattle'
--It did index scan
--Call it again, but this time with "Bothell" parameter
exec my_AddressSP 'Bothell'
--Normally "Bothell" query does Index seek+lookup
--But a query plan was created when SP called first time (with Seattle paramater)
-- ,cached and reused for "Bothell" execution
--And we call this problem as Paramater Sniffing
------------------------------------
--Workarounds
------------------------------------
/*
1.Workaround : OPTION (Recompile)
- Every time the query or stored procedure is executed when it arrives to the query marked with the OPTION(RECOMPILE), this query is recompiled using the current parameters values.
- In this way the plan behaves well for any combination of parameters (is statistics are good) but the overhead is recompilation of the query every time
*/
--Alter SP
alter proc my_AddressSP (@city nvarchar(30))
as
select *
from Person.Address
where city=@city
option (recompile)
--Call it for "Seattle"
exec my_AddressSP 'Seattle'
--Call it for "Bothell"
exec my_AddressSP 'Bothell'
/*
2. Workaround : OPTION (OPTIMIZE FOR (@VARIABLE=VALUE))
- Let's assume that this SP is called %99 percent for "Bothell" and only %1 percent for "Seattle"
- But if the first time it is called with "Seattle" paramater, after that all "Bothell" queries run with undesired query plan
- If you have a workload like this, you can use OPTION (OPTIMIZE FOR (@VARIABLE=VALUE)) workaround
- This option causes the compilation process to ignore the value for the specified variable and use the specified value instead.
- Unlike the first workaround (Option(Recompile)), this option avoid the overhead of recompiling the query every time.
*/
--Alter SP
alter proc my_AddressSP (@city nvarchar(30))
as
select *
from Person.Address
where city=@city
option (optimize for (@city='Bothell'))
--We call it first time with "Seattle" paramater, but the query plan is optimized for "Bothell" and cached like that
exec my_AddressSP 'Seattle'
--Call it for "Bothell", same query plan
exec my_AddressSP 'Bothell'
/*
3. Workaround : OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN))
- In this way SQL uses statistic densities instead of statistic histograms.
- So It estimates the same number of records for all paramaters
- The disadvantage is that some queries will use suboptimal plans because densities are not precise enough as the statistic histogram.
*/
--Alter SP
alter proc my_AddressSP (@city nvarchar(30))
as
select *
from Person.Address
where city=@city
option (optimize for (@city UNKNOWN))
--check the estimated number of rows. It's 34.1113 for every city
exec my_AddressSP 'Seattle'
exec my_AddressSP 'Bothell'
/*
4. Workaround : Use local variable
- This workaround is very similar with previous one (OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN)))
- when you assign the paramaters to local ones SQL Server uses statistic densities instead of statistic histograms
- So It estimates the same number of records for all paramaters
- The disadvantage is that some queries will use suboptimal plans because densities are not precise enough as the statistic histogram.
*/
--Alter SP
alter proc my_AddressSP (@city nvarchar(30))
as
declare @city_x nvarchar(30)
set @city_x = @city
select *
from Person.Address
where city=@city_x
exec my_AddressSP 'Seattle'
exec my_AddressSP 'Bothell'
--drop sp
drop proc my_AddressSP