고급 인덱싱과 통계 내부 동작

입문 강좌의 8강에서는 B-트리 인덱스의 기본 구조와 커버링 인덱스(INCLUDE)를 살펴보았습니다. 그러나 실무에서는 데이터 분포가 고르지 않거나 분석 쿼리와 OLTP 쿼리가 공존하는 환경에서 기본 인덱스 전략만으로는 한계에 부딪힙니다. 특정 조건에 해당하는 일부 행에만 인덱스를 만들고 싶거나, 수억 건의 판매 데이터를 컬럼 단위로 압축해서 집계 쿼리를 10배 이상 빠르게 만들고 싶을 때 필요한 것이 바로 이 레슨의 주제입니다.

이 레슨에서는 SQL Server 옵티마이저가 인덱스와 통계를 내부적으로 어떻게 소비하는지, 그리고 그 지식을 바탕으로 필터링된 인덱스, 컬럼스토어 인덱스, 통계 히스토그램을 실무에 올바르게 적용하는 방법을 심도 있게 다룹니다.

학습 목표

• 필터링된 인덱스(Filtered Index) 와 인덱싱된 뷰(Indexed View)가 효과적인 시나리오를 구분하고 설계할 수 있다.
• 컬럼스토어 인덱스(Columnstore Index) 의 압축 구조와 델타 스토어 동작 원리를 이해하고 분석 쿼리에 적용할 수 있다.
• 통계 히스토그램과 밀도 벡터가 카디널리티 추정에 사용되는 방식을 설명하고 직접 확인할 수 있다.
• 누락 인덱스 DMV(sys.dm_db_missing_index_*)를 올바르게 해석하고 중복·미사용 인덱스를 탐지해 통합 전략을 수립할 수 있다.
• 카디널리티 추정기(CE) 버전 차이가 실행 계획에 미치는 영향을 진단하고 적절히 제어할 수 있다.

---

필터링된 인덱스와 인덱싱된 뷰

필터링된 인덱스(Filtered Index)

필터링된 인덱스는 WHERE 조건을 인덱스 정의에 포함하여, 전체 테이블 중 일부 행에 대해서만 B-트리를 구성합니다. 인덱스 크기가 줄어들고 페이지 밀도가 높아지므로, 조건이 일치하는 쿼리의 I/O 비용이 극적으로 감소합니다.

-- 예시 테이블: 대용량 주문 테이블
-- 전체 1억 건 중 status = 'PENDING'인 행은 약 0.5% (미처리 주문만 자주 조회됨)
CREATE TABLE dbo.Orders (
    OrderID     BIGINT        NOT NULL IDENTITY PRIMARY KEY,
    CustomerID  INT           NOT NULL,
    Status      VARCHAR(20)   NOT NULL,  -- 'PENDING', 'COMPLETED', 'CANCELLED'
    OrderDate   DATETIME2     NOT NULL,
    TotalAmount DECIMAL(18,2) NOT NULL
);

-- ✅ 필터링된 인덱스: PENDING 상태 행에만 인덱스 생성
CREATE INDEX IX_Orders_Pending
    ON dbo.Orders (CustomerID, OrderDate)
    INCLUDE (TotalAmount)
    WHERE Status = 'PENDING';

-- 이 쿼리는 필터링된 인덱스만 스캔 (전체 인덱스 대비 훨씬 작은 범위)
SELECT CustomerID, OrderDate, TotalAmount
FROM dbo.Orders
WHERE Status = 'PENDING'
  AND CustomerID = 12345;

필터링된 인덱스가 옵티마이저에 의해 선택되려면 쿼리의 WHERE 절이 인덱스의 필터 조건을 포함(imply) 해야 합니다. 매개변수화된 쿼리에서 옵티마이저가 필터 조건을 증명하지 못하면 인덱스가 무시될 수 있습니다.

-- ❌ 파라미터화된 쿼리: 옵티마이저가 @Status = 'PENDING'을 컴파일 시점에 보장 못 함
DECLARE @Status VARCHAR(20) = 'PENDING';
SELECT * FROM dbo.Orders WHERE Status = @Status AND CustomerID = 12345;

-- ✅ 리터럴 조건이나 OPTION(RECOMPILE)으로 명시적 증명
SELECT * FROM dbo.Orders
WHERE Status = 'PENDING' AND CustomerID = 12345;

