Parameter Sniffing Trouble Shooting

파라미터 스니핑에 대해서 지난 포스트에 작성하였습니다.

지난 번에 이어서 파라미터 스니핑이 문제가 언제 발생하며, 해결은 어떻게 해야 하는지 공부하여, 정리한 포스트를 이번에 작성 합니다.

Ⅰ. 파라미터 스니핑은 언제 성능에 영향을 주는가?

파라미터 스니핑이 문제가 되는 경우는 데이터의 분포가 적고 얼마 없는 데이터를 조회하면서 플랜A를 만들었는데, 데이터의 분포도가 많은 데이터를 조회할때 

문제가 될 수 있습니다. 

쉽게 이야기하면 회사에서 행사를 열었는데, 오는 사람마다 선물을 하나씩 준다고 가정합니다. 그런데, 생각보다 사람이 더 많이와서 줄 선물을 만들어서 바로바로

줘야하는 경우죠. 그러니 느려질 수 밖에 없죠. 앞서 포스트에서 말했던 것 처럼 옵티마이저는 샘플링된 데이터를 보고 하기 때문에 우리가 예측 하는 것과 같습니다.

옵티마이저가 일일이 테이블의 데이터를 플랜을 만들때마다 계속 전부 조사해서 플랜을 만들면 엄청나게 속도가 느려지겠지요.

이것을 앞의 예와 비교하면 이번 행사는 지난번 처럼 100명이 올테니 그렇게 준비해! 하고 준비했는데, 500명이 와서 예상을 깨는 것이죠..

그러니 잘못 된 실행계획은 성능에 저하를 불러옵니다.

그렇다면 이런 경우는 어떤 경우인지 SQL Server에서 테스트를 해보겠습니다. 이전과 동일하게 AdventureWorks2008R2 데이터베이스를 사용하였습니다.

우선 테스트를 위해서 플랜캐시를 초기화 합니다. 플랜캐시를 초기화 해야지 실행계획을 다시 만들기 때문입니다. 앞서 포스트에서 테스트 했을 때 'London';을 이용하여

테스트를 한 실행계획이 캐시에 남아있으므로, 실행계획을 초기화 시켜야합니다.

DBCC FREEPROCCACHE()

그 다음에 이번에는 'Mentor' 를 실행해봅니다.

EXEC dbo.spAddressByCity 'Mentor' ;

실행 결과는 다음과 같이 나옵니다.

이전에 실행 했을때 보다 논리적 읽기 수가 매우 감소하였습니다. 지금 옵티마이저는 'Mentor'를 이용하여, 최적의 실행계획을 만들었음을 알 수 있습니다.

그렇다면 이번에는 'London'으로 프로시저를 실행해 보겠습니다.

아래의 실행계획을 보면 두개('Mentor' , 'London')의 실행계획은 같습니다. 하지만 ...

논리적 읽기수가 확연히 차이남을 볼 수 있습니다.

논리적 읽기수가 218개 ('Mentor') 1,084 ('London')으로 확연하게 차이 납니다.

이 같은 결과는 분포도 때문입니다. 데이터의 양부터가 확연히 차이남을 볼 수 있습니다.

SELECT  'London'  ,count(*)
FROM    Person.Address AS a
        JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   a.City =  'London'

union all

SELECT   'Mentor' ,count(*)
FROM    Person.Address AS a
        JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   a.City = 'Mentor' 

건수 자체가 1건 VS 434건 입니다. 

즉, 'London'을 이용하여, 옵티마이저가 실행계획을 만들면 많은 건수를 조회 하도록 실행계획을 만들고, 'Mentor'를 이용하여 실행계획을 만들면 1건을 위한 실행계획을

만듭니다. 물론 샘플 데이터이기에 이렇게 까지는 아니겠지만... 

지금의 경우 테스트를 위한 데이터이므로 건수가 별로없지만 만약 그 건수가 몇십 몇백만 건이라면? 그 결과는 상당히 위험합니다.

잘못된 플랜을 이용하여 오랜 시간 작업을 진행하다가 테이블에 LOCK이 걸리고 블로킹이 걸린다면 시스템 전체에 피해를 줄 수 있습니다. (실제 있었습니다...)

Ⅱ. 파라미터 스니핑에 의한 문제 발생시 해결

① OPIMIZE FOR

가장 일반적인 방법으로 이 방법은 데이터의 분포가 얼마나 되어 있는지 대략적으로 아는 경우 사용하는 것이 좋습니다. 옵티마이저에게 미리 "이 데이터에 대한 실행계획을 

만들어놔라"라고, 알려주는 것으로써 옵티마이저는 미리 해당 데이터를 통해 플랜을 만들어 해당 플랜을 사용 합니다.

