SQL Server 2008 데이터 웨어하우스 쿼리 성능

SQL Server 2008에서 데이터 웨어하우스를 운영하면서 수십억 건의 행에 대한 의사 결정 지원(Decision Support) 쿼리를 최적화하려면, 다음과 같은 핵심 기능과 전략을 종합적으로 활용해야 합니다. 각 기능별 예시와 보안·운영 측면의 실전 가이드를 포함합니다.

1. 스타 조인 쿼리 최적화

개요

스타 조인은 중앙의 팩트 테이블과 주변의 다수 차원 테이블 간의 조인으로 구성된 전형적인 데이터 웨어하우스 쿼리 구조입니다. SQL Server 2008은 이러한 스타 조인 최적화를 위해 다음을 지원합니다.

핵심 기술

• 비트맵 필터(Bitmap Filter): 차원 테이블에서 필터링 조건을 미리 추론하고, 팩트 테이블에서 불필요한 행을 제거.
• 선택도 기반 계획 선택: 팩트 테이블에서 읽을 행 비율에 따라 Nested Loop, Hash Join 등을 자동 선택.
• 스타 조인 추론: 외래 키 제약이 없어도 팩트/차원 구조를 자동으로 인식.

예시 쿼리

SELECT ProductAlternateKey, CalendarYear, SUM(SalesAmount)
FROM FactInternetSales Fact
JOIN DimTime ON Fact.OrderDateKey = TimeKey
JOIN DimProduct ON DimProduct.ProductKey = Fact.ProductKey
WHERE CalendarYear BETWEEN 2003 AND 2004
  AND ProductAlternateKey LIKE 'BK%'
GROUP BY ProductAlternateKey, CalendarYear

보안 및 점검 포인트

• 비즈니스 키 노출 최소화: 팩트 테이블에는 복합 비즈니스 키 대신 서로게이트 키 사용.
• 조인 조건 검토: 단일 컬럼 동등 조인만 허용되므로, 복합 조건 사용 시 계획 제외됨.
• 추론 오류 점검: 스타 조인 최적화가 비활성화된 경우, FK 누락 여부 점검.

2. 분할된 테이블 병렬 처리 (PTP)

개요

테이블 파티션을 사용하여 날짜 또는 키 기반의 대규모 데이터를 분산하고, 병렬 실행 계획으로 다중 코어를 활용해 쿼리 성능을 극대화합니다.

핵심 기술

• 라운드 로빈 방식 병렬화: 여러 파티션을 동시에 읽을 때, 파티션 수보다 많은 스레드를 효율적으로 배분.
• 쿼리 최적화 엔진 내 파티션 인식: 사용되는 파티션 범위를 기반으로 실행 계획 자동 생성.

실전 예

• 판매 데이터가 월별로 파티션된 테이블에서, 1~2개 분기에 해당하는 쿼리를 병렬 처리할 때 성능 최대 10배 향상.

CREATE PARTITION FUNCTION SalesPF (DATETIME)
AS RANGE LEFT FOR VALUES ('2023-06-30', '2023-09-30', '2023-12-31');

보안 및 운영 가이드

• 파티션 전환 시 잠금 레벨 확인: 데이터 잠금이 전체 테이블이 아닌 파티션 단위로 설정되어야 함.
• 쿼리 병렬 계획 분석: 실행 계획(XML 포함)에서 병렬 처리 여부 확인.
• 분할된 테이블의 유지보수 관리: Switch 작업 시 인덱싱된 뷰 제약 조건 확인.

3. 행 및 페이지 압축

개요

압축을 통해 저장 공간을 절감하면서 I/O 바운드 쿼리의 성능을 향상시킵니다. CPU 자원이 충분한 환경에서는 적극 추천됩니다.

압축 방식

유형	설명
행 압축(Row Compression)	고정 길이 → 가변 길이 포맷. NULL/0 제거.
페이지 압축(Page Compression)	반복되는 값은 한 번만 저장, LZ78 유사 알고리즘 적용.

적용 예시

ALTER TABLE FactInternetSales
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);

적용 전략 예

• 자주 액세스되지 않는 과거 데이터 → PAGE 압축
• 중간 빈도 데이터 → ROW 압축
• 실시간 액세스 파티션 → 압축 사용 안 함

점검 포인트

• CPU 사용률 모니터링: 압축 해제에 따른 오버헤드.
• 데이터 저장소 이득 평가: sp_estimate_data_compression_savings 사용.
• 압축 설정 자동화: ETL/배치 작업 이후 특정 파티션 압축 적용 스크립트 운영.

4. 파티션 정렬 인덱싱된 뷰

개요

인덱싱된 뷰(View with Clustered Index)는 사전 집계된 쿼리 결과를 저장하여 복잡한 집계 쿼리의 성능을 대폭 향상시킵니다. 파티션 정렬 뷰는 이러한 인덱싱된 뷰를 분할된 팩트 테이블과 정렬하여 유지 보수 작업도 최소화합니다.

특징

• 테이블 파티션 전환(SWITCH) 시 뷰 자동 일치
• ETL 처리 중에도 인덱싱된 뷰 삭제 없이 유지 가능

적용 예시

CREATE VIEW SalesSummary WITH SCHEMABINDING AS
SELECT DateKey, SUM(SalesAmount) AS Total
FROM dbo.FactInternetSales
GROUP BY DateKey;

CREATE UNIQUE CLUSTERED INDEX idx_SalesSummary
ON SalesSummary (DateKey);

보안 및 운영 가이드

• SCHEMABINDING 필수: 뷰 정의 시 테이블/스키마 변경 제한.
• 정렬 열 일치 확인: 파티션 키와 인덱스 정렬 키가 일치해야 유지관리 자동화 가능.

부가 기능 요약

기능	설명
MERGE 구문	UPSERT 지원, 차원 업데이트 간소화
GROUPING SETS	다중 그룹 집계 지원, 피벗 분석
백업 압축	전체/증분 백업에서 저장소 최적화
리소스 거버넌스	CPU/IO 쿼리별 제어로 멀티 작업 최적화

SQL Server 2008은 대용량 데이터 웨어하우스의 쿼리 성능, 저장소 효율성, 병렬 처리 최적화, 유지 보수 자동화 측면에서 매우 강력한 기능을 제공합니다.

운영 가이드 요약

항목	체크리스트
스타 조인	서로게이트 키 사용, FK 없는 스키마도 최적화 지원됨
병렬 처리	파티션별 DOP 활용, 실행 계획 분석 필수
데이터 압축	행/페이지 혼합 적용 전략, I/O 감소 및 저장소 절감
뷰 최적화	인덱싱된 뷰와 파티션 정렬로 요약 집계 자동화