-- 또는 파라미터를 쓰되 매번 재컴파일해서 실제 값을 기반으로 계획 생성
SELECT * FROM dbo.Orders
WHERE Status = @Status AND CustomerID = 12345
OPTION (RECOMPILE);

⚠️ 주의 필터링된 인덱스의 WHERE 절에는 IS NULL, IS NOT NULL, 비교 연산자(=, <>, >, < 등)만 사용할 수 있습니다. OR나 함수 호출은 허용되지 않습니다.

인덱싱된 뷰(Indexed View)

인덱싱된 뷰는 뷰의 결과를 물리적으로 구체화(materialize)하여 디스크에 저장합니다. 기반 테이블이 변경될 때마다 뷰도 자동으로 유지 관리되므로, 집계가 무겁고 조인이 복잡하지만 자주 조회되는 패턴에 적합합니다.

-- 인덱싱된 뷰 생성 조건: SCHEMABINDING 필수, 집계 함수 제한 존재
CREATE VIEW dbo.vw_DailySales
WITH SCHEMABINDING
AS
    SELECT
        CAST(OrderDate AS DATE)   AS SaleDate,
        CustomerID,
        COUNT_BIG(*)              AS OrderCount,  -- COUNT(*) 대신 COUNT_BIG(*) 필수
        SUM(TotalAmount)          AS Revenue
    FROM dbo.Orders
    WHERE Status = 'COMPLETED'
    GROUP BY CAST(OrderDate AS DATE), CustomerID;
GO

-- 뷰에 클러스터드 인덱스를 추가하는 순간 물리적으로 구체화됨
CREATE UNIQUE CLUSTERED INDEX IX_vw_DailySales
    ON dbo.vw_DailySales (SaleDate, CustomerID);

-- Enterprise Edition: 옵티마이저가 자동으로 인덱싱된 뷰를 활용
-- 기반 테이블 직접 쿼리도 뷰를 거쳐 최적화 가능
SELECT CAST(OrderDate AS DATE) AS SaleDate, SUM(TotalAmount)
FROM dbo.Orders
WHERE Status = 'COMPLETED'
GROUP BY CAST(OrderDate AS DATE);
-- 실행 계획에서 dbo.vw_DailySales 를 스캔하는 것을 확인할 수 있음

-- Standard Edition: WITH (NOEXPAND) 힌트로 뷰를 명시적으로 직접 접근
SELECT SaleDate, SUM(Revenue)
FROM dbo.vw_DailySales WITH (NOEXPAND)
GROUP BY SaleDate;

💡 TIP 인덱싱된 뷰는 기반 테이블에 DML이 발생할 때마다 뷰 유지 비용이 추가됩니다. OLTP 테이블에 적용할 경우 쓰기 성능 저하를 반드시 측정하세요. 쓰기가 잦은 테이블보다는 야간 배치로 채워지는 집계 테이블에 더 어울립니다.

---

컬럼스토어 인덱스의 압축 구조와 분석 쿼리 가속

행 저장 vs. 열 저장

전통적인 B-트리 인덱스는 행 단위로 데이터를 저장합니다. 분석 쿼리가 수백만 행 중 2~3개의 컬럼만 집계한다면, 필요 없는 컬럼까지 포함된 행 전체를 읽어야 하므로 I/O가 낭비됩니다.

컬럼스토어 인덱스(Columnstore Index) 는 데이터를 컬럼 단위로 묶어 저장하고, 같은 컬럼의 값들은 유사한 값끼리 모여 있어 압축률이 매우 높습니다. SQL Server는 행 그룹(Row Group) 이라는 단위(기본 약 1,048,576행)로 데이터를 분할하고, 각 컬럼 세그먼트를 개별적으로 압축합니다.

-- 비클러스터드 컬럼스토어 인덱스: 기존 행 저장 테이블에 분석 가속을 추가
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_CS_Orders_Analytics
    ON dbo.Orders (OrderDate, CustomerID, Status, TotalAmount);

