SQL Server 파라미터 스니핑(Parameter Sniffing)
출처: https://www.overtop.co.kr/294 [SQLin]
프로시저의 맹점인 파라미터 스니핑이 존재한다.
해결책은 아래 4가지 중에서 회피할 수 있다.
OPTION (RECOMPILE)
OPTION (OPTIMIZE FOR (@VARIABLE=VALUE))
OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN))
Use local variables
테스트 쿼리.
-- / 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'
/*
- 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'
/*
- 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'
/*
- 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
-- 해당 Plan 확인하는 쿼리
-- dbcc freeproccache
SELECT plan_handle, refcounts, usecounts, size_in_bytes, cacheobjtype, objtype
FROM sys.dm_exec_cached_plans
--option(recompile)
where plan_handle = 0x0500070051682317B06927440000000001000000000000000000000000000000000000000000000000000000
select o.object_id, s.plan_handle, h.query_plan
from sys.objects o
inner join sys.dm_exec_procedure_stats s on o.object_id = s.object_id
cross apply sys.dm_exec_query_plan(s.plan_handle) h
where o.object_id = object_id('my_AddressSP')
출처: https://www.overtop.co.kr/294 [SQLin]
