Parameter Sniffing에 관하여...

Parameter Sniffing(파라미터 스니핑)은 기존에 제가 알기론 성능에 나쁜 영향을 미치는 것으로 알고 있었습니다. 

하지만 파라미터 스니핑은 SQL Server에서 대부분의 프로세스에서 성능을 향상 시킵니다.

이것은 단순한 용어이며, 파라미터 스니핑이 성능에 나쁜 영향을 주는 것은 아닙니다.

하지만 파라미터 스니핑에 의한 성능 문제 발생 시 엄청난 장애로 이어질 수 있습니다.

(실제 30분 작업이 7시간이 걸려서 장애가 발생 한 적이 있습니다.)

 

파라미터 스니핑은 옵티마이저와 관련이 있습니다.

옵티마이저는 실행계획(Execution Plan)을 작성하기 위해서 통계 정보의 내용을 이용합니다. 그러므로 옵티마이저에 의해 생성된 실행 계획의 정확도는 통계 정보의 정확도와 통계를 생성하는데 사용된 데이터의 분포도에 따라 좌지우지 됩니다.

 

저장 프로시저와 매개 변수화된(parameterized queries)에서는 주로 지역 변수와 매개 변수를 사용하곤 합니다. 지역/매개 변수는 다음과 같이 정의 할 수 있습니다.

• 지역변수 : Transact-SQL 지역 변수는 특정 유형의 단일 데이터 값을 보유할 수 있는 개체입니다. 
• @DECLARE 를 선언하여, 만든 변수로 보시면 될 듯 합니다.
• https://technet.microsoft.com/ko-kr/library/ms187953(v=sql.105).aspx
• 매개변수 : 파라미터 또는 보조변수라고 한다.
• 매개 변수를 이용하면 실행될 부프로그램에게 값을 전달 할 수 있다. (네이버 백과 갈취)
• MSDN에는 정의가 딱히 뭐라고 나오지 않지만, 이값은 프로시저를 만들때 선언하는 변수로 보면 될 듯 합니다.

 

여기서 파라미터 스티핑 성능 상에 이슈는 지역변수와 매개변수에 따른 옵티마이저의 처리 방식 때문에 성능 이슈가 발생 합니다. 

그렇다면, 성능 이슈를 보기에 앞서 파라미터 스니핑은 무엇일까요?

 

 

 

ⅰ. 파라미터 스니핑이란?

 

SQL Server는 SP(저장 프로시저)를 처음 실행하기 전까지 매개변수의 값을 알 수 없습니다.

매개변수에 값이 할당(사용자가 값을 넣고, exec를 이용해 SP를 실행하면) 하면 

이 값은 옵티마이저로 전달 됩니다. 옵티마이저는 전달된 값을 이용해 통계 정보를 검색하게 됩니다.

옵티마이저는 모든 데이터를 읽고 실행계획을 만들기에는 너무 시간이 

오래 걸리므로, 통계 정보를 이용하여, 실행계획을 만듭니다.

이러한 매개 변수의 값을 이용하는 프로세스를 파라미터 스니핑이라고 합니다.

 

즉, 파라미터 스니핑은 프로시저나 매개 변수화된 쿼리가 실행될 때마다 발생한다고 할 수 있습니다.

결론적으로 이것은 나쁜 의미의 성능저하가 아닙니다. 

파라미터 스니핑을 확인해 보기 위해 테스트를 해보면 다음과 같습니다.

 

테스트는 AdventureWorks2008R2에서 진행하였습니다. 

우선 저장 프로시저를 한개 만듭니다.

CREATE 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 
 
GO 

 

이 프로시저를 생성하고, 실행계획 활성화 시킵니다.

또한 자세한 몇가지를 더 보기 위해서 몇가지 옵션을 활성화 합니다.

 

SET STATISTICS IO ON SET STATISTICS TIME ON 

 

옵션을 활성화 하고, 그래픽 실행계획을 활성화 한 후에 다음과 같이 실행 시킵니다.

EXEC dbo.spAddressByCity 'London' ; 

 

위의 결과값에 대한 실행계획은 다음과 같습니다.

 

논리적 읽기 수가 216번으로 나타나고 있으며, 실행계획 중에 SELECT 부분을 클릭하면, 붉은 동그라미 안에 @City라고 나타나 있는 것을 볼 수 있습니다. 이것을 보면 저장 프로시저를 호출해서 컴파일하는 데 사용된 값을 확인 할 수 있습니다.

 

여기서 보면 SP를 실행 할 때 'London'이라고 실행하였는데, 그 값은 @City로 변경되어 있다.

이는 옵티마이저가 자동으로 'London'을 매개변수화 하여, @City로 변환 

시켰음을 알수 있습니다.. 이것이 파라미터 스니핑입니다.

 

다음으로 위의 프로시저와 같은 쿼리를 DECLARE를 이용하여, 지역변수를 선언하고, 실행해 보겠습니다.

DECLARE @city VARCHAR(75) = 'London' ;  
 
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 ; 

 

 

 

이상하게 논리적 읽기 수가 216에서 1,084로 증가 하였다.

이는 지역변수가 파라미터 스니핑을 막았기 때문에 SP와 다른 성능을 보이는 것입니다.

이럴 경우 지역변수는 샘플링 되지 않기 때문에 SQL Server는 컴파일 시점에서 어떠한 값이 전달 되는지 알 수 없습니다.

 

즉, 옵티마이저는 실제 값을 사용하는 것이 아니라 통계 정보에 있는 데이터를 샘플링해 사용했다는 것을 의미합니다.

통계 정보의 샘플링된 값을 이용함으로써 서로 다른 실행 계획과 성능 수치를 보이게 됩니다.

 

그래서 예전에 제가 겪은 장애에 때는 전날 새벽 점검때 통계 업데이트를 모두 진행하였는데, 이것이 업데이트 된 통계정보를 이용하여, 잘못된 플랜을 세워두고, 그것을 이용하여 계속해서 작업을 처리하였기에 30분 걸리는 작업이 7시간으로 늘어 났던 것 입니다. 

 

결론적으로 파라미터 스니핑은 매개변수를 사용하는 저장 프로시저가 실행 될 때마다 개입되는 프로세스이고, 이를 통해 전달된 매개 변수에 가장 적합한 실행계획을 만들어 준다는 장점을 제공합니다. 

 

파라미터 스니핑에 의한 장애 및 처리는 다음 포스트에....

 

 

참조 

실전 SQL Server MVP61 - 그 두번째 이야기 32장