-- 클러스터드 컬럼스토어 인덱스: 테이블 자체를 열 저장으로 구성 (DW/HTAP)
-- 기존 클러스터드 인덱스를 교체하므로 OLTP와 분리된 테이블에서 사용
CREATE TABLE dbo.OrdersFact (
    OrderID     BIGINT        NOT NULL,
    CustomerID  INT           NOT NULL,
    Status      VARCHAR(20)   NOT NULL,
    OrderDate   DATETIME2     NOT NULL,
    TotalAmount DECIMAL(18,2) NOT NULL,
    INDEX IX_CCI CLUSTERED COLUMNSTORE
);

델타 스토어와 튜플 무버

컬럼스토어 인덱스에 행이 삽입될 때 즉시 압축 행 그룹에 들어가지 않습니다. 소량 삽입은 먼저 델타 스토어(Delta Store) 라는 B-트리 구조에 쌓이고, 행 수가 임계치(약 1,048,576)에 도달하거나 튜플 무버(Tuple Mover) 백그라운드 프로세스가 실행되면 압축 행 그룹으로 전환됩니다.

-- 행 그룹 상태 확인
SELECT
    object_name(i.object_id)    AS TableName,
    i.name                      AS IndexName,
    rg.row_group_id,
    rg.state_desc,              -- OPEN(델타), CLOSED, COMPRESSED
    rg.total_rows,
    rg.deleted_rows,
    rg.size_in_bytes
FROM sys.column_store_row_groups rg
JOIN sys.indexes i
    ON i.object_id = rg.object_id
   AND i.index_id  = rg.index_id
WHERE object_name(i.object_id) = 'OrdersFact'
ORDER BY rg.row_group_id;

-- 델타 스토어를 강제로 압축 행 그룹으로 전환 (유지 관리 창에서 실행)
ALTER INDEX IX_CCI ON dbo.OrdersFact REORGANIZE
    WITH (COMPRESS_ALL_ROW_GROUPS = ON);

-- 삭제된 행이 많은 경우 전체 재구성
ALTER INDEX IX_CCI ON dbo.OrdersFact REBUILD;

세그먼트 제거(Segment Elimination)와 배치 모드

컬럼스토어 인덱스는 각 행 그룹의 컬럼 세그먼트마다 최솟값과 최댓값 메타데이터를 보관합니다. 쿼리의 WHERE 조건이 특정 행 그룹의 범위를 벗어나면 해당 행 그룹 전체를 읽지 않고 건너뜁니다. 이것이 세그먼트 제거(Segment Elimination) 입니다.

-- 세그먼트 제거가 잘 일어나려면 데이터가 대략 정렬된 상태가 유리
-- OrderDate 기준 정렬 삽입 → OrderDate 범위 조건 시 대부분의 행 그룹 건너뜀
SELECT SUM(TotalAmount)
FROM dbo.OrdersFact
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01';

-- 세그먼트 제거 효과 확인: SET STATISTICS IO ON 출력의
-- 'segment reads / segment skipped' 메시지로 직접 측정
SET STATISTICS IO ON;
SELECT SUM(TotalAmount)
FROM dbo.OrdersFact
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01';
SET STATISTICS IO OFF;
-- 메시지 창에 "...,  segment reads N, segment skipped M, ..." 형태로 출력되며,
-- skipped 값이 클수록 세그먼트 제거가 효과적으로 일어난 것

-- 또는 컬럼스토어 물리 통계 DMV로 행 그룹/세그먼트 단위를 직접 확인
SELECT
    object_name(ps.object_id)   AS TableName,
    ps.row_group_id,
    ps.state_desc,
    ps.total_rows,
    ps.deleted_rows,
    ps.size_in_bytes
FROM sys.dm_db_column_store_row_group_physical_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.OrdersFact')
ORDER BY ps.row_group_id;

배치 모드(Batch Mode)는 컬럼스토어 인덱스와 함께 자동으로 활성화되며, CPU가 한 번에 약 900행 묶음을 벡터 연산으로 처리합니다. SQL Server 2019부터는 Batch Mode on Rowstore가 추가되어 B-트리 테이블에서도 일부 집계 쿼리에 배치 모드가 적용됩니다.

💡 TIP HTAP(Hybrid Transactional/Analytical Processing) 시나리오에서는 비클러스터드 컬럼스토어 인덱스를 OLTP 테이블에 추가하는 방식이 실용적입니다. 다만 DML 작업 시 델타 스토어 유지 비용이 발생하므로, 쓰기 빈도가 매우 높은 테이블은 파티셔닝이나 별도 DW 분리를 고려해야 합니다.

