매개변수 스니핑 (with recompile)

테스트 환경

 - OS : Windows 2008 R2 DataCenter

 - DBMS : SQL Server 2012 Enterprise Edition + SP4

 - 참고문서 : https://docs.microsoft.com/ko-kr/previous-versions/sql/sql-server-2012/ms190439(v=sql.110)

 

내용

 

 - 개요

저장프로시저의 경우 최초 실행될 때 실행계획이 프로시저캐시 라는 메모리에 저장되어 추후 해당 프로시저가 실행될때 실행계획이 재사용된다.
그런데, 최초 프로시저가 실행될때 매개변수에 하루 등 비교적 짧은 기간 검색이 사용되어 해당 컬럼의 인덱스를 사용하는 실행계획이 저장되었다. 이후 실행될때에는 1년과 같이 긴 기간 검색이 발생한 경우는 인덱스 사용 검색보다는 테이블 풀 스캔이 보다 효율적이지만 메모리에 저장된 프로시저의 실행계획을 재사용(인덱스 사용)하게 됨으로써 테이블 풀스캔보다 비효율이 발생하게 된다.

이런 현상을 매개변수 스니핑 이라고 부른다.

이런 현상을 제어하기 위해 아래와 같은 방법이 존재한다.

- sp_recompile 시스템 저장 프로시저 사용
- with recompile 를 포함해 생성
- with recompile 를 포함해 실행

  - 예제

-- 저장프로시저 생성 USE AdventureWorks2012; GO IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.uspGetAllEmployees; GO CREATE PROCEDURE HumanResources.uspGetAllEmployees AS SET NOCOUNT ON; SELECT LastName, FirstName, Department FROM HumanResources.vEmployeeDepartmentHistory; GO

-- with recompile 옵션과 함께 프로시저 실행 USE AdventureWorks2012; GO EXECUTE HumanResources.uspGetAllEmployees WITH RECOMPILE; GO

with recompile 옵션을 지정하여 프로시저를 실행하면
매모리의 실행계획을 재사용하지 않고 프로시저가 재컴파일되면서 매개변수에 맞게 실행계획을 재수립하게 된다.