SQL Server 파라미터 스니핑(Parameter Sniffing)

   

프로시저의 맹점인 파라미터 스니핑이 존재한다.

해결책은 아래 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'

 

 

/*

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

 

 

 

-- 해당 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')