---

통계 히스토그램과 밀도 벡터의 내부 동작

통계 객체 구조

SQL Server 옵티마이저는 실행 계획을 만들 때 각 연산자가 처리할 행 수(카디널리티)를 추정합니다. 이 추정의 근거가 되는 것이 통계(Statistics) 객체입니다. 통계 객체는 두 가지 핵심 정보를 담고 있습니다.

• 히스토그램(Histogram): 첫 번째(선두) 컬럼의 값 분포를 최대 200개 스텝으로 표현
• 밀도 벡터(Density Vector): 컬럼 조합별 평균 고유값 밀도 (1 / 고유값 수)

-- 통계 메타데이터 확인
SELECT
    s.name       AS StatName,
    s.auto_created,
    s.user_created,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter,
    sp.last_updated
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE object_name(s.object_id) = 'Orders'
ORDER BY sp.last_updated DESC;

-- 히스토그램과 밀도 벡터 직접 열람
DBCC SHOW_STATISTICS('dbo.Orders', 'IX_Orders_CustomerDate');

DBCC SHOW_STATISTICS 출력은 세 파트로 나뉩니다.

파트	내용
Header	행 수, 샘플링 비율, 마지막 갱신 시각
Density Vector	컬럼 조합별 밀도 (낮을수록 선택도 높음)
Histogram	RANGE_HI_KEY, RANGE_ROWS, EQ_ROWS, DISTINCT_RANGE_ROWS, AVG_RANGE_ROWS

히스토그램이 카디널리티 추정에 쓰이는 방식

히스토그램의 각 스텝은 RANGE_HI_KEY(상한값), EQ_ROWS(해당 값과 정확히 같은 행 수), RANGE_ROWS(이전 스텝과 현재 상한 사이 행 수)를 기록합니다. 옵티마이저는 쿼리 조건에 해당하는 스텝을 찾아 행 수를 계산합니다.

-- 히스토그램 스텝 수동 확인 (결과에서 패턴 읽기)
-- RANGE_HI_KEY: 각 스텝의 최대값
-- EQ_ROWS: WHERE col = RANGE_HI_KEY 로 조회 시 예상 행 수
-- RANGE_ROWS: 이전 스텝 초과 ~ RANGE_HI_KEY 미만 구간의 총 행 수
-- AVG_RANGE_ROWS: 해당 구간 내 고유값당 평균 행 수 (범위 조건 추정에 사용)
DBCC SHOW_STATISTICS('dbo.Orders', 'IX_Orders_Pending') WITH HISTOGRAM;

-- 실제 행 수 vs. 추정 행 수 비교 (카디널리티 추정 오류 진단)
SELECT
    qs.sql_handle,
    qs.execution_count,
    qs.total_rows,
    qs.last_rows,                       -- 마지막 실행 시 실제 반환 행 수
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.last_rows > 0;

밀도 벡터와 등치 조건 추정

밀도 벡터는 히스토그램이 다루지 않는 다중 컬럼 조합의 카디널리티 추정을 담당합니다.

-- (CustomerID, Status) 복합 조건의 추정 행 수 계산
-- 밀도(CustomerID) = 1 / 고유 CustomerID 수
-- 밀도(CustomerID, Status) = 1 / 고유 (CustomerID, Status) 쌍 수
-- 추정 행 수 = 밀도 × 전체 행 수

-- 실제 추정값 확인: 실행 계획 XML에서 EstimatedRows 속성 참조
-- SET STATISTICS XML ON 또는 SSMS에서 "실제 실행 계획 포함" 활성화 후 확인
SET STATISTICS XML ON;
SELECT CustomerID, Status, COUNT(*)
FROM dbo.Orders
WHERE CustomerID = 12345 AND Status = 'PENDING'
GROUP BY CustomerID, Status;
SET STATISTICS XML OFF;

⚠️ 주의 히스토그램은 최대 200 스텝밖에 없습니다. 고유값이 200개를 초과하는 컬럼은 스텝 사이 구간에 대해 AVG_RANGE_ROWS로 선형 보간합니다. 실제 데이터가 한쪽에 편중된 경우 추정 오류가 커질 수 있습니다. 이런 경우 통계를 전체 스캔(FULLSCAN)으로 갱신하거나, 통계 갱신 빈도를 높이는 것이 도움이 됩니다.

