소개
SQL Server 2022에서 가장 핫한 기능이 바로 이제 살펴볼 Parameter Sensitive Plan(PSP) Optimization 일 겁니다. SQL Server 기반으로 개발을 할 때는 일반적으로 저장 프로시저(또는 매개변수 쿼리도 마찬가지)를 많이 사용하고 그로 인해 경험할 수 있는 대표적인 쿼리 성능 이슈인 Parameter Sniffing 동작이 있습니다. 이 동작이 좋지 않은 프로시저 시나리오를 만나면 성능 문제가 발생하고 심한 경우 서버 성능에 큰 부정적 영향을 끼칠 수 있으므로 우리는 그 동안 이 문제를 해결 또는 조치하기 위한 다양한 접근 방법을 취해 왔습니다.
SQL Server 개발팀도 이 성능 문제 해결을 위한 다양한 고민과 노력을 기울이고 있다고 추측합니다, SQL Server 2022에서 처음 도입된 것이 바로 PSP 최적화입니다. 우선 기본적인 내용들을 살펴보죠.
참고. PSP 최적화에 대한 전반적인 소개는 MS 문서 “매개 변수 중요한 계획 최적화(Parameter Sensitive Plan Optimization)”를 참조하세요.
Parameter Sensitive Plan(PSP, 일명 Bad Parameter Sniffing) 문제
[쿼리-1]
SELECT ……
FROM Warehouse.StockItems AS s
WHERE SupplierID = @SupplierID
ORDER BY StockItemName
그 동안 (Bad) Parameter Sniffing으로 불리웠던 저장 프로시저 성능 문제에 대해 또 새로운 용어가 공식적으로 사용되나 봅니다. 두 용어가 의미하는 바는 정확히 같지 않지만 처음 접하는 상황임을 감안해서 여기서는 구분없이 사용하겠습니다.
위 쿼리-1를 포함한 저장 프로시저를 예로 들어보죠, StockItems 테이블의 SupplierID 열은 값에 따라서 행 수의 큰 차이가 있습니다 예를 들어,
- SupplierID = 1은 8건
- SupplierID = 4는 3,999,847 건의 행 수를 가지고 있습니다.
따라서 @SupplierID 매개변수의 값에 따라 SELECT 행 수의 큰 차이가 나고(이것이 Parameter Sniffing) 이로 인해 쿼리 실행 계획의 내용이 달라져야 하는 즉 Parameter에 따라 Sensitive한 Plan을 가져야하는 쿼리와 프로시저에 해당합니다. 그런데 저장 프로시저(혹은 매개변수 쿼리)는 첫 실행 시 컴파일 및 최적화 과정을 거치며 그 때 제공된 매개변수 값으로 행 수를 추정하고 이를 기반으로 실행 계획의 내용(예, Index Seek or Index Scan)의 결정 및 실행하며 해당 계획을 Cache에 저장한 뒤 이후 실행에서는 저장된 실행 계획을 계속 재사용한다는 것이죠.
실제 실행 계획과 함께 예를 들어 보죠. 만일 첫 실행 시 @SupplierID = 4로 호출된다면 예상 행 수가 약 4백만건으로 매우 많으므로 StockItems 테이블에 PK를 이용해서 Clustered Index Scan으로 데이터를 검색하며 Parallelism 연산자를 통해서 병렬로 처리하도록 실행 계획이 생성되었습니다. 여기까지는 OK.
[그림] 첫 실행 시 @SupplierID = 4를 사용한 경우에 생성된 쿼리 실행 계획
그런데 두 번째 실행 시 @SupplierID = 1로 호출하는 경우 예상 행 수가 8건으로 적기 때문에 정상적이라면 SupplierID 열에 Index를 이용해서 Index Seek 연산자로 처리하고 Parallelsim 연산자는 불필요하지만 실제로는 이전에 생성되고 Cache에 저장된 Clustered Index Scan + Parallelism 연산자를 가진 부적합한 실행 계획을 재사용하면서 결과적으로 느리거나 부하가 큰 쿼리 성능 문제을 가지게 됩니다.
[그림] 두 번째 실행 시 @SupplierID = 1를 사용한 경우에 재사용된 쿼리 실행 계획
SQL Server 2022의 새로운 기능, PSP 최적화(Optimization)
이러한 문제는 특히 대용량 테이블의 데이터 처리 환경에서 서비스나 업무의 심각한 영향을 미치거나 심지어 서비스 불능 상태로 빠지기도 합니다. 따라서 해당 문제를 해결하고 튜닝하기 위한 여러가지 접근 방법들을 프로시저나 쿼리에 따라 선택해서 적용해 왔습니다, 아마도 앞으로 꽤 긴 시간 동안 이와 같은 작업들은 계속될 것으로 예상합니다만.
SQL Server 2022에서 드디어 PSP 문제를 자동으로 조치하기 위한 첫 번째 방법을 도입했습니다, PSP Optimization입니다. 그 방법을 쉽고 간략하게 축약해서 설명하자면, 위 예제에서 StockItems 테이블의 SupplierID 열처럼 불균등한 분포(행 수)를 가진 열에 검색 조건을 가진 쿼리의 경우 해당 쿼리를 매개변수 쿼리 형태로 재 구성하고 예상 행 수의 범위를 정의한 뒤(예, 10건 이하, 1백만건 이하 등) 해당 범위에 따라 적절한 실행 계획을 생성 및 저장하고 실제 실행 시 매개변수 값의 예상 행 수에 따라 해당 실행 계획을 재사용하도록 지원하는 것입니다.
위 예제에서 @SupplierID = 1로 호출하는 경우, PSP Optimization이 적용되면 아래 그림과 같이 Index Seek 연산자를 사용하는 최적화된 실행 계획으로 동작하는 것을 볼 수 있습니다. Wow~
[그림] 두 번째 실행 시 @SupplierID = 1를 사용한 경우에 올바르게 선택된 쿼리 실행 계획
정리
개인적으로 컨셉 자체도 마음에 들지만 무엇보다 SQL Server Query Optimizing 팀이 Parameter Sniffing 이슈를 해결하는데 도움이 되는 솔루션에 큰 관심을 가지고 이를 지원하기 위해 노력하고 있다는 점이 무척 감동이었습니다. 이번엔 “소개”의 시간이라 이 정도로 간단하게 살펴봤습니다만 좀 더 깊이있게 들어가 보면 지원하는 조건식의 유형, 불균등 분포 유형 문제가 아닌 경우, 조건자의 개수 등등 실제로 실무에서 적용하기에 만만치 않은 부분들이 많이 있습니다.
하지만 이제 막 도입한 기능이라는 점을 고려해서 앞으로 점점 더 좋아져서 보다 실용적인 기능이 되기를 기대해 봅니다.
모두 건강하세요.
김정선 드림
