recompile pros & cons
RECOMPILE의 비용은 실행 빈도에 비례하고, 이득은 파라미터 값의
편차에 비례합니다. OLTP는 앞항이 크고 뒷항이 작아 금기이지만,
관리자 전용 조회는 정확히 그 반대이므로 정석에 가깝습니다.
────────────────────────────────────────────
1. 세 가지 추정 모드
────────────────────────────────────────────
옵티마이저가 WHERE col = @p 의 행 수를 추정하는 경로는 셋뿐입니다.
(1) 스니핑 ON (기본값)
- 추정 근거 : 최초 컴파일 시 전달된 값 → 히스토그램
- 플랜 확정 : 최초 1회
- 캐시 : 함
(2) 스니핑 OFF
- 추정 근거 : 평균 밀도 → density vector
(부등호는 고정 추정치: >, < 는 30%, BETWEEN 은 9%)
- 플랜 확정 : 최초 1회
- 캐시 : 함
(3) OPTION (RECOMPILE)
- 추정 근거 : 매 실행 시점의 실제 값 → 히스토그램
- 플랜 확정 : 매 실행
- 캐시 : 안 함
앞의 둘은 "언제 정한 값을 계속 쓰느냐"의 차이일 뿐,
하나의 플랜을 모든 값에 재사용한다는 점에서는 동일합니다.
세 번째만 이 전제를 깹니다.
────────────────────────────────────────────
2. RECOMPILE 은 스니핑을 끄는 것이 아닙니다
────────────────────────────────────────────
흔한 오해와 달리 RECOMPILE 은 스니핑을 무시하지 않습니다.
캐시에 박제된 과거의 스니핑 값을 버리고, 지금 값으로 다시
스니핑하는 것입니다. 옵티마이저 관점에서는 가장 강한 형태의
스니핑입니다.
이 동작의 정식 명칭은 PEO (Parameter Embedding Optimization)
이며, 런타임 값을 파라미터가 아닌 리터럴 상수로 쿼리 트리에
삽입한 뒤 최적화합니다. 그래서 RECOMPILE 에서만 가능한
최적화가 생깁니다.
- 로컬 변수도 스니핑 대상이 됨 (평소에는 불가능, 5번 참조)
- 모순 감지 : CHECK 제약이나 파티션 범위와 충돌하는 조건의
서브트리를 통째로 제거
- 조건 폴딩 : @p IS NULL OR col = @p 같은 catch-all 패턴에서
사용되지 않는 가지를 제거
────────────────────────────────────────────
3. 실제 손실은 CPU 가 아니라 메모리 그랜트
────────────────────────────────────────────
Sort / Hash 연산자의 메모리 그랜트는 컴파일 시점의
"추정 행 수 x 추정 행 크기" 로 결정되며, 실행 중에는
증가하지 않습니다. 추정이 어긋나면 양방향 모두 손해입니다.
- 과소 추정 → tempdb 스필.
페이징에서 TOP N 뒤에 Sort 가 붙으면 직격탄입니다.
- 과대 추정 → 사용하지 않을 메모리를 예약.
RESOURCE_SEMAPHORE 대기로 무관한 세션까지 굶깁니다.
두 번째가 더 위험합니다. 잘못된 플랜은 해당 쿼리만 느리게
하지만, 과대 그랜트는 인스턴스 전체에 영향을 줍니다.
────────────────────────────────────────────
4. OLTP 금기 사유와 그 무력화
────────────────────────────────────────────
(1) 컴파일 CPU
- 내용 : 단순 쿼리 1~5ms, 복잡한 조인은 수십 ms
- 관리자 모드 : 호출 QPS 가 사실상 한 자릿수이므로
총비용 무시 가능
(2) 관측 가능성 상실
- 내용 : sys.dm_exec_query_stats 에 누적되지 않아
슬로우 쿼리 집계에서 누락
- 관리자 모드 : 호출 건수가 적어 개별 추적이 오히려 용이.
Query Store 로도 보완 가능
(3) 동시 컴파일 경합
- 내용 : 동일 쿼리 동시 유입 시 컴파일이 직렬화 지점
- 관리자 모드 : 동시성 없음
반면 얻는 것은 큽니다. 관리자 조회는 조건 조합에 따라 대상
건수가 수십 건에서 수백만 건까지 벌어집니다. 이 편차 폭 자체가
단일 플랜으로는 커버 불가능하다는 근거입니다.
────────────────────────────────────────────
5. SSMS 로는 스니핑을 테스트할 수 없습니다
────────────────────────────────────────────
가장 실무적인 함정입니다.
SSMS 에서 DECLARE 로 변수를 선언해 실행하면 파라미터 스니핑은
발생하지 않습니다. 로컬 변수는 컴파일 시점에 값이 없는 것으로
취급되어 density vector 기반 추정을 받습니다.
즉 OPTIMIZE FOR UNKNOWN 과 동등합니다.
여기서 비직관적인 조합이 나옵니다.
DECLARE @id INT = 12345;
-- 스니핑 없음 (density)
SELECT ... WHERE member_id = @id;
-- 리터럴과 동일 (히스토그램)
SELECT ... WHERE member_id = @id OPTION (RECOMPILE);
-- 위와 동일한 플랜
SELECT ... WHERE member_id = 12345;
같은 변수인데 RECOMPILE 유무로 플랜이 갈립니다.
PEO 가 로컬 변수까지 상수로 치환하기 때문입니다.
애플리케이션의 실제 실행 경로를 재현하려면 다음 중 하나를
사용해야 합니다.
-- (1) sp_executesql : ADO.NET / JDBC 파라미터 바인딩과 동일 경로
EXEC sp_executesql
N'SELECT ... WHERE member_id = @id',
N'@id INT',
@id = 12345;
-- (2) sp_prepexec : RPC 레벨 prepared statement 재현
-- (3) 저장 프로시저로 감싸서 호출
────────────────────────────────────────────
6. 검증 : 실행 플랜 XML 의 <ParameterList>
────────────────────────────────────────────
- ParameterCompiledValue 가 존재
→ 스니핑 발생. 이 값 기준으로 플랜이 생성됨
- ParameterCompiledValue 가 없음
→ 로컬 변수. 스니핑 안 됨. 테스트 무효
- ParameterCompiledValue != ParameterRuntimeValue
→ 파라미터 스니핑 문제 발생 중
세 번째가 원인 규명의 결정타입니다. 컴파일 값과 런타임 값이
다르다는 것은, 지금 이 실행이 다른 값에 맞춰진 플랜을 빌려
쓰고 있다는 직접 증거입니다.
────────────────────────────────────────────
7. 적용 판단 체크리스트
────────────────────────────────────────────
RECOMPILE 적용 전 확인 사항입니다.
1) 파라미터 값에 따라 대상 건수가 자릿수 단위로 달라지는가
2) 해당 쿼리의 실행 빈도가 낮은가 (분당 수십 회 이하 수준)
3) 플랜에 메모리 그랜트를 요구하는 연산자
(Sort, Hash Match) 가 있는가
4) ParameterCompiledValue != ParameterRuntimeValue 가
실측으로 확인되었는가
1, 2 번이 함께 참이면 적용 근거가 성립하고,
3, 4 번이 그 근거를 실측으로 뒷받침합니다.
반대로 2 번이 거짓이면 다른 대안 - 플랜 가이드,
OPTIMIZE FOR 대표값 고정, 쿼리 분기 - 를 먼저 검토해야 합니다.