-- 전체 스캔으로 통계 갱신 (기본 샘플링 대비 더 정확, 비용은 높음)
UPDATE STATISTICS dbo.Orders IX_Orders_Pending WITH FULLSCAN;

-- 모든 통계 일괄 갱신
EXEC sp_updatestats;

-- 자동 통계 갱신 옵션 확인 및 설정
ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS_ASYNC ON;

---

누락 인덱스 DMV를 신중히 해석하기

SQL Server는 쿼리 실행 중 "이 인덱스가 있었다면 비용이 줄었을 것"이라는 정보를 내부적으로 수집합니다. 이 데이터를 sys.dm_db_missing_index_* DMV로 조회할 수 있습니다.

-- 누락 인덱스 후보 조회 (개선 효과 추정값 기준 내림차순)
SELECT TOP 20
    mid.database_id,
    OBJECT_NAME(mid.object_id, mid.database_id) AS TableName,
    mid.equality_columns,   -- = 조건에 쓰일 컬럼들
    mid.inequality_columns, -- >, <, BETWEEN 등 범위 조건 컬럼들
    mid.included_columns,   -- INCLUDE 절에 넣을 컬럼들
    migs.unique_compiles,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,   -- 인덱스 없을 때 평균 쿼리 비용
    migs.avg_user_impact,       -- 인덱스 생성 시 예상 비용 절감 %
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
        AS ImprovementMeasure   -- 종합 개선 지표
FROM sys.dm_db_missing_index_details   mid
JOIN sys.dm_db_missing_index_groups    mig  ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY ImprovementMeasure DESC;

이 DMV 결과를 그대로 믿으면 위험합니다. 몇 가지 함정이 있습니다.

함정 1. 서버 재시작 시 데이터 초기화

DMV는 인스턴스 메모리에 저장되므로 SQL Server가 재시작되면 모두 사라집니다. 최소 1주일 이상 안정적으로 운영된 환경의 데이터를 기준으로 삼아야 합니다.

함정 2. 같은 컬럼에 대한 중복 추천

동일 테이블의 같은 컬럼 조합이 다른 equality_columns/inequality_columns 분할로 여러 행에 나타날 수 있습니다. 개별 생성 전에 통합 가능한지 검토해야 합니다.

-- ❌ DMV 추천을 그대로 개별 생성하면 중복 인덱스 폭증
-- 추천 1: equality=(CustomerID), included=(TotalAmount)
-- 추천 2: equality=(CustomerID), inequality=(OrderDate), included=(TotalAmount)

-- ✅ 두 추천을 하나로 통합
CREATE INDEX IX_Orders_Customer_Date
    ON dbo.Orders (CustomerID, OrderDate)
    INCLUDE (TotalAmount, Status);

함정 3. avg_user_impact 과대 추정

avg_user_impact는 단일 쿼리에 대한 기대 개선치이며, 인덱스 생성 후 DML 비용 증가나 버퍼 풀 압박 등의 부작용은 반영하지 않습니다. 인덱스를 추가하는 것과 삭제하는 것의 전체 비용을 함께 고려해야 합니다.

💡 TIP 누락 인덱스 추천은 "이 쿼리가 어디서 힘들어하는지" 파악하는 시발점으로 활용하세요. 최종 인덱스 설계는 반드시 해당 쿼리의 실행 계획, 기존 인덱스 목록, 전체 DML 부하를 종합적으로 고려한 뒤 결정해야 합니다.

---

중복·미사용 인덱스 탐지와 인덱스 통합 전략

인덱스가 많을수록 SELECT는 빨라질 가능성이 있지만, INSERT/UPDATE/DELETE 시 모든 인덱스를 동기적으로 유지해야 하므로 쓰기 비용과 잠금 경합이 증가합니다.

미사용 인덱스 탐지

-- 인덱스 사용 통계 (인스턴스 시작 이후 누적)
SELECT
    OBJECT_NAME(i.object_id)    AS TableName,
    i.name                      AS IndexName,
    i.type_desc,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,           -- DML 시 갱신 횟수 (높으면 유지 비용 큼)
    ius.last_user_seek,
    ius.last_user_scan
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
    ON i.object_id = ius.object_id
   AND i.index_id  = ius.index_id
   AND ius.database_id = DB_ID()
