SQL Server Compatibility Level 100 vs 160: Query Store 관점에서 다시 보기

SQL Server Compatibility Level

SQL Server에서 데이터베이스의 호환성 수준(Compatibility Level)을 변경하는 것은 단순한 "버전 호환성" 이상의 의미를 갖습니다. 실제로는 쿼리 최적화 방식, 카디널리티 추정(Cardinality Estimation), 그리고 지능형 쿼리 처리(Intelligent Query Processing, IQP) 기능의 활성화 여부까지 결정하는 중요한 아키텍처적 선택입니다.

종종 특정 쿼리가 느려졌을 때 호환성 수준을 낮추어 임시로 성능을 복구하곤 합니다. 그러나 이는 데이터베이스 전체 관점에서 최신 엔진이 제공하는 핵심 최적화 기능을 포기하는 대가로 이어집니다. 본 글에서는 SQL Server 호환성 수준 100과 160의 차이점을 Query Store 관점에서 면밀히 분석하고, 최선의 성능 관리 전략을 공유합니다.

1. Compatibility Level 100에서도 Query Store는 작동할까?

가장 흔한 오해 중 하나는 **"구형 호환성 수준(예: 100)에서는 Query Store를 사용할 수 없다"**는 것입니다. 결론부터 말하자면, 그렇지 않습니다.

호환성 수준이 100(SQL Server 2008 수준)으로 설정되어 있더라도, SQL Server 2016 이상 버전에서 데이터베이스가 실행 중이라면 Query Store를 활성화하여 다음과 같은 핵심 기능을 정상적으로 활용할 수 있습니다.

성능 데이터 수집: 쿼리 텍스트, 실행 계획(Execution Plan), 런타임 통계(평균 실행 시간, CPU 사용량, 논리적 읽기 등) 수집.
실행 계획 비교: 호환성 수준 변경 전후 또는 특정 시점 간의 실행 계획 회귀(Regression) 감지.
실행 계획 강제 적용(Plan Forcing): 특정 query_id에 대해 과거의 안정적인 plan_id를 강제로 적용하여 쿼리 성능의 일관성 보장.

따라서 호환성 수준이 낮다고 해서 성능 모니터링 및 복구의 핵심 도구인 Query Store를 포기할 필요는 없습니다.

2. 100 vs 160: 지능형 쿼리 처리(IQP)의 한계선

하지만 수집(Query Store)을 넘어서 자동 최적화의 영역으로 들어가면 이야기가 달라집니다. SQL Server 2022(호환성 수준 160)에서 도입된 핵심 Intelligent Query Processing 기능들은 반드시 호환성 수준 160을 전제로 동작합니다.

① PSP(Parameter Sensitive Plan) Optimization

파라미터화된 쿼리가 항상 동일한 실행 계획을 재사용할 때 발생하는 고질적인 성능 문제입니다.

문제 상황: tenant_id = 1(데이터 수백 건)에 최적화된 Index Seek 계획이 캐시에 저장된 후, tenant_id = 999(데이터 수천만 건) 실행 시 재사용되어 심각한 성능 저하 발생(Parameter Sniffing).
IQP 해결책: PSP Optimization은 동일한 쿼리에 대해 입력된 파라미터 값의 범위나 통계 분포에 따라 여러 개의 실행 계획을 캐시에 관리하고 동적으로 매핑합니다. (호환성 수준 160 필수)

② Cardinality Estimation Feedback (CE Feedback)

옵티마이저는 통계 정보를 바탕으로 조건절에서 반환될 행의 수(Row Count)를 예측합니다. 이 예측(Cardinality Estimation)이 엇나가는 순간, 조인 순서(Join Order), 조인 방식(Hash vs Nested Loop), 메모리 할당(Memory Grant) 등이 모두 엉망이 됩니다.

IQP 해결책: 실제 실행 결과와 예측치의 편차를 Query Store에 기록하고, 다음 컴파일 시 모델을 보정(Feedback)하여 점진적으로 더 나은 계획을 생성합니다. (호환성 수준 160 및 Query Store READ_WRITE 상태 필수)

③ DOP Feedback & Memory Grant Feedback

DOP Feedback: 병렬 처리 쿼리의 오버헤드를 모니터링하여 최적의 병렬 처리 수준(Degree of Parallelism)을 스스로 찾아 조절합니다.
Memory Grant Feedback: 소트(Sort)나 해시(Hash) 작업 시 메모리가 부족해 TempDB Spill이 발생하거나 반대로 과도하게 낭비되는 현상을 다음 실행 시 자동으로 보정합니다.

