MSSQL에서 저장 프로시저가 어떤 날은 0.1초, 어떤 날은 30초가 되는 경우가 있다. 코드도 인덱스도 그대로고 데이터도 크게 안 늘었는데 그렇다. 서버를 재시작하면 잠깐 괜찮아지다가 다시 느려지기도 한다. 파라미터 스니핑의 전형적인 모습이다.
무슨 일이 일어나는가
MSSQL은 프로시저를 처음 실행할 때 실행 계획을 만들어 플랜 캐시에 넣고, 이후 호출에서는 그 계획을 재사용한다. 매번 컴파일하면 비싸기 때문이다.
계획을 만드는 순간 옵티마이저는 그때 넘어온 파라미터 값을 들여다본다(sniffing). 그 값에 최적인 계획을 세우고, 그 계획이 이후 모든 값에 대해 쓰인다.
값 분포가 고르면 문제가 없다. 편중돼 있으면 문제가 된다. 다음과 같은 분포를 가정해 보자.
select region_cd, count(*) from orders group by region_cd;
-- SEOUL 4,200,000
-- BUSAN 180,000
-- JEJU 1,900create procedure usp_get_orders @region char(5)
as
select ord_no, ord_dt, amt from orders where region_cd = @region;첫 호출이 JEJU로 들어오면 옵티마이저는 2천 건 남짓을 예상하고 인덱스 탐색(Index Seek) + 키 조회(Key Lookup) 계획을 만든다. 적절한 선택이다. 그 계획이 캐시에 남은 상태에서 SEOUL로 호출되면 420만 건을 한 건씩 키 조회하게 된다.
반대로 첫 호출이 SEOUL이면 테이블 스캔 계획이 만들어지고, JEJU 조회도 420만 건을 훑는다.
플랜 캐시는 재시작, 메모리 압박, 인덱스 변경, 통계 갱신 등으로 수시로 비워진다. 비워진 뒤 어떤 값이 먼저 들어오느냐가 그날의 성능을 결정한다. 아무도 아무것도 안 했는데 느려지는 이유가 이것이다.
확인하는 방법
캐시에 있는 계획과 그때 사용된 파라미터 값을 함께 볼 수 있다.
select top 20
qs.execution_count,
qs.total_logical_reads / qs.execution_count as avg_reads,
qs.total_elapsed_time / qs.execution_count as avg_time_us,
object_name(st.objectid, st.dbid) as proc_name,
substring(st.text, 1, 200) as sql_text,
qp.query_plan
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
where object_name(st.objectid, st.dbid) = 'usp_get_orders'
order by avg_reads desc;avg_reads가 비정상적으로 크면 잘못된 계획으로 돌고 있다는 신호다. 실행 계획 XML을 열면 ParameterCompiledValue와 ParameterRuntimeValue가 보인다. 이 둘이 다르면 파라미터 스니핑이 확정이다.
select
c.value('@Column', 'nvarchar(128)') as param,
c.value('@ParameterCompiledValue', 'nvarchar(128)') as compiled_val,
c.value('@ParameterRuntimeValue', 'nvarchar(128)') as runtime_val
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
cross apply qp.query_plan.nodes('//ParameterList/ColumnReference') t(c)
where qp.objectid = object_id('usp_get_orders');대응 방법
상황에 따라 선택지가 다르다. 위에서부터 부작용이 작은 순서다.
1. 인덱스를 먼저 본다. 커버링 인덱스를 만들어 키 조회 자체를 없애면, 계획이 어느 쪽으로 서든 차이가 줄어든다. 근본 원인 제거는 아니지만 진폭을 줄이는 데 가장 효과적이다.
create nonclustered index ix_orders_region
on orders (region_cd) include (ord_no, ord_dt, amt);2. OPTIMIZE FOR UNKNOWN. 파라미터를 엿보지 말고 통계의 평균 분포로 계획을 세우라는 지시다. 최고 성능은 포기하는 대신 예측 가능해진다. 최악을 없애는 게 목적일 때 적합하다.
select ord_no, ord_dt, amt from orders where region_cd = @region
option (optimize for unknown);3. RECOMPILE. 매 실행마다 계획을 새로 만든다. 항상 그 값에 맞는 최적 계획이 나오지만 컴파일 비용을 매번 낸다. 호출 빈도가 낮고 처리량 차이가 큰 배치성 쿼리에 맞는다. 초당 수백 번 호출되는 쿼리에 쓰면 CPU가 먼저 무너진다.
select ord_no, ord_dt, amt from orders where region_cd = @region
option (recompile);4. 로컬 변수로 옮기기. 파라미터를 로컬 변수에 담아 쓰면 옵티마이저가 값을 엿볼 수 없게 되어 사실상 OPTIMIZE FOR UNKNOWN과 같아진다. 널리 쓰이지만 의도가 코드에 드러나지 않아 다음 사람이 이유를 모른다. 쓰려면 주석을 남기는 편이 낫다.
5. 프로시저 분리. 처리량이 극단적으로 다른 경우를 분기해 서로 다른 프로시저로 호출하면 각자 자기 계획을 갖는다. 코드는 늘지만 가장 확실하다.
SQL Server 2022 이후
최신 버전에는 Parameter Sensitive Plan Optimization이 들어 있다. 파라미터 값의 구간별로 계획을 여러 개 만들어 두고 골라 쓰는 기능으로, 이 문제를 상당 부분 자동으로 완화한다. 데이터베이스 호환성 수준이 160 이상이어야 동작한다.
select name, compatibility_level from sys.databases where name = db_name();다만 자동으로 처리되는 범위가 제한적이라, 여전히 위의 대응이 필요한 경우가 있다.
정리
파라미터 스니핑은 버그가 아니라 계획 재사용이라는 설계의 부작용이다. 그래서 완전히 없앨 수는 없고, 편중된 데이터에서 진폭을 줄이는 쪽으로 관리한다.
판단 순서는 이렇다. 같은 프로시저의 실행당 논리적 읽기가 들쭉날쭉한지 보고, 계획 XML에서 컴파일 값과 실행 값이 다른지 확인한다. 맞으면 인덱스부터 손보고, 그래도 진폭이 크면 OPTIMIZE FOR UNKNOWN으로 예측 가능하게 만든다. 호출 빈도가 낮다면 RECOMPILE이 가장 단순한 답이다.