WHERE OBJECT_NAME(i.object_id) = 'Orders'
  AND i.type_desc <> 'HEAP'
ORDER BY (ius.user_seeks + ius.user_scans + ius.user_lookups) ASC;

user_seeks + user_scans + user_lookups = 0이고 user_updates > 0인 인덱스는 읽히지 않으면서 쓰기 비용만 발생하는 전형적인 미사용 인덱스 후보입니다.

중복 인덱스 탐지

SQL Server에는 MySQL의 sys.schema_redundant_indexes처럼 중복을 자동 감지하는 뷰가 없으므로, 직접 쿼리로 확인해야 합니다.

-- 선두 컬럼이 동일한 인덱스 조합 탐지
SELECT
    OBJECT_NAME(i1.object_id)   AS TableName,
    i1.name                     AS Index1,
    i2.name                     AS Index2,
    i1.type_desc
FROM sys.indexes i1
JOIN sys.indexes i2
    ON i1.object_id  = i2.object_id
   AND i1.index_id   < i2.index_id
   AND i1.type       = i2.type
JOIN sys.index_columns ic1
    ON ic1.object_id = i1.object_id AND ic1.index_id = i1.index_id AND ic1.key_ordinal = 1
JOIN sys.index_columns ic2
    ON ic2.object_id = i2.object_id AND ic2.index_id = i2.index_id AND ic2.key_ordinal = 1
   AND ic1.column_id = ic2.column_id
WHERE OBJECT_NAME(i1.object_id) NOT IN ('sysdiagrams')
  AND i1.is_primary_key = 0 AND i2.is_primary_key = 0
ORDER BY TableName, i1.name;

인덱스 통합 전략

인덱스를 통합할 때는 다음 순서로 접근합니다.

1. sys.dm_db_index_usage_stats로 각 인덱스의 실제 사용 빈도를 최소 1주일 관찰합니다.
2. 선두 컬럼이 같은 인덱스는 더 긴 쪽을 기준으로 통합 가능성을 검토합니다.
3. 통합 인덱스를 생성한 뒤 기존 인덱스를 즉시 삭제하지 않고, 먼저 통합 인덱스가 기존 쿼리를 커버하는지 실행 계획으로 확인합니다.
4. 기존 인덱스를 DISABLE하고 1~2일 운영하면서 쿼리 성능 변화를 모니터링합니다.
5. 문제가 없으면 DROP으로 최종 제거합니다.

-- 1단계: 통합 인덱스 생성
CREATE INDEX IX_Orders_Customer_Status_Date
    ON dbo.Orders (CustomerID, Status, OrderDate)
    INCLUDE (TotalAmount);

-- 2단계: 기존 인덱스 비활성화 (비활성화 상태에서도 구조는 유지됨)
ALTER INDEX IX_Orders_OldCustomer ON dbo.Orders DISABLE;
ALTER INDEX IX_Orders_OldStatus   ON dbo.Orders DISABLE;

-- 3단계: 영향 없으면 삭제
DROP INDEX IX_Orders_OldCustomer ON dbo.Orders;
DROP INDEX IX_Orders_OldStatus   ON dbo.Orders;

-- 롤백: 비활성화된 인덱스 재활성화
ALTER INDEX IX_Orders_OldCustomer ON dbo.Orders REBUILD;

⚠️ 주의 DISABLE된 인덱스는 DML 시 자동 유지되지 않습니다. 재활성화하려면 반드시 REBUILD가 필요합니다. 비활성화 기간이 길어질수록 REBUILD 시간이 늘어납니다.

---

카디널리티 추정기(CE) 버전 차이와 실행 계획 영향

SQL Server 2014에서 새로운 카디널리티 추정기(CE 70 → CE 120) 가 도입되었고, 이후 SQL Server 2016(CE 130), 2017(CE 140), 2022(CE 160)까지 계속 개선되었습니다. CE 버전은 데이터베이스의 호환성 수준(Compatibility Level) 에 연동됩니다.

CE 버전별 주요 차이

