Adaptive Plan Optimization (a.k.a. Multiple Plan) 확장
Optional Parameter Plan Optimization (이하 OPPO)
하나의 쿼리가 한 번의 하나의 실행 계획만 가진다는 기본 구조로 인해 생기는 부작용을 해결하기 위해 SQL Server는 Multiple Plan 전략을 도입하기 시작했습니다, 일명 Adaptive Plan Optimization입니다. 이를 위해 SQL Server 2022에서 처음 Parameter Sensitive Plan Optimization(PSPO)이 도입되었습니다(필자는 다른 방식의 PSPO를 SQL 개발팀에 제안하고 싶습니다, 현재의 구현 방식은 실용성 등이 떨어집니다 그래서 아직은 많이 아쉽습니다). 이 기능을 활용하는 것이 OPPO이므로 사전 지식으로 필요하므로 모르시는 분은 미리 살펴보시기 바랍니다. 참고로, 2025에서는 I/U/D/Merge에 대해서도 PSPO가 지원되는 것으로 확장되었습니다 이에 대해서는 다루지 않을 것이므로 링크의 문서를 참고하세요.
그럼 OPPO는 무엇인지, 왜 도입되었는지, 어떻게 사용할 수 있는지 살펴보겠습니다.
1. 만능조회 or 동적검색 쿼리 or Kitchen Sink
해외에서는 Kitchen Sink라는 표현도 쓰더군요^^ 일명 만능 조회 쿼리는 오랜 기간 실무에서 잘못된 방식으로 작성된 경우가 많아 인덱스를 사용하지 못하거나 쿼리 최적화에 방해가 되어 고부하 쿼리를 만드는 주 요인 중의 하나입니다.
대표적인 형식이 바로 논리 연산자 OR를 이용한 Short Circuit 방식입니다
SELECT * INTO dbo.OrdersOPPO
FROM EPlanHeap.dbo.BigOrders
GO
CREATE UNIQUE INDEX IX_OrdersOPPO_OrderID ON dbo.OrdersOPPO(OrderID)
GO
CREATE OR ALTER PROC dbo.up_GetOrders
@OrderID int = NULL
AS
SELECT *
FROM dbo.OrdersOPPO
WHERE (@OrderID IS NULL OR OrderID = @OrderID)
GO
EXEC dbo.up_GetOrders @OrderID = 10248;
EXEC dbo.up_GetOrders;
이 패턴의 쿼리가 호출되면 PSPO 기능을 통해 조건절에서 @OrderID IS NULL 인 경우와 그렇지 않는 경우에 최적화된 플랜을 각각 생성하고 재사용합니다. 결과로 확인해보죠.
아래는 Query Optimizer에 의해 PSPO가 적용된 결과 형태입니다.
SELECT *
FROM dbo.OrdersOPPO
WHERE (@OrderID IS NULL OR OrderID = @OrderID)
option (PLAN PER VALUE(ObjectID = 450100644, QueryVariantID = 1, optional_predicate(@OrderID IS NULL)))
아래 그림-1은 실행 계획 SELECT 연산자의 속성에서 확인할 수 있는 OPPO 정보입니다.
[그림-1] OPPO 실행 계획 중 SELECT 연산자의 속성
다음 그림-2와 3은 @OrderID 매개변수에 값이 넘어온 경우와 NULL 인 경우에 각각 다른 실행 계획이 생성되었음을 보여줍니다. 이러한 동작이 OPPO입니다.
[그림-2] EXEC dbo.up_GetOrders @OrderID = 10248 일 때 실행 계획
[그림-3] EXEC dbo.up_GetOrders 일 때 실행 계획
2. 고려사항
- SQL Server 2025부터 지원
- DB 구성 옵션, OPTIONAL_PARAMETER_OPTIMIZATION = ON(default) 로 조정
- 쿼리 단위 힌트, OPTION(USE HINT(‘DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION’)) 로 조정
- IS NULL + OR 방식만 가능
- 하나의 조건절만 가능
마치면서
SQL Server의 Multi Plan 전략 즉 Adaptive Plan Optimization이 시작되었습니다. SQL Server 개발팀(정확히는 쿼리 최적화팀)이 제품 향상을 위해 이러한 부분에 관심을 가지고 있고 실제로 구현하고 있다는 점 만으로도 사용자의 한 사람으로서 감사한 마음이며 응원하고 있습니다. 다만 초기 시점이라 아직은 실무에 적용하기에 PSPO나 OPPO가 부족한 부분 아쉬운 부분들이 있습니다.
그럼에도 불구하고 실제로 적용될 수 있는 단편적인 유형의 쿼리라면 이전보다 편리하고 안전한 방법이 될 수 있고 앞으로 버전 업그레이드를 통해 보다 실용적인 형태로 개선될 것으로 기대하고 있습니다. SQL Server Query Optimizer의 새로운 도전과 진보는 계속됩니다.
감사합니다.
