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

출처 : https://blogs.msdn.microsoft.com/turgays/2013/09/10/parameter-sniffing-problem-and-possible-workarounds/

테스트 쿼리.

-- /** 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'

/*

  1. 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'

/*

  1. 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'

/*

  1. 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]