CE 버전	도입 버전	호환성 수준	주요 변경
CE 70 (레거시)	SQL Server 7.0	110 이하에서 기본 사용 (120 이상에서도 FORCE_LEGACY 힌트/DB 옵션·TF 9481로 강제 가능)	통계 독립성 가정, 고정 추정 공식
CE 120	SQL Server 2014	120	다중 술어 선택도 개선, 범위 추정 향상
CE 130	SQL Server 2016	130	조인 포함(containment) 가정 개선
CE 140	SQL Server 2017	140	행 목표(Row Goal) 처리 개선
CE 160	SQL Server 2022	160	파라미터 민감 계획(PSP) 최적화 도입

CE 버전 확인 및 변경

-- 현재 데이터베이스 호환성 수준 확인
SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

-- 호환성 수준 변경 (CE 버전 변경)
-- SQL Server 2022 기능을 모두 사용하려면 160
ALTER DATABASE AdventureWorks SET COMPATIBILITY_LEVEL = 160;

-- 특정 쿼리에만 레거시 CE 강제 적용 (회귀 테스트용)
SELECT CustomerID, SUM(TotalAmount)
FROM dbo.Orders
WHERE OrderDate >= '2024-01-01'
GROUP BY CustomerID
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

-- 특정 쿼리에 신규 CE 강제 적용
SELECT CustomerID, SUM(TotalAmount)
FROM dbo.Orders
WHERE OrderDate >= '2024-01-01'
GROUP BY CustomerID
OPTION (USE HINT('FORCE_DEFAULT_CARDINALITY_ESTIMATION'));

CE 버전 전환 시 계획 회귀 대응

호환성 수준을 높이면 일부 쿼리에서 실행 계획이 바뀌어 성능이 오히려 저하되는 계획 회귀(Plan Regression) 가 발생할 수 있습니다. SQL Server 2016 이후의 Query Store를 활용해 회귀를 감지하고 이전 계획을 강제할 수 있습니다.

-- Query Store 활성화
ALTER DATABASE AdventureWorks SET QUERY_STORE = ON;
ALTER DATABASE AdventureWorks SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900
);

-- 계획 회귀 감지: 평균 CPU 시간이 급증한 쿼리 찾기
SELECT TOP 10
    qsq.query_id,
    qsq.query_hash,
    qsrs.avg_cpu_time,
    qsrs.avg_logical_io_reads,
    qsrs.count_executions,
    qsqt.query_sql_text
FROM sys.query_store_query           qsq
JOIN sys.query_store_query_text      qsqt ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan            qsp  ON qsp.query_id        = qsq.query_id
JOIN sys.query_store_runtime_stats   qsrs ON qsrs.plan_id        = qsp.plan_id
ORDER BY qsrs.avg_cpu_time DESC;

-- 특정 쿼리에 이전 계획 강제 적용 (plan_id는 Query Store에서 확인)
EXEC sys.sp_query_store_force_plan @query_id = 42, @plan_id = 7;

-- 강제 해제
EXEC sys.sp_query_store_unforce_plan @query_id = 42, @plan_id = 7;

SQL Server 2022의 파라미터 민감 계획(Parameter Sensitive Plan, PSP) 최적화는 CE 160에서 활성화되며, 동일한 쿼리에 대해 파라미터 값에 따라 여러 실행 계획을 캐싱합니다. 파라미터 스니핑(Parameter Sniffing) 문제를 자동으로 완화합니다.

-- PSP 최적화 확인 (SQL Server 2022, CE 160 이상)
-- has_compile_replay_script는 PSP 전용 플래그가 아니므로 PSP 식별 기준으로 쓰지 않습니다.
-- PSP는 하나의 dispatcher 쿼리와 여러 variant 쿼리/계획으로 표현되며,
-- 실행 계획 XML 내 Dispatcher 노드로 확실히 식별합니다.
SELECT
    qsq.query_id,
    qsp.plan_id,
    qsp.is_forced_plan,
    qsp.query_plan