ALTER PROC dbo.spAddressByCity @City NVARCHAR(30)
AS 
    SELECT  a.AddressID,
            a.AddressLine1,
            a.AddressLine2,
            a.City,
            sp.[Name] AS StateProvinceName,
            a.PostalCode
    FROM    Person.Address AS a
            JOIN Person.StateProvince AS sp
            ON a.StateProvinceID = sp.StateProvinceID
    WHERE   a.City = @City
    OPTION  (OPTIMIZE FOR (@City = 'Mentor')) ;
GO

위와 같이 OPTIMIZE FOR를 추가하면 해당 SP의 실행계획은 초기화 된다. 위의 옵션을 적용하고, 'London'으로 SP를 실행(플랜 캐시가 초기화되어서 원래는 'London'으로 

만들어야함)하여도 'Mentor'의 실행계획으로 나옴을 알 수 있습니다. 하지만 이것은 데이터의 분포가 매번 변화되는 테이블에 대해서는 지속적으로 신경을 써야합니다.

만약 'Mentor'의 값이 2천건으로 증가 한다면, 지금까지 쓰고있던 플랜은 성능에 매우 좋지 못하다. 그러므로, 변화되는 데이터의 분포도를 정확히 파악하고 신경을 써줘야

하는 것 입니다.

ALTER PROC dbo.spAddressByCity @City NVARCHAR(30)
AS 
    SELECT  a.AddressID,
            a.AddressLine1,
            a.AddressLine2,
            a.City,
            sp.[Name] AS StateProvinceName,
            a.PostalCode
    FROM    Person.Address AS a
            JOIN Person.StateProvince AS sp
            ON a.StateProvinceID = sp.StateProvinceID
    WHERE   a.City = @City
    OPTION  (OPTIMIZE FOR (@City UNKNOWN)) ;
GO

데이터의 분포도를 잘 알지 못하거나 너무 빈번하게 변경된다면 위와 같이 UNKNOWN을 사용 할 수 있습니다. 이것은 매번 샘플링된 값을 이용하게 하는 것으로써 변경에 대한 

이슈를 줄 일 수 있습니다.

② WITH RECOMPILE

제 경험상 저는 이것으로 해결하였습니다. 이것은 실행 할때마다 실행계획을 새로 만드는 것 입니다. 개인적으로 이것은 이럴 경우에 사용 하는 것을 추천 합니다.

- 하루에 몇번 실행되지 않는다.

- 하루에 1~2번 JOB으로 자동 실행된다.

- IF 문이 상당히 많다. (SP 또는 AD-HOC이든...)

실행계획을 옵티마이저가 만드는 것 자체가 비용이지만, 하루에 별로 실행되지도 않는다면? 잘못된 실행계획으로 장애가 나는 것보다는 그 비용을 감수하는 것이 휠씬 

좋아 보입니다. 이것은 테이블에 힌트로 넣을 수도 있고, SP에 실행하면서 함께 실행되게 할 수도 있습니다. 

테이블에 직접 넣는 경우

ALTER PROC dbo.spAddressByCity @City NVARCHAR(30)
AS 
    SELECT  a.AddressID,
            a.AddressLine1,
            a.AddressLine2,
            a.City,
            sp.[Name] AS StateProvinceName,
            a.PostalCode
    FROM    Person.Address AS a
            JOIN Person.StateProvince AS sp
            ON a.StateProvinceID = sp.StateProvinceID
    WHERE   a.City = @City
    OPTION  (RECOMPILE) ;

프로시저 옵션에 추가 하는 부분

ALTER PROC dbo.spAddressByCity @City NVARCHAR(30)
WITH RECOMPILE
AS 
    SELECT  a.AddressID,
            a.AddressLine1,
            a.AddressLine2,
            a.City,
            sp.[Name] AS StateProvinceName,
            a.PostalCode
    FROM    Person.Address AS a
            JOIN Person.StateProvince AS sp
            ON a.StateProvinceID = sp.StateProvinceID
    WHERE   a.City = @City;

위의 2개의 차이점은 SP에 관련된 실행계획만 다시 만드는가? 아니면 옵션으로 준 테이블의 실행계획을 다시 만드는가? 의 차이입니다.

대부분의 경우는 위의 2개의 방법으로 해결이 가능하며, 서적에서 소개하는 나머지 해결부분은 크게 와닿지 않아서 읽어보기만 하였습니다. 

혹시 나머지 부분이 궁금하시면 메일이나 카톡을 부탁드립니다.

감사합니다.

참고 

실전 SQL SERVER MVP 61 그 두 번째 이야기