Turgay Sahtiyan has been working in IT industry over a decade and he currently works as a Senior SQL Server Premier Field Engineer at Microsoft. He is based in Turkey and primarily giving proactive services to top enterprise customers in the MEA region. His main areas are Performance Tuning, HA&DR solutions, database administration and SQL Server 2012 Parallel Data Warehouse
Before joined to Microsoft, he was one of the SQL Server MVP in Turkey, in addition he is founder and former president of SQLPass Turkey Chapter. He is still on the leader team of SQLPass Turkey Chapter and still working with communities as being community leader by sharing his passion, technical expertise, and real-world knowledge of SQL Server with others.
Turgay regularly presents sessions at Microsoft and EMEA region SQLSaturday events. In addition he frequently blogs about SQL Server to his Turkish and English blogs
TR Blog : www.turgaysahtiyan.comEN Blog : http://blogs.msdn.com/b/turgays/Twitter : @turgaysahtiyanLinkedin : http://www.linkedin.com/pub/turgay-sahtiyan/15/b46/682
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.
We will be examining first 4 workarounds with below scripts.
Turgay Sahtiyan - Microsoft SQL Server Premier Field Engineer
Blog : www.turgaysahtiyan.com
Twitter : @turgaysahtiyan
--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
--If you run it for "Bothell" you got Index Seek+Key Lookup
--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))
--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
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 proc my_AddressSP (@city nvarchar(30))
--Call it for "Seattle"
--Call it for "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.
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
--Call it for "Bothell", same query plan
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.
option (optimize for (@city UNKNOWN))
--check the estimated number of rows. It's 34.1113 for every city
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
declare @city_x nvarchar(30)
set @city_x = @city
drop proc my_AddressSP