매개변수 스니핑(parameter sniffing)이란?
우선, 매개변수 스니핑에 대해 두려워하지 마십시오. 예상되는(정상) 동작입니다.
저장 프로시저가 컴파일되거나 다시 컴파일되면 해당 호출에 대해 전달된 매개변수 값이 "스니핑"되어 카디널리티 추정에 사용됩니다. 최종 효과는 특정 매개변수 값이 쿼리에서 리터럴로 사용된 것처럼 계획이 최적화된다는 것입니다.
다음 저장 프로시저를 예로 들어 보겠습니다.
create procedure dbo.SearchProducts @Keyword varchar(100) as
select * from Products where Keyword like @Keyword테이블에 약 100,000개의 행이 있고 키워드 열에 단일 열 비클러스터형 인덱스(single-column nonclustered index)가 있다고 가정합니다.
이것을 처음 호출하고 @Keyword='XBOX%' 매개변수를 전달한다고 가정해 보겠습니다. XBOX로 시작하는 키워드가 있는 테이블의 행 수가 매우 적다고 가정합니다. 아마도 수십 개의 행일 것입니다. 최적화 프로그램은 키워드 열의 인덱스를 사용하여 LIKE를 평가한 다음 책갈피 조회를 사용하여 행에 대한 다른 열을 검색하는 쿼리 계획을 사용하도록 선택할 수 있습니다.
이 index seek + bookmark lookup plan 은 캐시되어 후속 절차 실행에 재사용됩니다.
매개변수 스니핑(Parameter Sniffing)으로 인한 성능 문제
그러나 미래의 어느 시점에서 서버는 저장 프로시저에 대한 새 계획을 컴파일/재컴파일해야 합니다(이전 계획은 캐시에서 만료되었거나 Products 테이블에서 시작된 통계 자동 업데이트 등 ). 불행히도 새 계획을 컴파일한 프로시저의 특정 실행에는 'KINECT%'의 @Keyword 매개변수가 있었습니다. 필터 'KINECT%'가 테이블 행의 10%를 반환한다고 가정합니다. 이 매개변수를 사용하여 프로시저를 컴파일할 때 SQL은 전체 테이블 스캔을 사용하는 쿼리 계획을 선택할 수 있습니다. 이 계획은 'KINECT%' 매개변수에 이상적이지만 다른 더 선택적인 검색 기준에 대해서는 끔찍한 계획이 될 것입니다.
불행히도 재컴파일 후에 테이블 스캔 계획도 캐시되어 재사용됩니다.
보다 일반적인 매개변수 값을 사용하는 후속 실행의 성능은 저하됩니다.
매개변수 스니핑을 통해 SQL은 실제로 저장 프로시저에 전달되는 매개변수 유형에 맞는 계획을 컴파일할 수 있습니다. 일반적으로 이 기능을 사용하면 저장 프로시저 실행 계획을 보다 효율적으로 수행할 수 있지만 모든 것이 예상대로 작동하기 위한 핵심 요구 사항은 컴파일에 사용되는 매개 변수 값이 "일반"이어야 한다는 것입니다. 불행히도 이 가상의 예에서 설명하는 것처럼 프로시저 또는 매개변수화된 쿼리가 때때로 비정형 매개변수로 실행될 수 있습니다(이러한 경우 데이터 왜곡이 종종 발생합니다).
매개변수 스니핑 성능 문제는 모든 종류의 쿼리에 영향을 줄 수 있지만 LIKE(위에서 설명한 예와 같이)를 사용하는 쿼리는 특히 이러한 종류의 문제에 취약합니다. 매개변수 스니핑으로 인해 발생하는 성능 문제는 일반적으로 By Design으로 간주됩니다.
행동 계획(Action plan):
SQL Server 2005부터 단일 저장 프로시저에 대한 전체 실행 계획을 컴파일하는 대신 개별 계획을 다시 컴파일할 수 있는 새로운 기능이 있습니다. 성능 문제의 영향을 받는 경우 아래에서 몇 가지 해결 방법을 찾을 수 있습니다.
- 매개변수에 직접 표시되지 않는 더미 변수를 사용하면 아래 예와 같이 재컴파일 힌트를 추가하지 않고도 실행 계획의 안정성을 보장할 수 있습니다.
create procedure dbo.SearchProducts
@Keyword varchar(100)
As
Declare @Keyworddummy as varchar(100)
Set @Keyworddummy = @Keyword
select * from Products where Keyword like @Keyworddummy- 이와 유사한 상황을 방지하기 위해 다음 쿼리 옵션을 사용할 수 있습니다.
OPTIMIZE FOR
RECOMPILE- 일괄 처리 중 자동 업데이트 통계 비활성화 (Disable auto-update statistics during the batch)
출처:
