[MSSQL][SQL SERVER] SQL 성능 최적화: 캐싱과 동적 평가(Dynamic Evaluation)의 함정

연말연초라 그런지 프로그램 사용량이 늘어 성능 이슈가 늘었다. 업무상 성능 개선이 시급히 필요한 시점에서 SQL 튜닝을 하면서 새로 배운 점을 정리하고자 한다. (쿼리나 함수명, 변숫값은 예제로 새로 작성하였다.)

문제의 쿼리: 병목

WHERE a.Status LIKE '%' + @InputStatus + '%' -- 문제 부분

실행계획을 분석해보니 위의 LIKE 연산자 조건 비교 부분에서 프로시저 전체의 56%에 달하는 비용이 발생하고 있었다. LIKE 연산자로 검색하려는 문자열의 앞에 '%'가 올 경우 인덱스를 활용하지 못한다. LIKE 조건에 일치하는 패턴을 찾아야 하는데 찾고자 하는 값이 문자열의 어느 부분에서 시작하는지 알 수 없으므로 전체를 스캔해야 하기 때문이다.

@InputStatus는 'Ordered,Delivered,Shipped'와 같이 상태값을  ', '로 이어 붙인 문자열이기 때문에 굳이 LIKE연산자로 값을 찾을 필요가 없었다. ', '를 기준으로 문자열을 나눠서 테이블로 반환한 다음 IN 연산자를 사용하면 된다.

그러나 비즈니스 로직이 반영되어야 하므로 내장함수인 STRING_SPLIT는 사용할 수 없었다. 내부 비즈니스 로직에 따라문자열을 나눠 테이블로 반환하는 UDF_SPLIT라는 사용자 정의 함수가 이미 정의되어 있어 해당 함수를 사용하여 IN연산자를 활용하기로 했다.

UDF_SPLIT 함수를 통해 반환된 테이블 예시

 

WHERE a.Status IN (SELECT value FROM dbo.UDF_SPLIT(@InputStatus)) -- '%'와 LIKE 연산자 제거

이렇게 변경했더니 실행계획에서 LikeRange 연산은 실행계획에서 사라졌으나 속도 향상은 미미했다. 실행계획에서 Compute Scalar에 매번 사용자 정의 함수 UDF_SPLIT가 있단 걸 확인하였다. 사용자 정의 함수가 행별로 실행되고 있었던 것이다. 해당 프로시저 전반적으로 UDF_SPLIT이라는 사용자 정의 함수가 많이 사용되었으므로 반복적인 함수 호출과 연산이 늘어나 성능 개선 효과가 미미하다고 판단하였다.

 

 

캐싱

반복적으로 호출해야 한다면 함수실행값을 캐싱해두는 것이 효율적이다. 함수 실행값을 고정하기 위해 테이블 변수를 선언하고 결괏값을 입력했다.

DECLARE @StatusTable TABLE (Status NVARCHAR(10));

-- 문자열을 분리하여 테이블 변수에 저장
INSERT INTO @StatusTable
SELECT value FROM dbo.UDF_SPLIT(@InputStatus, ',');

SELECT 'X'
FROM Orders a
WHERE a.Status IN (SELECT Status FROM @StatusTable) -- 캐싱한 변수 사용

이렇게 캐싱까지 적용한 뒤 다시 쿼리를 실행시켰다. 프로시저 전반에서 여러 번 호출되는 함수이기에 캐싱을 하고 나면 상당한 성능향상을 기대했다.

그러나 기대와 다르게 성능은 전혀 개선되지 않았다.

 

상수?

실행계획에는 여전히 Compute Scalar(DEFINE:([Expr1078] = [Shipment].[Status]))가 있었다. 함수 실행값을 변수에 대입하면 상수처럼 프로시저를 컴파일할 때 최적화할 거라 생각했다. 하지만 변경 후에도 여전히 컴파일 이후, 실행시점에서 Status 조걸 절을 행별로 판단하고 있었다.

 

 

동적 판단(Dynamic Evaluation, 런타임 판단)

SQL Server에서 옵티마이저는 컴파일 시점에 변수 값이 명확한 경우, 해당 값을 기반으로 최적화 계획을 수립한다. 이것을 정적 판단(Static Evaluation)이라 한다. 예를 들어, @InputStatus가 'Ordered'로 고정되어 있다면, 옵티마이저는 이를 반영한 효율적인 실행 계획을 생성한다.

반대로 변수 값이 실행 시점까지 결정되지 않는 경우, 이를 동적 판단(Dynamic Evaluation)이라고 합니다. 이 경우, 옵티마이저는 변수 값을 알 수 없으므로 테이블 통계를 기반으로 한 최적화를 수행할 수 없다. 대신, 변수 값에 관계없이 작동 가능한 실행 계획을 생성할 가능성이 높아진다. 쿼리를 수정하더라도 실행계획을 보기 전엔 어떻게 작동하는지 확신할 수 없기 때문에 동적 판단에 대해서는 간과하기 쉽다. 하지만 발생한다면 상당한 성능저해를 일으킨다.

MSSQL 공식 문서에는 이러한 용어가 직접 정의되어 있지 않지만, 정적 판단과 동적 판단 혹은 런타임 판단으로 불린다.

 

동적 판단(Dynamic Evaluation)과 정적 판단(Static Evaluation)

프로그래밍 언어로 예시를 들자면 아래와 같다.