FROM sys.query_store_plan qsp
JOIN sys.query_store_query qsq ON qsq.query_id = qsp.query_id
-- 계획 XML의 <StmtSimple> 아래에 <Dispatcher> 노드(하위에 <ParameterSensitivePredicate> 포함)가 있으면 PSP dispatcher 계획
CROSS APPLY (
    SELECT CAST(qsp.query_plan AS XML) AS plan_xml
) px
WHERE px.plan_xml.exist(
    'declare namespace s="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
     //s:Dispatcher') = 1;

💡 TIP 호환성 수준을 높이기 전에 반드시 Query Store를 먼저 활성화하세요. 전환 후 1~2주간 계획 회귀를 모니터링하고, 회귀가 발견되면 개별 쿼리에 OPTION (USE HINT(...)) 또는 Query Store 강제 계획으로 대응하는 것이 안전한 마이그레이션 패턴입니다.

---

요약

• 필터링된 인덱스는 조건에 해당하는 일부 행만 B-트리에 포함해 인덱스 크기를 줄이고 I/O를 절감합니다. 매개변수화 쿼리에서 옵티마이저가 필터 조건을 증명하지 못하면 인덱스가 무시되므로 주의가 필요합니다.
• 컬럼스토어 인덱스는 열 단위 압축과 세그먼트 제거, 배치 모드 실행으로 분석 쿼리를 크게 가속합니다. 델타 스토어를 주기적으로 압축해 행 그룹 품질을 유지하세요.
• 통계 히스토그램은 최대 200 스텝으로 선두 컬럼의 값 분포를 표현하고, 밀도 벡터는 다중 컬럼 조합의 카디널리티 추정에 쓰입니다. 데이터 분포가 편중된 경우 FULLSCAN으로 갱신해 추정 정확도를 높이세요.
• 누락 인덱스 DMV는 유용한 시발점이지만 인스턴스 재시작 시 초기화되고 DML 비용을 고려하지 않으므로, 추천을 그대로 생성하지 말고 기존 인덱스와 통합 가능성을 검토하세요.
• 중복·미사용 인덱스는 쓰기 비용과 잠금 경합을 증가시킵니다. sys.dm_db_index_usage_stats로 사용 빈도를 파악하고, DISABLE 후 관찰 기간을 거쳐 안전하게 제거하세요.
• CE 버전은 호환성 수준에 연동되며 버전마다 카디널리티 추정 로직이 다릅니다. 호환성 수준 업그레이드 전 Query Store를 활성화해 계획 회귀를 대비하고, 개별 쿼리에 USE HINT로 CE 버전을 제어할 수 있습니다.

---

연습문제

1. 아래 테이블에 IsDeleted = 0인 행이 전체의 2%밖에 없고, 다음 쿼리가 초당 수백 번 실행됩니다. 최적의 인덱스 전략을 설계하고 이유를 설명하세요.

   SELECT UserID, CreatedAt, Content
   FROM dbo.Comments
   WHERE IsDeleted = 0 AND PostID = @PostID
   ORDER BY CreatedAt DESC;
   

   힌트 필터링된 인덱스의 WHERE 절, 키 컬럼 순서, INCLUDE 컬럼을 모두 고려하세요.

2. dbo.SalesFact 테이블에 클러스터드 컬럼스토어 인덱스가 있습니다. 다음 쿼리 실행 후 sys.column_store_row_groups를 조회했더니 state_desc = 'OPEN'인 행 그룹이 다수 존재합니다. 이 상태가 성능에 미치는 영향과 해결 방법을 설명하세요.

   -- 매 분마다 소량 삽입
   INSERT INTO dbo.SalesFact SELECT ... FROM dbo.SalesStaging WHERE ...;
   

   힌트 델타 스토어의 특성과 REORGANIZE 명령을 활용하세요.

3. DBCC SHOW_STATISTICS('dbo.Orders', 'IX_Orders_CustomerDate') 결과에서 Density Vector의 [CustomerID] 밀도가 0.0001이고, [CustomerID, OrderDate] 밀도가 0.000005입니다. WHERE CustomerID = 99999 AND OrderDate = '2025-03-15' 조건으로 예상되는 행 수를 계산하고, 실제 행 수와 차이가 클 때 어떻게 대응해야 하는지 서술하세요.

   힌트 밀도 × 전체 행 수 공식과 FULLSCAN 갱신을 활용하세요.

4. 현재 데이터베이스의 호환성 수준이 130(SQL Server 2016)입니다. 160으로 올렸을 때 특정 쿼리의 평균 CPU 시간이 3배 증가하는 계획 회귀가 발생했습니다. Query Store를 이용해 해당 쿼리에 이전 계획을 강제 적용하는 절차를 SQL로 작성하세요.

   힌트 sys.query_store_query, sys.query_store_plan, sp_query_store_force_plan을 활용하세요.

---