with SQL Server

지난번 소개해 드린 Parameter Sniffing의 해결방법(?)에 대해서 공유합니다.

결론부터 말씀 드리면 3가지 정도가 있습니다.

1.     추적 플래그 4136 설정

2.     옵티마이저 쿼리힌트 설정

3.     프로시저 내 WITH RECOMPILE 옵션 추가

이번에도 마찬가지로 데모를 통해 하나하나 눈으로 확인해 보죠~

* 추적 플래그 4136 설정

Use AdventureWorks

go

--쿼리실행 시간 및 통계설정

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

DBCC TRACEON(4136, -1); --4136 추적 플래그 전역으로 ON!

- 추적 플래그는 SQL Server가 재시작 되면 초기화 되므로 시작 시 매개변수로 지정해서 자동으로 설정 가능

--SQL Server 캐시삭제(모든실행계획삭제)

DBCC FREEPROCCACHE

--인덱스 검색 후 풀스캔

EXEC user_GetCustomerShipDates '2001-07-10', '2001-07-11' -- 인덱스검색

EXEC user_GetCustomerShipDates '2001-07-08', '2004-08-07' -- 풀스캔(모든구간검색)

추적 플래그를 설정하기 전 인덱스 검색 후 풀스캔 했을 때 논리적 읽기 수가 94468 이였던 것에 반해 약 800배 이상이 감소했습니다.

참고로 추적 플래그 4136은 아래 버전이상에서만 포함되어 있습니다.

(This option was added in SQL Server 2008 SP1 CU7,

and SQL Server 2008 R2 CU2, and also back-ported into SQL Server 2005 in SP3 CU9)

추적 플래그 작동 방법을 요약하면 들어온 파라미터 값으로 통계 히스토그램(statistics histogram)을 통해

반환될 행 수를 미리 계산해서 실행하게 됩니다.

하지만 이와 같은 경우 정상적인 프로시저도 영향을 미칠 수 있으므로 Parameter Sniffing이 해결되지 않은

최후의 방법으로 사용해야 된다고 합니다.

* 옵티마이저 쿼리힌트 설정

DBCC TRACEOFF(4136, -1); --추적 플래그 OFF!

ALTER PROCEDURE [dbo].[user_GetCustomerShipDates]

(

@ShipDateStart DATETIME ,

@ShipDateEnd DATETIME

)

AS

SELECT  CustomerID ,

SalesOrderNumber

FROM Sales.SalesOrderHeader

WHERE ShipDate BETWEEN @ShipDateStart AND @ShipDateEnd

OPTION ( OPTIMIZE FOR ( @ShipDateStart = '2001-07-08',

@ShipDateEnd = '2004-08-07' ))

실행계획을 FOR 이후 일자를 이용해서 생성하도록 옵션을 설정합니다.

위에서는 어떤 범위가 값이 들어오던지 풀 스캔 하도록 했습니다~

--SQL Server 캐시삭제(모든실행계획삭제)

DBCC FREEPROCCACHE

-- 인덱스 검색 후 풀스캔

EXEC user_GetCustomerShipDates '2001-07-10', '2001-07-11' -- 인덱스검색

EXEC user_GetCustomerShipDates '2001-07-08', '2004-08-07' -- 풀스캔(모든구간검색)

결과는 풀스캔 때와 동일하므로 따로 캡쳐하지 않았습니다.

* 프로시저 내 WITH RECOMPILE 옵션 추가

ALTER PROCEDURE [dbo].[user_GetCustomerShipDates]

(

@ShipDateStart DATETIME ,

@ShipDateEnd DATETIME

)

WITH RECOMPILE –프로시저가 호출될 때 마다 실행 계획 재작성

AS

SELECT CustomerID ,

                   SalesOrderNumber

FROM Sales.SalesOrderHeader

WHERE ShipDate BETWEEN @ShipDateStart AND @ShipDateEnd

위와 같이 RECOMPILE 옵션 추가 후 프로시저를 호출해 보겠습니다.

--SQL Server 캐시삭제(모든실행계획삭제)

DBCC FREEPROCCACHE

--이번엔 반대로 인덱스 검색 후 풀스캔

EXEC user_GetCustomerShipDates '2001-07-10', '2001-07-11' -- 인덱스검색

EXEC user_GetCustomerShipDates '2001-07-08', '2004-08-07' -- 풀스캔(모든구간검색)

오오 파라미터 스니핑이 해결된 건 물론이고 각각 실행했을 때에 맞는(인덱스 탈 때 타고 풀스캔 해야 될 때 하는)

논리적 읽기 수를 보여주고 있네요. 실행계획도 정상적이고요~

그럼 피라미터 스니핑을 방지하기 위해서 모든 프로시저 내에 RECOMPILE 옵션 추가해도 될까요?

- 대답은 No!! 입니다 ㅎㅎ

맨 처음 들어가면서 프로시저의 큰 장점 중 하나가 실행계획의 재사용이라고 했는데요.

RECOMPILE 옵션을 사용하게 되면 정상적인 프로시저 또한 실행계획을 재사용하지 못하고 호출할 때 마다

실행계획을 다시 세우기 때문에 그만큼 비용이 낭비되게 됩니다.

하지만 파라미터 스니핑이 발생하는 프로시저에는 RECOMPILE 옵션을 추가하는 방법을 고려할 수 있습니다.

추가적인 RECOMPILE 옵션 Tip을 드리자면 Multiple queries로 되어있는 프로시저 내에는

문제가 되는 쿼리에만 RECOMPILE 옵션을 따로 지정할 수 있습니다.

CREATE PROCEDURE user_GetCustomerShipDates

