Parameter Sniffing 방지 SP 작성 TIP(101019)

Store procedure를 쓰는 가장 큰 이유는 쿼리 실행 플랜이 메모리에 저장되어 재사용 된다는 점이다.

하지만 장점이 있으면 단점도 있는 법

가장 문제가 되는 점을 Parameter Sniffing 이라고 불리며 정의는 하기와 같다.

 

" Parameter Sniffing 은 Stored Procedure 에서 파라미터를 사용하여 Value 를 Pass 할 경우에 전형적인 경우에서 발생하는 캐쉬가 

  저장 되지 않고 비전형적인 Value 가 Pass 되었을 경우의 캐쉬가 저장되어 전형적인 경우에서도 계속적으로 캐쉬가 사용되어

  비효율과 성능저하를 나타내는 경우를 말한다. "

상기 내역을 쿼리 결과를 통해 알아보고 해결책을 제시하고자 한다.

 

USE tempdb;

GO

IF OBJECT_ID('dbo.TEST_TABLE','U') IS NOT NULL

DROP TABLE dbo.TEST_TABLE; 

 

 

CREATE TABLE dbo.TEST_TABLE

(

 Seq int  not null,

 DT Smalldatetime,

 Value Varchar(10),

);

 

 

 --10000건 삽입

 

go

declare @I int

set @I = 0

while @I < 10000

Begin

Insert dbo.TEST_TABLE select @I, GETDATE()+@i, REPLICATE('TEST',2)

Set @I= @I+1

END

 

GO

 --유닉크 넌클러스터드 인덱스 날짜컬럼에 생성

Create unique index NCI_TEST_TABLE_DT ON TEST_TABLE(DT)

WITH FILLFACTOR = 80 

GO 

--TEST용 프로시저 생성  

IF OBJECT_ID('dbo.TESTSP','P') IS NOT NULL

DROP PROC dbo.TESTSP;

go

Create PROC TESTSP (@Fday Smalldatetime, @Lday Smalldatetime)

AS

SELECT * FROM TEST_TABLE

WHERE DT BETWEEN @Fday AND @Lday  

 

 

DBCC FREEPROCCACHE

GO

set statistics io on

 

--풀스캔

select * FROM TEST_TABLE

(10000개행이영향을받음)

테이블'TEST_TABLE'. 검색수1, 논리적읽기수23, 물리적읽기수0,

go

 

--1건 조회

exec dbo.TESTSP '2010-10-15 16:09:00','2010-10-16 16:09:00'

(1개행이영향을받음)

테이블'TEST_TABLE'. 검색수1, 논리적읽기수3, 물리적읽기수0

Go

 

--1년치 데이터 조회

exec  dbo.TESTSP '2010-10-15 16:09:00','2011-11-16 16:09:00'

(397개행이영향을받음)

테이블'TEST_TABLE'. 검색수1, 논리적읽기수400,

set statistics io OFF

 

 

 2번째 프로시저 실행 결과 첫번째 실행계획(index seek) 재사용으로 풀스캔 보다 더큰 비용 발생(랜덤 IO)

 

 

--TEST 프로시저 변경

Alter PROC TESTSP (@Fday Smalldatetime, @Lday Smalldatetime)

AS

 

IF (SELECT DATEDIFF(day,@Fday,@Lday)) < 100

             EXEC SmallSP @Fday, @Lday

ELSE

             EXEC BINGSP @Fday, @Lday

GO

 

 

Create PROC BINGSP (@Fday Smalldatetime, @Lday Smalldatetime)

AS

SELECT * FROM TEST_TABLE

WHERE DT BETWEEN @Fday AND @Lday 

GO

 

Create PROC SmallSP (@Fday Smalldatetime, @Lday Smalldatetime)

AS

SELECT * FROM TEST_TABLE

WHERE DT BETWEEN @Fday AND @Lday 

 

 

 

DBCC FREEPROCCACHE

GO

set statistics io on

select * FROM TEST_TABLE

(10000개행이영향을받음)

 

테이블'TEST_TABLE'. 검색수1, 논리적읽기수23, 물리적읽기수0

 

 

go

 

 

exec dbo.TESTSP '2010-10-15 16:09:00','2010-10-16 16:09:00'

(1개행이영향을받음)

 

테이블'TEST_TABLE'. 검색수1, 논리적읽기수3, 물리적읽기수0

Go

 

 

exec  dbo.TESTSP '2010-10-15 16:09:00','2011-11-16 16:09:00'

(397개행이영향을받음)

테이블'TEST_TABLE'. 검색수1, , 논리적읽기수23, 물리적읽기수0

set statistics io OFF

 

장점 : 리컴파일 없이 처리 가능

 

단점 : 인덱스 선택도의 임계치 계산이  필요