구분	동적 평가	정적 평가
시점	실행시점에서 조건을 판단	조건을 미리 런타임 전에 판단
예시 코드	status_list = ['Ordered', 'Shipped', 'Delivered'] result = [] for status in status_list:     if 'ed' in status:          result.append(status) print(result)	result = ['Ordered', 'Shipped', 'Delivered']  print(result)
실행 결과	['Ordered', 'Shipped', 'Delivered']	['Ordered', 'Shipped', 'Delivered']

프로그래밍으로 비교하면 그 차이가 더 분명하게 보인다. 함수 실행 시점에서 일일히 판단하는 것과 이미 값에 대한 판단을 마친 후 실행하는 것은 메모리와 I/O 측면에 큰 성능 차이를 보인다. 예시의 리스트에는 원소가 3개뿐이지만 리스트의 길이가 길어질수록 그 차이는 더욱 커진다.

미리 캐싱을 해뒀기 때문에 상수처럼 작동할 것이라 생각했지만 프로시저 내부에서 실행할 때는 매번 동작 판단이 일어나고 있었다. 서브쿼리 내부에서 조건절에 변수를 사용했기 때문이다.

WHERE a.Status IN (SELECT Status FROM @StatusTable)

@StatusTable이라 선언한 테이블 변수는 실행시점에만 존재하는 변수이고 옵티마이저는 이에 대한 통계정보를 얻지 못 한다. 그러므로 동적 판단의 영역이 된다.

 

 

임시 테이블을 이용한 캐싱

그렇다면 테이블 값을 미리 캐싱하기 위해서는 어떻게 해야하는가? 임시테이블을 선언해야 한다. 

CREATE TABLE #StatusTable (Status NVARCHAR(10)); -- 임시 테이블 생성

-- 임시 테이블에 캐싱
INSERT INTO #StatusTable
SELECT value FROM dbo.UDF_SPLIT(@InputStatus, ',');

-- 캐싱한 임시 테이블을 사용하여 조회
SELECT 'X'
FROM Orders a
WHERE a.Status IN (SELECT Status FROM #StatusTable); -- 임시 테이블 사용

-- 임시 테이블 삭제
DROP TABLE #StatusTable;

위와 같은 방법으로 캐싱을 할 경우 원하는 성능 개선을 기대할 수 있다. 임시 테이블은 생성했다면 반드시 drop 해야 한다. 프로시저 내부에서 선언한 임시 테이블은 프로시저 내부에서만 생명주기를 가진다지만 명시적으로 작성하여 메모리 낭비를 막는다.

 

테이블 변수와 임시 테이블의 차이

테이블 변수와 임시 테이블의 어떤 차이점이 동적 판단의 유무에 영향을 미치는지를 정리하였다.

	테이블 변수	임시 테이블
스코프	배치(Batch) 또는 프로시저 내부에서만 유효	세션(Session) 또는 프로시저 종료 시까지 유효
통계 정보	없음 (항상 행 수를 1로 추정)	존재함 (행 수 및 분포 정보 활용 가능)
최적화 정보	통계 정보 부족으로 고정된 계획 사용	통계 정보를 통해 동적인 계획 사용
재컴파일	발생하지 않음	발생

임시 테이블은 실행계획에 반영할 수 있는 통계정보가 있다. 또한 통계정보가 변경됐을 경우 옵티마이저가 리컴파일 할 수 있기 때문에 임시 테이블은 정적 판단이 가능하다.

 

 

동적 판단이 일어나는 케이스

SQL에서 변수를 사용했다고 해서 반드시 동적 판단이 발생하지는 않는다.

WHERE Status = @InputStatus

이렇게 변수 자체를 직접 비교할 때는 동적 판단이 발생하지 않는다.

LIKE 연산자 사용자 검색문자열 앞에 %을 사용할 경우

처음 문제가 된 쿼리인 LIKE '%' + @InputStatus + '%'도 이 경우에 해당한다. 매칭 범위를 가늠할 수 없으므로 옵티마이저가 인덱스를 활용할 수 없기 때문이다.

테이블 변수를 사용할 경우

테이블 변수의 경우 통계정보가 제공되지 않기 때문에 동적판단이 일어난다.

변수에 연산식을 적용할 경우

WHERE Salary > @InputParam * 1.3

변수를 직접 비교하지 않고 연산할 경우에도 동적 판단이 발생한다.

 

마무리

SQL 성능 최적화를 고민하며 지금까지 동적 판단을 간과해왔다. 처음 접근했던 테이블 변수를 통한 캐싱이 실패했지만 이를 통해 SQL 내에서의 이 기회에 동적 판단에 대해 알아볼 수 있었다. SQL 튜닝이 단지 쿼리를 수정하는 것과는 다르다는 것, 조회되는 결과는 같더라도 내부에서 옵티마이저가 작동하는 방식은 내 예상과 전혀 다를 수 있다는 걸 체감했다. 실행계획을 분석하고 실제로 어떤 프로세스를 거쳐 결과가 도출되는지 더 알아보고 관심을 가져야겠다.

---

https://www.geeksforgeeks.org/sql-correlated-subqueries/

https://learn.microsoft.com/ko-kr/sql/relational-databases/performance/execution-plans?view=sql-server-ver16&source=recommendations

https://learn.microsoft.com/ko-kr/azure/azure-sql/managed-instance/identify-query-performance-issues?view=azuresql

https://learn.microsoft.com/ko-kr/sql/relational-databases/performance/subqueries?view=sql-server-ver16