3. Compatibility Level 100 고정이 위험한 이유

특정 오래된 쿼리는 구형 카디널리티 추정기(Legacy CE)와 구형 옵티마이저 규칙(호환성 수준 100) 하에서 더 빠를 수 있습니다. 그러나 이 한두 개의 쿼리 때문에 데이터베이스 전체를 호환성 수준 100에 묶어두는 것은 다음과 같은 큰 손실을 초래합니다.

최신 기능의 전면 차단: 위에서 설명한 PSP, CE Feedback, DOP Feedback, Batch Mode on Rowstore 등의 혜택을 전체 데이터베이스가 받지 못합니다.
비효율적인 자원 사용: 최신 하드웨어 및 CPU 아키텍처에 맞춘 최적화 혜택이 상실되어 메모리 및 CPU 비용이 증가합니다.

4. 실무적인 이관 및 운영 전략

그렇다면 특정 쿼리의 성능 회귀를 예방하면서 안전하게 160 수준으로 마이그레이션하려면 어떻게 해야 할까요? 전체를 낮추는 것이 아니라, 전체는 올리고 문제가 되는 개별 쿼리만 통제해야 합니다.

graph TD
    A[1. Level 100 상태에서 작동] --> B[2. Query Store 활성화 및 양호한 실행 계획 수집]
    B --> C[3. 데이터베이스 호환성 수준 160으로 변경]
    C --> D{4. 특정 쿼리 성능 저하 감지?}
    D -- Yes --> E[5. Query Store에서 과거 plan_id 강제 적용 Plan Forcing]
    D -- Yes --> F[6. Query Store Hint로 개별 쿼리에 FORCE_LEGACY_CARDINALITY_ESTIMATION 적용]
    D -- No --> G[7. PSP 및 CE Feedback 등의 최신 IQP 혜택 누림]

단계별 마이그레이션 프로토콜

사전 준비: 대상 DB에 Query Store를 활성화하여 운영 워크로드 하에서 안정적인 실행 계획을 수집합니다.
호환성 변경: 데이터베이스 호환성 수준을 160(또는 타겟 최신 버전)으로 올립니다.
성능 추적: Query Store의 '성능이 저하된 쿼리(Regressed Queries)' 패널을 모니터링합니다.
개별 대응:
- Plan Forcing: 특정 쿼리가 새로운 옵티마이저 하에서 느려진 경우, 수집해둔 예전의 정상 plan_id를 강제 적용합니다.
- Query Store Hints: 실행 계획 고정이 불가능한 다이내믹 쿼리 등의 경우, Query Store Hint 기능을 통해 해당 query_id에만 FORCE_LEGACY_CARDINALITY_ESTIMATION 같은 legacy 힌트를 직접 부여합니다.

5. 통계 정보 설계의 중요성

"느려질 때마다 UPDATE STATISTICS를 실행하면 빨라진다"는 현상은 성능 튜닝의 신호등입니다. 이는 데이터 옵티마이저의 카디널리티 예측이 실제 데이터 분포와 맞지 않아 비효율적인 계획을 세우고 있음을 의미합니다.

기본 설정: AUTO_CREATE_STATISTICS와 AUTO_UPDATE_STATISTICS는 항상 ON으로 유지합니다.
한계 극복: 특정 테넌트 ID(tenant_id), 특정 상태 값, 혹은 최근 날짜에 데이터가 편중되어 통계 업데이트 주기가 이를 따라가지 못할 때는 다음 설계를 보완해야 합니다.
- 필터링된 통계(Filtered Statistics): 특정 조건절이 자주 쓰이는 분포를 타겟팅하여 별도 통계 생성.
- FULLSCAN 예약 작업: 야간 등 비선점 시간에 핵심 테이블에 대한 UPDATE STATISTICS ... WITH FULLSCAN 실행 계획 수립.

결론

데이터베이스 성능 관리의 올바른 패러다임은 **"낮은 호환성 수준에 시스템을 안주시키는 것"**이 아니라, **"시스템 전체는 최신 호환성 수준(160)으로 유지하여 지능형 엔진의 혜택을 누리되, 예외적인 쿼리만 Query Store와 힌트, 그리고 정교한 통계 관리를 통해 핀포인트로 제어하는 것"**입니다. 이 현실적이면서도 강력한 전략을 통해 인프라 효율성을 극대화해 보시기 바랍니다.

Talk Lowy(kr)

이 블로그 검색