[MSSQL] CTE는 결과를 저장하지 않는다: 반복 참조 시 실행 비용 검증

728x90

CTE(Common Table Expression): 실무적 정의와 운영상 중요성

MSSQL에서 CTE는 WITH 절로 정의되는 명명된 결과 집합으로, 이후 이어지는 SELECT, INSERT, UPDATE, DELETE 문 안에서 마치 테이블이나 뷰처럼 참조할 수 있는 구조이다. 파생 테이블(derived table)과 마찬가지로 CTE는 쿼리 실행이 끝나면 소멸하며, 뷰(view)나 인덱싱된 뷰(indexed view)와 달리 물리적으로 저장되지 않는다.

실무에서 가장 많이 발생하는 오해는 "WITH AS로 한 번 정의해두면 여러 번 참조해도 그 결과가 재사용된다"는 가정이다. 이 가정은 틀렸다. 비재귀 CTE(non-recursive CTE)는 옵티마이저 내부에서 매크로처럼 취급되어, 참조되는 시점마다 정의된 쿼리가 해당 위치에 인라인으로 펼쳐진다. 즉 SELECT 문 하나에서 동일 CTE를 N번 참조하면, 옵티마이저는 최대 N개의 독립적인 서브트리를 실행 계획에 만들어낼 수 있고, 이 서브트리 각각이 원본 테이블에 대한 스캔·조인·집계를 별도로 수행할 위험이 있다.

이 특성을 명확히 이해하지 못한 채 대용량 집계나 윈도우 함수를 포함한 CTE를 self-join 형태로 여러 번 참조하면, CPU 사용량과 논리적 읽기(logical reads)가 참조 횟수에 비례해 증가하고, 운영 환경에서 쿼리 응답 시간 급증과 CPU 병목으로 이어진다. DBA는 CTE를 사용한 쿼리를 리뷰할 때 반드시 동일 CTE의 참조 횟수와 실행 계획상의 재계산 여부를 함께 확인해야 한다.

Part 1. CTE 기본 문법 및 사용법 (Basic Syntax)

CTE는 WITH 키워드로 시작하며, 괄호 안에 컬럼 목록을 선택적으로 지정할 수 있다. 하나의 WITH 절 안에 콤마로 구분해 여러 CTE를 순차적으로 정의하고, 뒤에 정의된 CTE가 앞서 정의된 CTE를 참조하는 것도 가능하다.

WITH SalesSummary AS (
    SELECT
        CustomerID,
        SUM(TotalDue)  AS TotalSales,
        COUNT(*)       AS OrderCount
    FROM Sales.SalesOrderHeader
    GROUP BY CustomerID
)
SELECT
    CustomerID,
    TotalSales,
    OrderCount
FROM SalesSummary
WHERE TotalSales > 10000;

위 쿼리는 SalesSummary라는 CTE를 정의하고 단 한 번만 참조한다. 이 경우에는 옵티마이저가 CTE 정의를 그대로 인라인해도 원본 집계 연산이 한 번만 수행되므로 재계산 문제가 발생하지 않는다.

여러 CTE를 체이닝하는 문법도 자주 사용된다.

WITH FirstCte AS (
    SELECT ProductID, SUM(OrderQty) AS TotalQty
    FROM Sales.SalesOrderDetail
    GROUP BY ProductID
),
SecondCte AS (
    SELECT ProductID, TotalQty
    FROM FirstCte
    WHERE TotalQty > 100
)
SELECT * FROM SecondCte;

이 구조 역시 각 CTE가 한 번씩만 참조되므로 문제가 되지 않는다. 문제는 다음 Part에서 다루는, 하나의 CTE를 outer 쿼리에서 두 번 이상 참조하는 패턴에서 시작된다.

Part 2. 내부 동작 메커니즘과 안티패턴

MSSQL 옵티마이저는 비재귀 CTE를 실행 계획을 세우기 전 단계에서 뷰 확장(view expansion)과 유사한 방식으로 처리한다. CTE에 대한 참조는 파싱 트리 상에서 해당 CTE의 SELECT 정의로 치환되며, 이 치환은 쿼리 내 참조 위치마다 개별적으로 이루어진다. 결과적으로 옵티마이저가 최종 실행 계획을 생성하는 시점에는 이미 CTE라는 개념 자체가 사라지고, 기저 테이블에 대한 스캔·조인·집계 연산자들의 트리로 펼쳐진 상태가 된다.

가장 흔한 안티 패턴은 시계열 비교나 순위 비교를 위해 동일 CTE를 self-join 형태로 두 번 참조하는 패턴이다.

WITH DailySales AS (
    SELECT
        OrderDate,
        SUM(TotalDue) AS DailyTotal
    FROM Sales.SalesOrderHeader
    GROUP BY OrderDate
)
SELECT
    Today.OrderDate,
    Today.DailyTotal,
    Yesterday.DailyTotal AS PrevDayTotal
FROM DailySales AS Today
LEFT JOIN DailySales AS Yesterday
    ON Yesterday.OrderDate = DATEADD(DAY, -1, Today.OrderDate);

위 쿼리는 DailySales CTE를 Today와 Yesterday라는 별칭으로 두 번 참조한다. Sales.SalesOrderHeader 테이블에 대한 스캔과 OrderDate 기준 GROUP BY 집계가 Today 쪽 서브트리와 Yesterday 쪽 서브트리에서 각각 독립적으로 수행될 가능성이 높다. 즉 원본 집계 연산이 물리적으로 두 번 수행되는 것이다. 테이블 행 수가 수백만 건이고 집계 대상 컬럼에 적절한 인덱스가 없다면, Full Table Scan과 정렬·집계 비용이 그대로 두 배로 늘어나 대용량 트랜잭션 처리 중 CPU 병목과 쿼리 응답 지연을 유발한다.

이 재계산 여부는 추정 실행 계획(estimated plan)만으로는 확정할 수 없다. 실제 실행 계획(actual execution plan)을 확보해 다음 지점을 확인해야 한다.

Ctrl+M으로 Actual Execution Plan을 켠 뒤 쿼리를 실행한다.
DailySales가 확장된 지점에서 나타나는 Stream Aggregate, Sort, Clustered Index Scan 연산자가 실행 계획 상에 두 개의 독립된 서브트리로 존재하는지 확인한다.
Nested Loop 하위에 위치한 연산자라면 Properties 창(F4)에서 Actual Rebinds와 Actual Rewinds 값을 확인한다. Rebind는 외부 입력 행이 바뀔 때마다 내부 입력을 처음부터 다시 평가한 횟수, Rewind는 이전에 평가한 결과를 그대로 재사용한 횟수를 의미한다. Rebind 값이 0보다 크고 Rewind가 0이라면, 해당 서브트리가 매 반복마다 처음부터 재계산되고 있다는 뜻이다.
연산자에 마우스를 올려 Number of Executions 속성도 함께 확인한다. 이 값이 1보다 크면 해당 서브트리가 물리적으로 여러 번 실행되었다는 직접적인 증거다.

STATISTICS IO와 STATISTICS TIME을 함께 사용하면 논리적 읽기 수치로도 재계산 여부를 교차 검증할 수 있다.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

WITH DailySales AS (
    SELECT OrderDate, SUM(TotalDue) AS DailyTotal
    FROM Sales.SalesOrderHeader
    GROUP BY OrderDate
)
SELECT
    Today.OrderDate,
    Today.DailyTotal,
    Yesterday.DailyTotal AS PrevDayTotal
FROM DailySales AS Today
LEFT JOIN DailySales AS Yesterday
    ON Yesterday.OrderDate = DATEADD(DAY, -1, Today.OrderDate);

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

메시지 창에 출력되는 'Table SalesOrderHeader'의 논리적 읽기(logical reads) 값이 CTE를 한 번만 참조하는 쿼리 대비 얼마나 커지는지 비교하면, 재계산 여부와 그 비용을 수치로 확인할 수 있다. 이 논리적 읽기 값과 Actual Rebinds/Rewinds, Number of Executions 결과는 운영 환경의 실제 테이블과 데이터 분포에 따라 달라지므로, 반드시 대상 서버에서 직접 실행 계획을 캡처해 판단해야 한다.

모든 CTE 참조가 무조건 재계산으로 이어지는 것은 아니다. 옵티마이저가 비용 기반 최적화 과정에서 Common Subexpression Spool 같은 스풀 연산자를 삽입해 결과를 임시로 캐시하고 재사용하는 경우도 있다. 그러나 이는 옵티마이저의 재량적 판단에 의한 결과이지, CTE라는 구문 자체가 보장하는 동작이 아니다. 실무에서는 이 재사용 여부를 코드만 보고 추정하지 말고, 반드시 실행 계획으로 검증해야 한다.

Part 3. 성능 최적화 및 튜닝 전략

성능 최적화의 핵심은 반복 참조가 예정된 CTE를 물리적으로 저장된 객체로 치환해 원본 연산의 실행 횟수를 1회로 고정하는 것이다. MSSQL에서 이를 구현하는 방법은 임시 테이블(#temp table), 테이블 변수, 인덱싱된 뷰 세 가지로 나뉘며, 각각 적용 기준이 다르다.

구분	물리적 저장	인덱스 생성	통계 정보	다중 참조 시 재계산	재귀 지원
비재귀 CTE	없음	불가	없음	발생 가능	미지원
파생 테이블	없음	불가	없음	발생 가능	미지원
#임시 테이블	TempDB에 저장	가능	자동 생성	없음(1회 적재 후 재사용)	-
테이블 변수	TempDB에 저장	제한적(PK/UNIQUE만)	SQL Server 2019 이상 부분 지원	없음	-
인덱싱된 뷰	디스크에 영구 저장	가능(클러스터드 필수)	있음	없음	미지원
재귀 CTE	없음(워크 테이블 사용)	불가	없음	해당 없음(설계된 반복)	지원

가장 일반적인 대응은 #temp table로의 치환이다. 앞서 예시로 든 DailySales CTE를 다음과 같이 바꾼다.

SELECT
    OrderDate,
    SUM(TotalDue) AS DailyTotal
INTO #DailySales
FROM Sales.SalesOrderHeader
GROUP BY OrderDate;

CREATE CLUSTERED INDEX IX_DailySales_OrderDate
    ON #DailySales(OrderDate);

SELECT
    Today.OrderDate,
    Today.DailyTotal,
    Yesterday.DailyTotal AS PrevDayTotal
FROM #DailySales AS Today
LEFT JOIN #DailySales AS Yesterday
    ON Yesterday.OrderDate = DATEADD(DAY, -1, Today.OrderDate);

DROP TABLE #DailySales;

이 방식은 SELECT INTO 시점에 원본 테이블에 대한 스캔과 집계를 단 한 번만 수행하고, 그 결과를 TempDB에 물리적으로 적재한다. 이후 #DailySales에 대한 두 번의 참조는 이미 계산이 끝난 임시 테이블을 읽는 것이므로 원본 테이블에 대한 재스캔이 발생하지 않는다. 조인 키인 OrderDate에 클러스터드 인덱스를 생성해 이후 self-join이 Clustered Index Seek로 처리되도록 유도하는 것도 중요하다. 인덱스 없이 방치하면 #temp table을 읽는 두 번의 스캔이 새로운 병목으로 남는다.

이 튜닝을 적용할 때 반드시 지켜야 할 절차는 다음과 같다.

실행 계획에서 동일 서브트리가 여러 번 나타나는 CTE를 식별한다.
해당 CTE 정의를 SELECT ... INTO #temp 형태로 분리한다.
조인·필터 조건에 사용되는 컬럼 기준으로 #temp에 인덱스를 생성한다.
원본 쿼리의 CTE 참조를 #temp 참조로 교체한다.
SET STATISTICS IO/TIME과 실제 실행 계획으로 치환 전후의 논리적 읽기와 실행 시간을 비교해 개선 여부를 수치로 확인한다.

주의가 필요한 예외도 명확히 이해해야 한다. 첫째, 재귀 CTE는 이 문제와 무관하다. 재귀 CTE는 anchor member와 recursive member가 UNION ALL로 결합되어 반복 실행되는 구조이며, 이 반복은 재귀 자체의 설계된 동작이지 다중 참조로 인한 비효율이 아니다. 재귀 CTE의 성능 이슈는 MAXRECURSION 옵션, 워크 테이블(work table) 스풀 비용 등 별도의 관점에서 다뤄야 한다. 둘째, OPTION (RECOMPILE) 힌트는 이 반복 계산 문제를 해결하지 못한다. 이 힌트는 파라미터 스니핑으로 인한 실행 계획 부적합 문제를 대응하기 위한 것으로, CTE의 다중 참조로 인한 원본 연산 재실행과는 별개의 이슈다. 셋째, 매우 빈번하게 재사용되는 집계 로직이라면 #temp table 대신 인덱싱된 뷰로 전환하는 것도 검토 대상이지만, 스키마 바인딩(SCHEMABINDING) 제약과 원본 테이블 갱신 시의 유지보수 비용을 함께 고려해야 한다.

#temp table로 치환하면 CTE가 소모하던 CPU 비용이 TempDB에 대한 쓰기·읽기 비용으로 옮겨간다. 이 과정에서 TempDB의 메모리 그랜트(Memory Grant)와 디스크 I/O 부담이 함께 늘어날 수 있으므로, 치환 후에는 TempDB Spill 여부도 함께 점검해야 한다. 또한 CTE 재계산 문제는 옵티마이저의 통계 정보 최신성과도 맞물려 판단 오차를 키울 수 있어, 대상 테이블의 통계 갱신 상태를 함께 확인하는 것이 바람직하다.

성능 최적화의 핵심은 CTE를 '저장된 결과'로 오인하지 않는 데서 시작한다. 동일 CTE를 두 번 이상 참조하는 쿼리를 작성하거나 리뷰할 때는 반드시 Actual Execution Plan에서 Number of Executions, Actual Rebinds/Rewinds를 확인해 원본 연산이 실제로 몇 번 수행되는지 검증해야 한다. 재계산이 확인되면 #temp table 또는 인덱싱된 뷰로 치환해 원본 연산을 1회로 고정하고, 치환 후에는 조인 키 인덱스와 STATISTICS IO 수치를 반드시 함께 점검해 운영 환경에서의 CPU·디스크 I/O 병목을 사전에 제거해야 한다.