[MSSQL] Actual Execution Plan의 Estimated Rows 오차 원인과 카디널리티 교정 절차

728x90

Actual Execution Plan: 실무적 정의와 운영상 중요성

MSSQL의 Actual Execution Plan은 옵티마이저가 컴파일 시점에 선택한 계획과 실행 중 수집된 행 수 정보를 함께 보여 주는 진단 자료이다. Estimated Number of Rows는 통계와 카디널리티 추정 모델을 이용한 예상값이며, Actual Number of Rows는 해당 실행에서 연산자가 반환한 행 수이다.

두 값의 차이는 단순한 표시 오차가 아니다. 옵티마이저는 예상 행 수를 기준으로 인덱스 접근 방식, 조인 순서, Nested Loops·Hash Match·Merge Join 선택, 병렬 처리 여부와 메모리 그랜트(Memory Grant)를 결정한다. 따라서 하위 연산자의 작은 추정 오류가 상위 조인과 정렬에서 확대되면 CPU 증가, 디스크 I/O 병목, tempdb Spill과 동시성 저하로 이어진다.

핵심 답은 명확하다. 실제보다 적게 추정하면 반복 탐색에 유리한 Nested Loops와 부족한 메모리 그랜트가 선택되기 쉽다. 실제보다 많이 추정하면 불필요한 Hash Match, 과도한 메모리 예약, 병렬 계획 또는 넓은 스캔이 선택될 수 있다. DBA는 비용 비율이 높은 아이콘만 볼 것이 아니라 데이터가 처음 크게 어긋난 연산자를 찾아야 한다.

Part 1. Estimated Rows와 Actual Rows 기본 확인 방법 (Basic Syntax)

Actual Execution Plan은 SSMS에서 Include Actual Execution Plan을 활성화한 뒤 쿼리를 실제로 실행해 수집한다. 운영 환경에서는 쿼리가 끝까지 수행되므로 대용량 트랜잭션과 부하 집중 시간대를 피해야 한다. 실행하지 않고 계획만 확인하는 Estimated Plan에는 Actual Rows와 실행 시점 경고가 존재하지 않는다.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT o.CustomerID,
       COUNT_BIG(*) AS OrderCount
FROM dbo.Orders AS o
WHERE o.OrderDate >= @StartDate
  AND o.OrderDate <  @EndDate
GROUP BY o.CustomerID
OPTION (RECOMPILE);

위 쿼리에서는 Index Seek 또는 Scan의 Estimated Number of Rows와 Actual Number of Rows를 먼저 비교한다. 연산자가 여러 번 실행되면 Actual Number of Rows와 Number of Executions를 함께 확인해야 한다. 한 번의 반환 행 수만 보고 전체 처리량을 판단하면 Nested Loops 내부 입력의 반복 비용을 놓치게 된다.

확인 항목	의미	운영상 판단
Estimated Number of Rows	컴파일 시 예상한 연산자 출력 행 수	조인 선택과 비용 계산의 기준이다.
Actual Number of Rows	실행 중 관측된 출력 행 수	예상값과의 방향 및 배수를 비교한다.
Number of Executions	연산자 실행 횟수	반복 탐색의 누적 행 수와 I/O를 판단한다.
Actual Rebinds·Rewinds	내부 입력 재평가 또는 재사용 횟수	Nested Loops와 Spool의 반복 부담을 확인한다.
Memory Grant Info	요청·허가·사용 메모리 정보	과소 할당, 과다 예약과 동시성 영향을 구분한다.
Warnings	Sort·Hash Spill 등의 실행 경고	tempdb 쓰기와 추가 패스 발생 여부를 확인한다.

비교표에는 각 연산자의 Node ID, Physical Operator, Estimated Rows, Actual Rows, 실행 횟수와 오차 방향을 기록해야 한다. 실제 환경의 수치는 실행 계획 XML에서 추출해야 하며 임의의 예시 값으로 대체하면 안 된다.

측정 절차: 대상 쿼리의 실제 실행 계획 XML을 내려받아 보관하고, 각 핵심 연산자의 속성 창에서 아래 항목을 차례로 확인한다.

