[MSSQL/SQL Server] 파라미터 스니핑(Parameter Sniffing)

1. 개요

파라미터 스니핑 (Parameter Sniffing)

저장 프로시저를 처음 실행할 때 옵티마이저는 통계 정보를 바탕으로 실행계획을 생성한다.

이때 생성된 실행계획을 캐시에 저장하고, 이후에 저장 프로시저가 다시 실행되면 캐싱된 실행계획을 재사용한다.

그러나 저장 프로시저의 파라미터 값이 어떻게 들어오는지에 따라 최적화되지 못한 실행계획으로 인해 성능 저하를 일으킬 수 있는데, 이러한 현상을 파라미터 스니핑이라고 한다.

주로 저장 프로시저를 실행할 때 발생하는 현상이다.

 

해결 방안

• Recompile 옵션 사용
• IF 조건문 활용

 

2. 파라미터 스니핑 재현

1) 테스트 쿼리

예약 내역을 조회하는 쿼리가 있다. 예약 상태(status) 컬럼에는 Non Clustered Index가 생성되어 있고, 데이터 분포도가 작은 값으로 검색하는 경우와 데이터 분포도가 큰 값으로 검색하는 경우 실행계획이 어떻게 다른지 확인한다.

-- 1) 분포도가 작은 값으로 검색하는 경우 (status = 2)
select res_no, status, res_date
from reservation with(nolock)
where status = 2
 
-- 2) 분포도가 큰 값으로 검색하는 경우 (status = 4)
select res_no, status, res_date
from reservation with(nolock)
where status = 4

 

▶ 실행계획 비교

1) 분포도가 작은 값(status=2)으로 검색하는 경우, 인덱스를 사용하여 검색한다.

2) 반면 분포도가 큰 값(status=4)으로 검색하는 경우, 거의 모든 데이터를 출력해야 하므로 인덱스가 있음에도 불구하고 스캔한다.

 

2) 파라미터 스니핑 테스트

-- Step 1. 저장 프로시저 생성
create procedure sniffing_test
    @res_status tinyint
as
begin
    select res_no, status, res_date
    from reservation with(nolock)
    where status = @res_status
end
 
-- Step 2. 캐시 비우기
DBCC FREEPROCCACHE()
GO
 
-- Step 3. 파라미터 값을 다르게 하여 순차적으로 저장 프로시저 실행
declare @res_status tinyint
set @res_status = 2
exec dbo.sniffing_test @res_status
go
 
declare @res_status tinyint
set @res_status = 4
exec dbo.sniffing_test @res_status
go

 

▶ 결과

파라미터 값을 다르게 하여 순차적으로 저장 프로시저를 실행하면, 저장 프로시저를 최초에 실행했을 때 생성된 실행계획을 그대로 재사용함을 알 수 있다. 즉 파라미터 값에 따라 최적화되지 못한 실행계획으로 인해 성능이 저하되는 파라미터 스니핑 현상이 발생하였다.

 

3) 파라미터 스니핑 문제 해결

방법 1) Recompile 옵션 사용

Recompile 옵션을 사용하면 SP가 실행될 때마다 리컴파일 한다.

즉 SP가 실행될 때마다 실행계획을 새로 생성하므로, 파라미터 값에 따라 최적화된 실행계획이 생성될 수 있다.

 

① 저장프로시저 생성 시 WITH RECOMPILE 옵션 추가

-- 저장 프로시저 생성
create procedure sniffing_recompile_test
    @res_status tinyint
with recompile
as
begin
    select res_no, status, res_date
    from reservation with(nolock)
    where status = @res_status
end

 

② 저장프로시저 실행 시 WITH RECOMPILE 옵션 추가

-- 저장 프로시저 실행
declare @res_status tinyint
set @res_status = 2
exec dbo.sniffing_test @res_status with recompile
go

 

▶ 결과

SP가 실행될 때마다 리컴파일 하므로, 매개변수 값에 따라 최적화된 실행계획을 새로 생성한다.

 

방법 2) IF 조건문 활용

매개변수 값에 따라 서로 다른 쿼리가 실행되도록 IF 조건문을 만들어준다.

create procedure sniffing_if_test
    @res_status tinyint
as
begin
 
    IF @res_status = 2
        BEGIN
            select res_no, status, res_date
            from sr.dbo.reservation with(nolock, index=IX_res_status)
            where status = @res_status
        END
 
    ELSE IF @res_status = 4
        BEGIN
            select res_no, status, res_date
            from sr.dbo.reservation with(nolock)
            where status = @res_status
        END
 
end

 

▶ 결과

IF문에 의해 매개변수 값에 따라 서로 다른 쿼리가 실행되므로, 파라미터 스니핑 문제를 해결하는 데 도움이 될 수 있다.