(

@ShipDateStart DATETIME ,

@ShipDateEnd DATETIME

)

AS

SELECT CustomerID ,

SalesOrderNumber

FROM Sales.SalesOrderHeader

WHERE ShipDate BETWEEN @ShipDateStart AND @ShipDateEnd

OPTION ( RECOMPILE ) –쿼리 단위로 옵션 설정 가능

GO

이상으로 Parameter Sniffing의 원인과 해결방법 까지 모두 살펴보았습니다.

Reference : Troubleshooting SQL Server A Guide for the Accidental DBA

오늘은 지난 시간에 이어서 좀 더 깊이 있게 Parameter Sniffing이란 것을 소개해 드릴까 합니다.

오라클 DBMS에서는 Bind Peeking이라고도 불립니다.

우선, 파라미터 스니핑을 짧게 설명하자면 아래와 같습니다.

* Parameter Sniffing

- Parameter Sniffing은 SQL Server에서 프로시저를 처음 호출할 때(SQL Server 재 시작 등) 생성된 실행계획을

   플랜 캐시에 저장한 뒤 이후 프로시저가 호출되면 실행계획을 세우지 않고 재사용 합니다.

   그래서 성능을 고려해 프로시저를 이용하는 것이 큰 장점입니다.

   하지만 이와 같은 문제가 오히려 성능에 치명적인 영향을 미치는 경우가 발생합니다.

데모를 통해서 함께 확인해 보시죠~

일단 샘플 데이터 만드는 게 귀차니즘이므로 AdventureWorks 예제 DB를 사용하겠습니다.

AdventureWorks DB에는 Sales.SalesOrderHeader(판매주문) 라는 테이블이 있습니다.

위 테이블 내 ShipDate(출고일자) 컬럼을 이용해 검색하는 프로시저를 하나 만들어 보죠~

참고로 ShipDate에는 2001-07-08 ~ 2004-08-07 까지의 데이터만 존재합니다.

--물건 출고일자 내 고객ID, 주문수량 검색 프로시저

CREATE PROCEDURE Usp_GetCustomerShipDates(

@ShipDateStart DATETIME ,

@ShipDateEnd DATETIME)

AS

SELECT CustomerID ,

     SalesOrderNumber

FROM Sales.SalesOrderHeader

WHERE ShipDate BETWEEN @ShipDateStart AND @ShipDateEnd

GO

프로시저에서 검색할 때 유리하도록 인덱스도 만들어 주겠습니다.

--출고일자컬럼인덱스생성

CREATE NONCLUSTERED INDEX IDX_ShipDate_ASC

ON Sales.SalesOrderHeader (ShipDate)

GO

세팅은 끝났습니다~ Ho ya~

그럼 이제 프로시저를 호출 할건데요.

프로시저 호출 시 옵티마이저에서 실행계획을 두 가지를 비교해보기 위해서

한번은 출고일자를 처음부터 끝까지(풀스캔) 다른 하나는 하루치만 검색하도록 하겠습니다.(인덱스 검색)

통계를 확인하기 위해 옵션 ON! & 플랜캐시 삭제!

--쿼리실행 시간 및 통계설정

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

--SQL Server 캐시삭제(모든 실행계획 삭제)

DBCC FREEPROCCACHE

그럼 풀스캔 하는 프로시저와 인덱스 검색하는 프로시저를 차례로 실행해 보겠습니다.

--데이터 선택도에 따라 옵티마이저 풀스캔 or 인덱스검색

EXEC user_GetCustomerShipDates '2001-07-08', '2004-08-07' -- 풀스캔(모든구간검색)

EXEC user_GetCustomerShipDates '2001-07-10', '2001-07-11' -- 인덱스검색

아래 실행 결과 입니다.(실행계획은 스압으로 인해 캡쳐하지 않았습니다 -_-+)

들어가면서 쓴 것과 같이 최초 호출되는 실행계획(풀스캔하는 실행계획)이 플랜캐시에 등록되었으므로,

인덱스 검색을 해야 하는 상황에서도 실행계획 재사용으로 인해 인덱스를 타지 않고 풀스캔 된 것을 확인 하실 수 있습니다.

이번엔 반대로 인덱스 검색을 실행하고 풀스캔을 실행해보도록 하겠습니다.

--SQL Server 캐시삭제(모든 실행계획 삭제)

DBCC FREEPROCCACHE

--이번엔 반대로 인덱스 검색 후 풀스캔

EXEC user_GetCustomerShipDates '2001-07-10', '2001-07-11' – 인덱스 검색

EXEC user_GetCustomerShipDates '2001-07-08', '2004-08-07' -- 풀스캔(모든구간검색)

아래 실행 결과 입니다.

위에서도 인덱스를 타야 되는 실행구문이 풀스캔 되는 문제도 성능에 영향을 미치겠지만 이번 결과가 더 충격적입니다.

동일한 검색이지만 풀스캔 시에는 논리적 읽기 수가 706 인 것에 비해

최초 실행 된 인덱스 검색 실행계획으로 Parameter Sniffing이 발생해서 논리적 읽기 수가 94468 약 133배 증가 했습니다.(랜덤 IO 발생)

성능적인 이슈만 봤을 때 Parameter Sniffing으로 인해 프로시저 사용이 언제나 향상된 성능을 보장하진 않습니다.

(프로시저 사용하지 말자는 뜻이 절~대 아닙니다!)

모니터링을 통해 평소에 정상적이던 프로시저가 갑자기 실행시간이나 IO 발생이 높아진다면

Parameter Sniffing을 의심해봐야 합니다.

Reference : Troubleshooting SQL Server A Guide for the Accidental DBA