확인 순서	실행 계획에서 읽을 항목	판단 방법
1	Node ID와 Physical Operator	데이터 흐름을 따라 비교할 연산자를 식별한다.
2	Estimated Number of Rows와 Actual Number of Rows	Actual이 Estimated보다 크면 과소 추정, 작으면 과다 추정으로 분류한다.
3	Number of Executions	반복 실행된 연산자는 반환 행 수와 실행 횟수를 함께 해석한다.
4	하위 입력부터 처음 오차가 나타난 Node ID	상위 연산자로 전파된 결과가 아니라 최초 원인 후보를 표시한다.

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

카디널리티 추정은 열 통계의 히스토그램, 밀도 정보, NULL 비율과 조건식의 선택도를 이용한다. 통계가 오래되었거나 표본이 데이터 분포를 충분히 표현하지 못하면 특정 값의 빈도와 범위 행 수를 잘못 계산한다. 대량 적재 직후 또는 증가 키의 최신 구간을 조회할 때 기존 히스토그램 범위를 벗어난 값이 많으면 오차가 커질 수 있다.

여러 조건 사이의 상관관계도 주요 원인이다. 예를 들어 지역과 지점 코드처럼 서로 종속적인 열을 독립 조건으로 계산하면 결합 선택도가 실제 분포와 달라진다. 복합 통계가 없거나 표현식 때문에 통계를 활용하지 못하면 이 오차가 조인 입력까지 전달된다.

가장 흔한 안티 패턴은 검색 컬럼을 함수나 암시적 형 변환으로 가공하는 것이다. 아래 조건은 컬럼 원형의 분포와 인덱스 탐색 가능성을 훼손하며, 전체 테이블 스캔과 부정확한 추정을 함께 유발할 수 있다.

-- 안티 패턴: 컬럼 가공
WHERE CONVERT(date, o.OrderDate) = @TargetDate;

-- 개선: 반개방 범위 검색
WHERE o.OrderDate >= @TargetDate
  AND o.OrderDate < DATEADD(day, 1, @TargetDate);

개선 쿼리는 날짜 컬럼을 그대로 유지하므로 SARGable 조건이 된다. 같은 원리는 문자열과 숫자 비교에서 발생하는 CONVERT_IMPLICIT에도 적용된다. 데이터 형식은 컬럼과 매개변수 사이에서 일치시켜야 한다.

매개변수 스니핑(Parameter Sniffing)은 데이터 편향이 큰 환경에서 별도로 확인해야 한다. 최초 컴파일에 사용된 값에 적합한 계획이 캐시에 저장된 뒤 선택도가 다른 값에도 재사용되면, 동일 쿼리라도 Actual Rows 오차와 처리 시간이 크게 달라질 수 있다. 한 번의 실행 계획만으로 통계 문제라고 결론 내리면 안 된다.

원인	계획에서 확인할 단서	교정 방향
오래되거나 부정확한 통계	기본 Scan·Seek 단계부터 행 수가 어긋남	수정 행 비율과 통계 속성을 확인한 후 대상 통계를 갱신한다.
데이터 편향	매개변수 값에 따라 실제 행 수가 급격히 변함	히스토그램 단계와 대표 매개변수별 계획을 비교한다.
열 간 상관관계	개별 조건은 맞지만 결합 조건에서 오차가 발생함	필요한 열 조합의 복합 통계를 검토한다.
비-SARGable 조건	함수·연산·암시적 변환과 Scan이 나타남	컬럼 가공을 제거하고 범위 조건으로 재작성한다.
매개변수 스니핑	컴파일 값과 런타임 값의 선택도가 다름	대표 값 검증 후 재컴파일, 쿼리 분리 또는 PSP 적용 가능성을 판단한다.
테이블 변수·중간 결과	중간 연산자의 추정이 실제 분포를 반영하지 못함	SQL Server 버전과 호환성 수준을 확인하고 임시 테이블 및 통계를 검토한다.

과소 추정이 Nested Loops와 메모리 부족을 만드는 과정

옵티마이저가 외부 입력을 소량으로 예상하면 Nested Loops와 내부 인덱스 탐색을 저렴하게 계산한다. 실제 외부 행 수가 훨씬 많으면 내부 Seek가 반복되고 Random I/O와 CPU 사용량이 누적된다. 적은 입력에는 합리적인 계획이 대량 입력에서는 병목으로 바뀌는 구조이다.

Sort와 Hash Match도 예상 행 수 및 행 크기를 기반으로 필요한 메모리를 산정한다. 과소 추정으로 메모리 그랜트가 부족하면 실행 중 작업 데이터가 메모리에 들어가지 못해 tempdb로 Spill될 수 있다. 이 과정에서 추가 읽기와 쓰기, 재분할 작업이 발생하며 동시 쿼리의 tempdb 경합으로 이어질 수 있다.

과다 추정이 Hash Match와 과도한 메모리 예약을 만드는 과정

실제보다 많은 행을 예상하면 옵티마이저는 Hash Match, 넓은 Scan 또는 병렬 계획을 선택할 가능성이 커진다. 실행 자체가 빠르게 끝나더라도 필요 이상으로 큰 메모리 그랜트를 요청하면 다른 쿼리가 RESOURCE_SEMAPHORE 대기 상태에 놓일 수 있다. 단일 쿼리의 사용 메모리만이 아니라 서버 전체 동시성을 함께 판단해야 한다.

조인 알고리즘 자체를 문제로 단정해서는 안 된다. Nested Loops는 작은 외부 입력과 효율적인 내부 인덱스에서 적합하며, Hash Match는 큰 비정렬 입력에서 합리적이다. 문제는 잘못된 예상 행 수를 근거로 적합하지 않은 알고리즘이 선택된다는 점이다. 관련 조인 계획의 기본 구조는 MSSQL INNER JOIN 실행 계획 분석과 튜닝에서 함께 확인할 수 있다.

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

교정은 실행 계획의 오른쪽 아래 입력 연산자부터 데이터 흐름을 따라 진행한다. Actual Rows가 최초로 크게 벗어난 지점이 원인 후보이며, 그 위쪽 연산자의 오차는 결과일 가능성이 높다. 최상위 SELECT의 오차만 보고 인덱스를 추가하는 방식은 근본 원인을 가린다.

재현 조건을 고정한다. 동일한 데이터 시점, 매개변수, SET 옵션, 데이터베이스 호환성 수준과 실행 주체를 기록한다. 캐시된 계획과 재컴파일 계획을 구분한다.
실제 계획과 런타임 지표를 수집한다. 실행 계획 XML, STATISTICS IO·TIME 결과, 대기 유형과 tempdb Spill 경고를 확보한다. 운영 환경에서는 수집 자체의 부하를 통제해야 한다.
최초 오차 연산자를 찾는다. Scan·Seek·Filter 단계부터 Estimated Rows와 Actual Rows를 비교한다. 조인 위에서만 오차가 보이면 각 입력의 추정과 결합 조건을 다시 확인한다.
통계 상태를 확인한다. sys.dm_db_stats_properties로 마지막 갱신 시점, 수정 행 수와 샘플링 정보를 확인하고 DBCC SHOW_STATISTICS로 히스토그램과 밀도를 검토한다.
쿼리 형태를 검사한다. 비-SARGable 함수, 암시적 변환, 로컬 변수, 선택적 조건, 상관관계가 높은 복합 조건과 중간 결과를 확인한다.
가장 좁은 범위로 교정한다. 문제가 확인된 통계를 우선 갱신하고, 필요할 때 복합 통계 또는 적절한 인덱스를 검토한다. 전체 데이터베이스 통계 갱신은 I/O와 컴파일 부하를 유발하므로 기본 해법으로 사용하면 안 된다.
새 실제 계획으로 검증한다. Estimated Rows 오차, 조인 방식, Logical Reads, Spill, 요청·허가·사용 메모리와 전체 소요 시간을 전후 비교한다. 계획 모양만 바뀌었다는 이유로 개선이라고 판단하면 안 된다.

SELECT
    OBJECT_SCHEMA_NAME(s.object_id) AS SchemaName,
    OBJECT_NAME(s.object_id) AS ObjectName,
    s.name AS StatisticsName,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'dbo.Orders');

조회 결과는 갱신 필요성을 판단하는 근거이지 자동 갱신 명령이 아니다. 데이터 분포, 수정 패턴, 업무 시간대와 대상 쿼리의 중요도를 함께 검토해야 한다. 통계 이름과 대상 열을 확인하지 않은 채 광범위한 UPDATE STATISTICS를 실행하면 대량 I/O와 계획 재컴파일을 유발한다. 히스토그램을 검사할 때는 위 조회 결과의 StatisticsName을 DBCC SHOW_STATISTICS의 두 번째 인수로 사용한다.

-- 대상 통계가 원인으로 확인된 후 StatisticsName을 지정해 실행
-- FULLSCAN 또는 SAMPLE 비율은 별도 검증 후 선택한다.
UPDATE STATISTICS dbo.Orders;

FULLSCAN은 항상 정답이 아니다. 큰 테이블에서는 읽기 비용과 수행 시간이 증가하므로 유지보수 창과 운영 부하를 고려해야 한다. 표본 비율은 실제 분포 재현성과 갱신 비용을 측정해 결정하며 임의의 고정값을 모든 테이블에 적용하면 안 된다.

통계 갱신 후 반드시 비교할 항목

검증 항목	수집 방법	판정 기준
최초 문제 연산자의 Estimated·Actual Rows	동일 조건의 갱신 전후 실제 실행 계획에서 두 값을 추출한다.	오차 방향과 크기가 줄었는지 확인한다.
조인 방식과 조인 순서	갱신 전후 계획의 Physical Operator와 입력 순서를 대조한다.	실제 입력 규모에 적합한지 판단한다.
Memory Grant	각 계획의 요청·허가·사용 메모리를 같은 단위로 기록한다.	부족 또는 과다 예약이 완화되었는지 확인한다.
Hash·Sort Spill	실행 계획의 경고와 tempdb 사용 여부를 전후로 확인한다.	실행 계획 경고와 tempdb 사용을 확인한다.
Logical Reads·CPU·Elapsed Time	동일한 매개변수와 SET 옵션으로 STATISTICS IO·TIME 결과를 수집한다.	동일 조건에서 자원 사용량을 비교한다.

위 표는 반드시 실제 실행 계획과 측정 결과로 채워야 한다. 통계 갱신 후에도 최초 Scan 또는 Seek의 추정이 틀리면 데이터 편향, 히스토그램 표현 한계, 조건식 변환 또는 상관관계를 다시 검사한다. 특정 매개변수에서만 문제가 재현되면 매개변수 스니핑과 계획 재사용 여부를 우선 검증한다.

조인 결과가 급격히 확대되는 쿼리는 카디널리티 오차와 실제 데이터 중복을 구분해야 한다. 의도하지 않은 다대다 조인 또는 조건 누락은 통계 교정으로 해결되지 않는다. 행 수 증폭과 실행 계획 분석 방법은 MSSQL CROSS JOIN 성능저하 방지와 실행 계획 분석을 참고할 수 있다. 양쪽 입력 보존으로 계획이 복잡해지는 경우에는 MSSQL FULL OUTER JOIN 성능 저하의 원인과 실행 계획 분석과 비교할 필요가 있다.

성능 최적화의 핵심은 Estimated Rows 오차를 계획의 결과가 아니라 원인 발생 지점에서 교정하는 것이다. DBA는 컬럼 가공과 암시적 변환을 제거하고, 통계의 최신성과 데이터 편향을 확인하며, 실제 입력 규모에 적합한 인덱스와 범위 검색을 적용해야 한다. 마지막 판단은 통계 갱신 전후의 Actual Execution Plan, 메모리 그랜트, Spill과 논리적 읽기를 동일 조건에서 비교해 수행한다.