Parameter sniffing
Concept
SQL Server 계열 엔진이 컴파일 시점에 확인된 매개변수 값에 맞춰 실행 계획을 컴파일하고 캐시된 계획을 재사용하는 동작으로, 값의 차이가 매우 큰 경우 성능이 저하될 수 있습니다. 매개변수 민감성(parameter sensitivity)이라고도 합니다.
Description
매개변수 스니핑(Parameter sniffing)은 SQL Server 계열 데이터베이스 엔진에서 나타나는 동작으로, 컴파일 시점에 확인된 매개변수 값에 맞춰 쿼리 계획을 컴파일한 뒤 그 캐시된 계획을 재사용하는 것을 말합니다. 매개변수 민감성(parameter sensitivity)이라고도 합니다. 이 동작 자체가 결함은 아닙니다. 엔진은 쿼리를 컴파일하거나 다시 컴파일할 때 현재 매개변수 값을 살펴보고 이를 쿼리 최적화기에 전달하므로, 해당 값에 대해 보다 효율적인 실행 계획을 만들 수 있습니다. 문제는 캐시된 계획이 나중에 원래 값과 매우 다르게 보이는 매개변수 값에 사용될 때 발생합니다. 한 값에 적합한 계획이 다른 값에서는 성능이 나쁠 수 있기 때문입니다.
SQL Server용 Microsoft의 쿼리 처리 아키텍처 가이드는 매개변수 민감성을 다루는 절에서 매개변수 스니핑을 설명합니다. 해당 가이드에 따르면 이 과정은 저장 프로시저, sp_executesql을 통해 실행되는 쿼리, 준비된 쿼리에 적용됩니다. 이는 계획 재사용과 직접 맞물려 있는데, 컴파일된 계획은 계획 캐시에 저장되어 이후 실행에서 다시 사용될 수 있기 때문입니다. 따라서 컴파일을 유발한 첫 번째 값 집합이 이후 호출이 받게 될 계획의 형태를 결정하며, 어떤 호출이 계획을 컴파일했는지에 따라 결과가 달라질 수 있는 이유가 여기에 있습니다.
가이드는 한 가지 해결책으로 OPTION (RECOMPILE) 쿼리 힌트를 언급합니다. 이 힌트를 사용하면 최적화기는 매개변수와 지역 변수를 컴파일 시점의 상수로 취급하며, 이 값들은 리터럴로 접힐(folded) 수 있습니다. 사용되는 값은 원래 전달된 값이 아니라 문이 실행되기 직전에 유효한 값입니다. 이는 입력 데이터의 변동성이 크거나 치우쳐 있을 때 도움이 될 수 있습니다. 가이드는 또한 이 힌트가 실행될 때마다 쿼리를 다시 컴파일하므로 비용이 많이 들 수 있으며, 도입하기 전에 전체 워크로드에 미치는 영향을 측정할 것을 권고합니다. 아울러 더 읽을거리로 매개변수 민감 계획(Parameter Sensitive Plan) 최적화에 관한 별도 문서도 안내합니다.
같은 문서에서 다루는 관련 세부 사항은 지역 변수에 관한 것입니다. 지역 변수의 값은 컴파일 시점에 스니핑되지 않습니다. 대신 최적화기는 밀도 정보를 바탕으로 추정하며, 통계가 없는 경우에는 등치 및 범위 비교에 대해 고정된 백분율 추측값을 사용합니다. 이 차이는 느린 쿼리를 진단할 때 중요합니다. 매개변수와 지역 변수는 서로 다른 추정치와 그에 따른 다른 계획으로 이어질 수 있기 때문입니다.
실무에서 매개변수 스니핑은 어떤 입력에서는 빠르고 다른 입력에서는 느리게 실행되는 쿼리로 나타나거나, 계획이 제거되고 다시 컴파일된 뒤 성능이 바뀌는 형태로 나타납니다. M365.FM 에피소드 "Fix Bad Data Upstream with T-SQL Contracts"는 매개변수 스니핑을 엔터프라이즈 환경에서 흔히 발생하는 장애 중 하나로 꼽으며, 무작위성처럼 보일 수 있다고 설명합니다. 이 에피소드는 또한 계획 형태를 거버넌스 문제로 다루어, 계획 형태가 바뀌면 비용과 안정성도 함께 바뀐다고 말합니다. 즉 계획 동작이 비용 안정성과 운영 위험으로 이어진다는 관점입니다.
"Design Reliable and Resilient Azure Architectures" 에피소드는 Azure SQL Database의 문제 해결 흐름에서 매개변수 스니핑을 다룹니다. 이 에피소드는 Intelligent Insights와 Query Performance Insight를 활성화하고, 장시간 실행되는 쿼리를 수집하고, 실행 계획을 검토하며, 누락된 인덱스나 매개변수 스니핑을 식별할 것을 제안합니다. 또한 Azure SQL의 자동 튜닝 권장 사항과 Azure Advisor를 통한 인덱스 제안, 그리고 쿼리 최적화, 적절한 인덱싱, DTU 또는 vCore 확장 또는 Hyperscale로의 전환 검토도 언급합니다.
이 자료들을 종합하면, 매개변수 스니핑은 계획 캐싱에 따른 절충으로 볼 수 있습니다. 컴파일된 계획을 재사용하면 컴파일 작업을 줄일 수 있지만, 한 매개변수 값에 맞춰 조정된 계획은 다른 값에는 적합하지 않을 수 있습니다. 이와 관련된 일반적인 결정에는 실행 계획 검토, 데이터 분포가 치우쳐 있는지 확인, 그리고 OPTION (RECOMPILE) 같은 옵션의 컴파일 비용과 더 적합한 계획으로 얻는 이점을 비교하는 작업이 포함됩니다.
Overview compiled with AI from M365.FM episodes and the facts on this page. You can improve it with the edit button below.
| Type | Concept |
|---|
Frequently asked questions
Questions marked with AI were written with AI from the facts on this page.
매개변수 스니핑이란 무엇입니까? (AI)
SQL Server 계열 엔진이 컴파일 시점에 확인된 매개변수 값에 맞춰 쿼리 계획을 컴파일하고 캐시된 계획을 재사용하는 동작입니다. 이후 매개변수 값이 크게 다르면 성능이 나빠질 수 있습니다. 매개변수 민감성(parameter sensitivity)이라고도 합니다.
OPTION (RECOMPILE)은 매개변수 스니핑 문제에 어떤 영향을 줄 수 있습니까? (AI)
Microsoft의 쿼리 처리 가이드에 따르면, 이 힌트는 최적화기가 매개변수와 지역 변수를 컴파일 시점의 상수로 취급하게 하며, 이 상수는 리터럴로 접힐 수 있습니다. 이는 변동성이 크거나 치우친 데이터에 도움이 될 수 있습니다. 다만 쿼리가 실행될 때마다 다시 컴파일되므로 비용이 많이 들 수 있어, 먼저 전체 워크로드에 미치는 영향을 측정해야 합니다.
지역 변수도 매개변수와 같은 방식으로 스니핑됩니까? (AI)
아닙니다. 문서에 따르면 지역 변수의 값은 컴파일 시점에 스니핑되지 않습니다. 대신 최적화기는 밀도 정보를 바탕으로 추정하며, 통계가 없으면 등치 및 범위 비교에 대해 고정된 백분율 추측값을 사용합니다.
Community wiki
Short info above, community knowledge below: add practical tips, examples or corrections. New contributions are reviewed first; trusted members publish directly.
Experts and companies for this topic
Nobody has listed this topic yet. Add it to your profile.
Maintained by Mirko Peters (Microsoft MVP) · Last updated: