SQL Server 2008
데이터 웨어하우스 쿼리 성능
Sunil Agarwal and Torsten Grabs and Joachim Hammer 박사
한 눈에 보기:
- 스타 조인 쿼리 최적화
- 분할된 테이블 병렬 처리
- 행 및 페이지 압축
- 파티션 정렬 인덱싱된 뷰
SQL Server 2008은 이전 버전의 SQL Server보다 더 강력한 관계형 데이터 웨어하우징 기능을 제공하지만 어떻게 이러한 새 기술을 사용하여 수십억 개의 행을 기반으로 하는 의사 결정 지원을 위한 잘 작동하는 데이터 웨어하우스를 만들 수 있는지 궁금할 것입니다. 또는 어떤 기능을 사용해야 의사 결정 지원 쿼리와 보고서의 쿼리 성능을 최상으로 만들 수 있는지, 이 새로운 버전의 SQL Server®에서 현실적으로 어떤 종류의 성능 향상을 기대할 수 있는지 알고 싶을 것입니다.
출시가 가까워질수록 궁금한 점은 더 많아질 것입니다. SQL Server 2008에서 가장 중요한 성능 관련 데이터 웨어하우스 기능에 대해 자세히 다루는 이 문서가 여러분에게 도움이 되기를 바랍니다.
로컬 데이터베이스 설계: 다차원 모델링
트랜잭션 기반의 기간 업무(LOB) 응용 프로그램은 대개 정규화된 데이터베이스 스키마를 가지고 있습니다. 하지만 관계형 데이터 웨어하우스의 논리적 데이터베이스 스키마에서는 정규화를 그다지 중요하게 다루지 않습니다. 현재 많은 관계형 데이터 웨어하우스 설계는 Ralph Kimball과 Margy Ross가 공동 저술한 The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling을 통해 널리 알려진 차원 모델링 접근 방식을 따르고 있습니다.
데이터 웨어하우스를 오랫동안 다루어 보았다면 별모양 스키마와 눈송이 스키마 같은 관계형 데이터 웨어하우스의 일반적인 스키마 패턴에 대해 잘 알고 있을 것입니다. 차원 모델링에서는 차원 테이블과 팩트 테이블을 구분합니다. 차원 테이블은 제품, 고객, 매장, 국가 등의 마스터 데이터가 저장되는 테이블이고 팩트 테이블은 판매, 주문, 구매, 반품 등의 트랜잭션 데이터가 저장되는 테이블입니다.
차원 테이블과 팩트 테이블은 기본 키(PK)/외래 키(FK) 관계로 연결됩니다. 저장소 요구 사항을 최소화하기 위해 많은 데이터 웨어하우스는 FK 제약 조건을 강제하지 않으며 이를 통해 기반 인덱스의 저장소 오버헤드를 줄이고 팩트 테이블 유지 관리 비용을 낮춥니다. 대개 데이터 웨어하우스의 차원 테이블은 상당히 작아, 적게는 수천 개에서 많게는 수백만 개의 행을 유지합니다. 이와 달리 팩트 테이블은 수억 개에서 수십억 개에 이르는 행을 유지하는 매우 큰 테이블입니다. 따라서 논리적 설계에서 주의를 기울여야 하는 것은 팩트 테이블의 저장소 요구 사항입니다.
이 크기 요인은 팩트 테이블/차원 테이블 관계를 유지하기 위해 차원 테이블에서 선택해야 하는 키를 결정할 때도 영향을 미칩니다. 예를 들어 차원이 나타내는 엔터티의 실세계 식별자인 차원 비즈니스 키 기반의 복합 키는 대개 여러 열과 관련을 맺습니다. 팩트 테이블에 이러한 복합 키에 해당하는 외래 키가 있는 경우 여러 열 복합 키가 각 팩트 테이블 행에서 반복되므로 문제가 될 수 있습니다.
이 문제를 해결하기 위해 일반적으로 사용되는 방법은 팩트 테이블과 차원 간의 관계를 구현하는 작은 서로게이트 키를 사용하는 것입니다. 서로게이트 키는 차원 테이블의 인공적인 기본 키 역할을 수행하는 정수 형식의 ID 열입니다. 크기가 훨씬 작은 서로게이트 키를 참조하는 팩트 테이블을 사용하면 대규모 팩트 테이블에서 저장소 요구 사항을 상당히 줄일 수 있습니다. 그림 1에서는 차원 테이블 및 팩트 테이블과 함께 서로게이트 키를 사용하는 차원 모델링 데이터 웨어하우스 스키마를 보여 줍니다.
그림 1** 팩트 테이블 하나와 차원 테이블 두 개가 있는 별모양 스키마 예제 **(더 크게 보려면 이미지를 클릭하십시오.)
눈송이 스키마 설계에서는 하나 이상의 차원이 여러 수준으로 전파됩니다. 예를 들어 고객 차원이 고객, 국가 및 지역 수준으로 전파됩니다. 따라서 이 스키마 설계에서는 훨씬 큰 규모의 차원을 정규화해야 하며, 데이터가 과도하게 중복될 경우 문제가 발생할 수 있습니다. 수준은 개별 테이블로 표현되므로 이 스키마는 눈송이 형태를 갖게 됩니다. 이와 달리 별모양 스키마 설계에서는 스키마 차원이 테이블을 넘어 전파되지 않습니다. 별모양 스키마는 중앙의 팩트 테이블을 중심으로 차원 테이블이 그룹화된 별 형태를 갖게 됩니다.
차원 모델링된 별모양 스키마나 눈송이 스키마를 사용하면 의사 결정 지원 쿼리가 전형적인 패턴을 따르게 됩니다. 즉, 쿼리를 통해 팩트 테이블에서 여러 관심 측정값을 선택하고 서로게이트 키를 통해 팩트 행을 하나 이상의 차원과 조인하고 차원 테이블의 비즈니스 열에 필터 조건자를 배치하고 하나 이상의 비즈니스 열을 그룹화한 다음 일정 시간 동안 팩트 테이블에서 검색된 측정값을 집계합니다. 다음은 이 패턴을 보여 주는 예제입니다. 이러한 쿼리를 스타 조인 쿼리라고도 합니다.
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
실제 설계
관계형 데이터 웨어하우스의 많은 SQL 쿼리는 스타 조인 쿼리 구조를 따릅니다. 하지만 의사 결정권자는 기반 비즈니스 데이터를 새로운 방식으로 파악하기 위해 지속적으로 노력하므로 의사 결정 지원 쿼리는 대개 시간이 지나면서 변하게 됩니다. 이것이 데이터 웨어하우스 업무에서 임시 쿼리의 비율이 높은 이유이며 의사 결정 지원 쿼리와 차원 모델링된 데이터 웨어하우스 스키마의 실제 설계를 어렵게 만드는 원인입니다.
SQL Server를 사용하는 데이터 웨어하우스 설계자는 대개 SQL Server에서 제공하는 설계 초안이나 실제 설계를 기반으로 시작한 다음 시간이 지나 업무가 변화함에 따라 설계를 세밀하게 조정합니다. SQL Server의 설계 초안은 자유롭게 데이터 웨어하우스에 적용하고 변경할 수 있습니다. 이 작업을 수행할 경우 업데이트를 위한 인덱스 유지 관리로 인한 성능 영향과 인덱스의 저장소 요구 사항 같은 실제 데이터베이스 설계의 모범 사례를 고려해야 합니다.
팩트 테이블
설계 초안에서는 일반적인 스타 쿼리를 만들고 팩트 테이블에 대한 인덱스를 만든다고 가정합니다. 팩트 테이블의 클러스터형 인덱스에서는 여러 개의 차원 서로게이트 키 열(외래 키 열)을 인덱스 키로 사용합니다. 자주 사용되는 열은 인덱스 키 목록에 있어야 합니다. 이러한 인덱스 키 목록이 업무에서 자주 실행하는 쿼리에 대해 뛰어난 액세스 경로를 지원하는지 확인하기 위해 따로 시간을 할애할 수도 있습니다.
또한 설계 초안에서는 팩트 테이블의 차원 서로게이트(외래 키) 열 각각에 대해 비클러스터형 단일 열 인덱스를 만듭니다. 이러한 인덱스는 해당 차원 중 특정한 차원에 대해 대단히 효율적인 액세스 경로를 지원합니다.
클러스터형 인덱스의 목표는 업무 중에 실행되는 대부분의 쿼리에 뛰어난 성능을 지원하는 것입니다. 이와 달리, 비클러스터형 인덱스 집합의 목표는 특정 고객이나 제품에 대한 팩트 테이블 측정값을 가져오는 쿼리의 성능을 높이는 것입니다. 예를 들어 비클러스터형 인덱스를 사용하면 단일 고객에 대한 판매 데이터를 검색하기 위해 팩트 테이블을 검색할 필요가 없습니다.
차원 테이블
차원 테이블에 설계 초안을 적용할 때 각 차원 테이블에 대한 인덱스를 만들어야 합니다. 여기에는 차원의 서로게이트 키 열에 대한 비클러스터형 기본 키 제약 조건 인덱스와 차원 엔터티의 비즈니스 키 열에 대한 클러스터형 인덱스가 포함됩니다. 대규모 차원 테이블의 경우에는 선택도가 높은 조건자에서 자주 사용되는 열에 대한 비클러스터형 인덱스를 추가하는 것도 고려해야 합니다.
데이터 웨어하우스의 유지 관리 시간대에 실행되는 프로세스는 실행 시간이 중요한 경우가 많습니다. 클러스터형 인덱스를 사용하면 이 유지 관리 시간대에 효율적인 ETL(추출, 변환 및 로드)이 가능합니다. 예를 들어 느리게 변하는 차원이 있다면 기존 행은 적절히 업데이트되며 차원에 없는 행은 차원 테이블에 추가됩니다. 이 액세스 패턴이 성공적이려면 ETL 시점에 차원 테이블을 조회하고 업데이트하는 성능이 좋아야 합니다.
설계 초안은 SQL Server로 구축되는 관계형 데이터 웨어하우스를 실제로 설계하기 위한 좋은 시작점입니니다. 이 대표적인 관계형 데이터 웨어하우스 설정을 기반으로 SQL Server 2008의 새로운 주요 기능을 탐색해 볼 수 있습니다.
스타 조인 쿼리 최적화
차원 모델링된 관계형 데이터 웨어하우스에서 스타 조인 쿼리를 실행할 때 가장 많은 비용이 드는 부분은 대개 팩트 테이블 처리입니다. 선택도가 높은 쿼리의 경우에도 차원 테이블과 비교할 때 팩트 테이블에서 10배 이상의 행을 검색하므로 이 사실을 쉽게 확인할 수 있습니다. 따라서 팩트 테이블로 연결되는 최선의 액세스 경로를 사용하는 것이 뛰어난 쿼리 성능을 얻는 데 필수적입니다.
SQL Server의 쿼리 최적화 프로그램은 자동으로 여러 대체 경로 중에 예상 비용이 가장 낮은 액세스 경로를 선택합니다. 데이터 웨어하우스의 핵심 목표는 쿼리 최적화 프로그램이 스타 조인 쿼리의 실행 계획에서 가능성 있는 모든 대체 액세스 경로를 고려하도록 하는 것입니다. SQL Server의 쿼리 최적화 프로그램에는 뛰어난 성능의 스타 조인 쿼리 실행 계획을 자동으로 제공할 수 있도록 하는 다양한 기능이 포함되어 있습니다.
그림 2에서 볼 수 있는 것처럼, 스타 조인 쿼리는 크게 세 가지 분류로 나뉜다고 생각할 수 있습니다. 이 대략적인 분류는 SQL Server 엔진에서 각 쿼리에 맞는 적절한 계획을 선택할 때도 도움이 됩니다. SQL Server에서 사용하는 기본 개념은 팩트 테이블에 대한 쿼리의 선택도입니다. 쿼리는 팩트 테이블에서 사용하는 행 수가 적을수록 선택도가 높다고 간주됩니다. 팩트 테이블에서 검색되는 행의 비율이 이러한 쿼리 분류의 직관적인 근거로 사용됩니다. 이러한 비율은 일반적인 고객 배포를 통해 얻은 값을 나타내며 액세스 경로 정의를 생성할 때 사용되는 엄격한 경계는 아닙니다.
그림 2** 스타 조인 쿼리의 선택도 범위 **(더 크게 보려면 이미지를 클릭하십시오.)
첫 번째 분류에는 팩트 테이블에서 최대 10%의 행을 처리하는 선택도가 높은 쿼리가 포함됩니다. 중간 선택도가 포함되는 두 번째 분류는 팩트 테이블 행의 10%부터 최대 75%까지 처리하는 쿼리로 구성됩니다. 낮은 선택도의 세 번째 분류에 속하는 쿼리는 팩트 테이블에 저장된 행의 75% 이상을 처리합니다. 그림의 상자에는 각 선택도 분류에 속하는 기본 쿼리 실행 계획이 강조 표시되어 있습니다.
선택도 기반의 계획 선택
선택도가 높은 스타 쿼리는 대개 팩트 테이블 행의 10% 미만을 검색하므로 팩트 테이블에 대한 임의 액세스가 가능합니다. 따라서 이 분류에 대한 쿼리 계획은 중첩 루프 조인과 함께 팩트 테이블에 대한 (비클러스터형) 인덱스 검색 및 책갈피 조회에 크게 의존하게 됩니다. 이 분류의 쿼리는 팩트 테이블에 대해 임의 I/O를 수행하므로 팩트 테이블의 상당한 부분을 검색해야 하는 순차적 I/O에 비해 성능이 훨씬 뛰어납니다. 따라서 팩트 테이블의 행 수가 특정 기준을 넘게 되면 여러 쿼리 계획에서 이 분류의 쿼리를 고려하게 됩니다.
선택도가 중간인 스타 쿼리는 팩트 테이블에서 상당한 비율의 행을 처리하므로, 대개 팩트 테이블 검색이나 팩트 테이블 범위 검색을 사용하는 해시 조인이 팩트 테이블의 선호되는 액세스 경로입니다. SQL Server는 비트맵 필터를 사용하여 이러한 해시 조인의 성능을 향상시킵니다.
그림 3에서는 SQL Server가 스타 조인 쿼리 실행 중에 비트맵 필터를 사용하여 조인 성능을 향상시키는 방법을 보여 줍니다. 이 그림에서는 서로게이트 키로 팩트 테이블이 조인되는 Product와 Time이라는 두 차원 테이블에 대한 쿼리 계획을 보여 줍니다. 쿼리에서는 두 차원 모두에 대해 WHERE 절과 같은 필터 조건자를 사용하여 한 행이 한 차원만 한정하게 만듭니다. 이것은 두 조인 연산자 옆에 있는 작은 빨간색 테이블로 표시됩니다.
그림 3** 조인 감소 처리를 사용하는 스타 조인 쿼리 계획 **(더 크게 보려면 이미지를 클릭하십시오.)
각 조인의 조인 구현은 해시 조인이므로 SQL Server가 두 차원 테이블 모두에 대해 조인 감소 정보라고 하는 한정 열에 대한 정보를 차원 테이블에서 사용할 수 있습니다. 그림의 녹색 상자는 조인 감소 정보 데이터 구조를 나타냅니다. 기반 차원 테이블에서 정보가 입력되면 SQL Server가 쿼리 실행 중에 자동으로 이러한 데이터 구조를 팩트 테이블을 처리하는 연산자(예: 테이블 검색)에 전달합니다. 그러면 연산자가 차원 테이블 행에 대한 정보를 사용하여 차원에 대한 조인 조건을 한정하지 않는 팩트 테이블 행을 제거합니다.
SQL Server는 쿼리를 처리하는 동안 아주 초기 단계인 팩트 테이블에서 행을 검색한 직후에 이러한 팩트 테이블 행을 정리합니다. 이렇게 하면 쿼리 계획의 이후 연산자에서 정리된 행을 처리할 필요가 없으므로 CPU 사용량이 줄어들고 디스크 I/O가 줄어들 가능성도 있습니다. SQL Server는 쿼리 실행 시간에 비트맵 표현을 사용하여 조인 감소 정보 데이터 구조를 효율적으로 구현합니다.
스타 조인 최적화 파이프라인
최적화 프로세스에서는 조인 쿼리 최적화에 대한 표준적인 추론을 사용하여 대안 쿼리 실행 계획의 초기 집합을 생성합니다. 그런 다음 특수한 용도의 확장 기능을 호출하여 추가적인 대안 쿼리 계획을 생성합니다.
데이터 웨어하우스의 경우에는 확장 기능이 별모양 스키마, 눈송이 스키마 및 스타 쿼리 패턴을 검사하여 팩트 테이블에 대한 쿼리의 선택도를 추정합니다. 스키마 및 쿼리 형태가 패턴과 일치하면 SQL Server가 자동으로 계획 공간에 후속 쿼리 계획을 추가하며, 계속해서 비용 기반 최적화에 의해 이러한 쿼리 계획 중에서 실행 시 가장 성능이 좋을 것으로 예상되는 쿼리 계획이 선택됩니다.
또한 쿼리 실행 시 SQL Server는 런타임의 실제 조인 감소 선택도를 모니터링합니다. 선택도가 변하는 경우 SQL Server는 조인 감소 정보 데이터 구조를 동적으로 재배치하여 가장 선택도가 높은 쿼리를 먼저 적용합니다.
스타 조인 추론 기능
데이터 웨어하우스의 많은 실제 설계는 별모양 스키마를 따르지만 앞서 외래 키 제약 조건에서 설명한 것처럼 팩트 테이블과 차원 테이블 간의 관계를 완전하게 지정하지는 않습니다. 외래 키 제약 조건을 명시적으로 지정하지 않는 경우 SQL Server는 추론에 의존하여 별모양 스키마 쿼리 패턴을 검색해야 합니다. 스타 조인 쿼리 패턴을 검색하는 데는 다음과 같은 추론이 적용됩니다.
- n항 조인에 참여하는 테이블 중 가장 큰 테이블이 팩트 테이블로 간주되며 팩트 테이블의 최소 크기에 대한 추가 제한이 있습니다. 예를 들어 가장 큰 테이블이 지정된 크기보다 작으면 n항 조인이 스타 조인으로 간주되지 않습니다.
- 스타 조인 쿼리에서 이항 조인의 모든 조인 조건은 단일 열 동등 조건자여야 하며, 조인은 내부 조인이어야 합니다. 다소 제한적으로 느껴질 수도 있지만 이러한 조건은 일반적인 별모양 스키마에서 서로게이트 키에 대한 팩트 테이블과 차원 테이블 간의 거의 모든 조인에 적용됩니다. 조인에 위에서 설명한 패턴에 맞지 않는 보다 복합적인 조인 조건이 있는 경우에는 해당 조인이 스타 조인에서 제외됩니다. 예를 들어 5개 경로가 있는 조인에서 두 조인 경로가 패턴에 맞지 않는 복합 조인 조건자를 갖는다면 해당 조인은 3개 경로가 있는 스타 조인이 됩니다(추가 조인 두 개는 나중에 처리됨).
이것이 추론 규칙이라는 것을 기억하십시오. 추론에서 차원 테이블을 팩트 테이블로 선택하게 되는 실제 상황은 거의 없습니다. 추론이 계획 선택에 영향을 미치지만 선택된 계획의 정확성에는 영향을 미치지 않습니다. 계속해서 스타 조인에 포함된 이항 조인은 선택도가 낮아지는 순서로 정렬됩니다. 이 컨텍스트의 조인 선택도는 팩트 테이블의 입력 카디널리티와 조인의 결과 카디널리티 비율로 정의됩니다. 조인 선택도는 특정 차원이 팩트 테이블의 카디널리티를 줄이는 정도를 나타냅니다. 일반적인 규칙으로, 선택도가 높은 조인을 먼저 고려합니다.
결과 쿼리 계획의 예상 쿼리 비용이 적절한 경우 SQL Server의 쿼리 프로세서가 스타 조인 패턴과 앞서 설명한 조건에 따라 쿼리에 자동으로 최적화를 적용합니다. 따라서 응용 프로그램을 변경하지 않아도 상당한 성능 향상 효과를 얻을 수 있습니다. 조인 감소와 같은 일부 스타 조인 최적화는 SQL Server Enterprise Edition에서만 사용할 수 있다는 점에 유의하십시오.
스타 조인 성능 결과
SQL Server 2008의 스타 조인 최적화를 개발하는 노력의 일환으로, Microsoft에서는 벤치마크와 실제 고객 작업에 기반한 성능 연구를 다양하게 수행했습니다. 이러한 작업 중 세 가지 결과는 살펴볼 가치가 있습니다.
Microsoft 영업부 데이터 웨어하우스 이 작업에서는 Microsoft 영업부의 의사 결정 지원을 위해 내부적으로 사용되는 데이터 웨어하우스의 성능을 추적합니다. 이를 위해 인덱스를 포함해 약 750GB 크기의 데이터베이스 샘플 스냅숏을 만들었습니다. 이 작업의 쿼리는 많은 수가 10개 이상의 조인을 가지고 있기 때문에 쿼리 처리가 어렵습니다.
정품 고객 이 일련의 실험은 일반 매장과 온라인 매장을 모두 가진 소매 업종의 데이터 웨어하우징 고객을 대상으로 합니다. 이 고객의 특징은 차원 모델링된 눈송이형 스키마와 정식 스타 조인 쿼리를 사용한다는 것입니다. 실험을 위해 약 100GB의 원시 데이터를 웨어하우스 스냅숏에 입력했습니다.
의사 결정 지원 작업 이 일련의 실험에서는 100GB의 차원 모델링된 데이터베이스에서 의사 결정 지원 작업의 성능을 조사합니다. 그림 4에서는 이 세 작업의 결과를 보여 줍니다. 이 그림에서는 작업의 모든 쿼리에 대한 쿼리 응답 시간의 정규화된 기하 평균을 보여 줍니다. 이 메트릭은 작업에서 임의 쿼리를 실행할 때 어떤 쿼리 성능을 기대할 수 있는지 나타내는 뛰어난 지표입니다. 그림의 막대 그래프는 스타 조인 최적화를 사용하지 않는 기준 성능(1.0)을 스타 조인으로 최적화된 성능과 비교합니다. 모든 쿼리는 SQL Server 2008을 사용하여 수행되었습니다.
그림 4** 스타 조인 최적화를 통한 성능 향상 **(더 크게 보려면 이미지를 클릭하십시오.)
그림에서 볼 수 있는 것처럼 모든 작업이 12%에서 최대 30%까지 크게 향상됩니다. 개별적인 성능 향상 수준은 다를 수 있지만 SQL Server 2008의 새 기능인 스타 조인 관련 최적화 확장 기능을 통해 SQL Server 엔진을 사용하는 의사 결정 지원 작업이 약 15–20% 향상될 것으로 예상하고 있습니다.
분할된 테이블 병렬 처리
대규모 데이터 웨어하우스의 쿼리 처리 속도를 높이기 위해 데이터베이스 관리자는 대규모 팩트 테이블을 날짜별로 분할하는 경우가 많습니다. 이렇게 하면 데이터가 서로 다른 파일 그룹에 배치되어 특정 데이터 범위에 속한 행을 처리할 때 검색해야 하는 데이터 양이 줄어들 뿐만 아니라 많은 수의 실제 디스크에 파일 그룹을 배포할 때 기반 디스크 시스템의 동시성이 향상됩니다.
SQL Server 2005에서는 규모가 큰 관계를 작은 논리적 조각으로 분할하여 대규모 테이블의 유지 관리 성능을 개선하는 방법이 사용되었습니다. 또한 대규모 의사 결정 지원 응용 프로그램 등의 쿼리 처리 성능이 크게 향상되었습니다.
하지만 안타깝게도 SQL Server 2005를 사용하는 일부 고객은 이 분할된 테이블의 쿼리와 관련된 성능 문제를 겪고 있습니다. 특히 병렬 공유 메모리 다중 프로세서 컴퓨터에서 SQL Server 2005를 실행할 경우 문제가 심각합니다. SQL Server 2005에서 분할된 테이블에 대해 병렬 쿼리를 처리할 때 사용 가능한 스레드의 일부만 쿼리 실행에 할당되는 경우가 있습니다.
코어가 64개인 컴퓨터를 가정해 보겠습니다. 이 경우 쿼리는 최대 64개의 스레드를 병렬로 사용할 수 있으며 파티션 두 개를 사용합니다. SQL Server 2005는 64개 스레드 중에서 2개만 받기 때문에 컴퓨터 CPU 처리 능력의 3.1%(2/64)만 사용하게 됩니다. 일부 쿼리의 경우 분할된 팩트 테이블의 쿼리 성능이 동일한 컴퓨터에서 동일한 팩트 테이블의 분할되지 않은 버전에서 동일한 쿼리를 수행할 때의 성능보다 10배 이상 떨어지는 것이 보고되었습니다.
이 시점에서 SQL Server 2005는 단일 파티션을 사용하는 쿼리에 최적화되었다는 것을 언급해야겠습니다. 단일 파티션의 경우 쿼리 프로세서는 모든 사용 가능한 스레드를 할당하여 검색을 수행합니다. 이 특수한 최적화는 다중 코어 컴퓨터에서 단일 파티션 쿼리의 성능을 상당히 향상시킵니다. 따라서 고객이 다중 파티션을 사용하는 쿼리에 대해서도 같은 동작을 기대하는 것은 당연한 일입니다.
SQL Server 2008의 새로운 분할된 테이블 병렬 처리(PTP) 기능은 쿼리가 사용하는 파티션 수나 개별 파티션의 상대적인 크기에 관계없이 기존 하드웨어의 처리 성능을 최대한 활용하여 분할된 테이블에서 실행되는 쿼리의 성능을 향상시킵니다. 분할된 팩트 테이블이 있는 일반적인 데이터 웨어하우스의 경우 병렬 계획에서 실행하는 쿼리의 성능이 크게 향상되는 것을 확인할 수 있습니다. 특히 쿼리에 영향을 받는 파티션의 수보다 사용할 수 있는 프로세서 코어 수가 많다면 성능 향상이 두드러집니다. 이 새로운 기능은 어떠한 조정이나 구성 작업 없이 설치 즉시 사용할 수 있습니다.
판매 날짜별로 구분된 판매 데이터를 나타내는 팩트 테이블이 네 개의 파티션에 분할되어 있다고 가정해 보겠습니다. 그림 5의 다이어그램은 이 예제를 알기 쉽게 보여 줍니다. 전체 날짜 범위에 대해 단일 클러스터형 인덱스를 사용하는 대신, 분할되지 않은 팩트 테이블의 경우와 마찬가지로 각 파티션에 있는 팩트 테이블의 날짜 열에 클러스터형 인덱스가 있습니다. 이제, 지난 일주일의 판매 현황을 요약하는 Q라는 쿼리를 가정합니다. 새 판매 데이터는 팩트 테이블의 마지막 파티션(P4)에 계속 입력되지만 쿼리는 실행되는 시점에 따라 여러 파티션을 사용하게 됩니다. 다이어그램의 첫 번째 행에서 이것을 볼 수 있습니다. 즉, Q1 쿼리는 단일 파티션만 사용하지만 실행 시점에 관련 데이터가 P3 및 P4 파티션에 걸쳐 있는 Q2 쿼리는 두 개의 파티션을 사용합니다.
그림 5** 새 PTP 기능의 작동 방식 **(더 크게 보려면 이미지를 클릭하십시오.)
이제 사용할 수 있는 스레드가 8개라고 가정해 보겠습니다. SQL Server 2005에서 Q1 및 Q2를 실행하면 예기치 않은 동작이 발생할 수 있습니다. SQL Server 2005에서는 최적화 프로그램이 컴파일 시에 쿼리가 단일 파티션만 사용한다는 것을 인식하면 해당 파티션을 분할되지 않은 단일 테이블로 취급하고 모든 사용 가능한 스레드를 할당하여 테이블에 액세스하는 계획을 생성하는 방식으로 최적화를 수행합니다.
결과적으로 단일 파티션(P3)과 관련된 Q1은 8개의 스레드로 처리되는 계획을 만들게 됩니다(표시 안 됨). 하지만 두 개의 파티션을 사용하는 Q2의 경우에는 기반 하드웨어에 추가로 사용할 수 있는 스레드가 있음에도 실행기가 각 파티션에 단일 스레드를 할당합니다. 따라서 Q2는 사용 가능한 CPU 처리 능력 중 극히 일부만 사용하게 되고 대개 Q1보다 상당히 느리게 실행됩니다.
SQL Server 2008에서 Q1 및 Q2를 실행하면 하드웨어를 훨씬 잘 활용하게 되고 더 뛰어난 성능과 예측 가능한 결과 동작이라는 이점을 얻게 됩니다. 즉, Q1의 경우에는 이전과 마찬가지로 실행기가 사용 가능한 8개 스레드 모두를 할당하여 P2에 있는 데이터를 처리합니다(표시 안 됨). 하지만 Q2의 경우 실행기가 모든 사용 가능한 스레드를 라운드 로빈 방식으로 P3과 P4에 할당하는 병렬 계획이 만들어지므로, 다이어그램의 맨 아래 행에서 볼 수 있는 것처럼 두 파티션이 각각 4개의 스레드를 할당 받는 효과가 나타납니다. 따라서 CPU 처리 능력을 최대한 사용하게 되므로 Q1과 Q2의 성능이 비슷해집니다.
이 라운드 로빈 스레드 할당 방식을 사용하면 쿼리가 액세스하는 파티션 수보다 많은 수의 프로세서 코어를 사용하여 쿼리 성능을 향상시킬 수 있습니다. 그러나 유감스럽게도 파티션에 대한 스레드 할당이 이 예제의 경우처럼 간단하지 않은 경우가 많습니다.
다중 코어 프로세서 컴퓨터의 분할된 테이블 시나리오에 있어서, SQL Server 2005와 비교하여 SQL Server 2008에서 얻을 수 있는 성능 이점에 대한 자세한 설명은 그림 6을 참조하십시오. 이 그래프는 분할된 테이블의 검색 성능을 강조하여 보여 줍니다. 64개의 코어와 256GB의 RAM이 있는 시스템에서 수행된 이 테스트의 경우 121GB의 단일 테이블을 각각의 크기가 11GB인 11개의 파티션으로 분할했습니다. 이 그림에서 설명하는 일련의 테스트를 위해 콜드 및 웜 버퍼 시작을 모두 사용하는 힙 파일 구조를 사용했습니다. 모든 쿼리는 데이터에 대한 단순한 검색을 수행합니다.
그림 6** 새 PTP 기능을 사용하는 SQL Server의 검색 성능 **(더 크게 보려면 이미지를 클릭하십시오.)
y축은 응답 시간(초)을 나타내고 x축은 병렬 처리 수준(DOP)을 나타냅니다. 여기에서 DOP는 쿼리에 할당된 스레드 수와 유사합니다. 그림에서 확인할 수 있는 것처럼, 콜드 부팅과 웜 부팅 모두에서 DOP가 22에 도달할 때까지 응답 시간이 지속적으로 감소합니다. 콜드 부팅의 경우 DOP가 22에 도달하면 I/O 시스템이 가득 차게 됩니다. 이것은 이 예제에 사용된 쿼리가 I/O에 바인딩되어 있기 때문입니다. CPU에 바인딩된 작업이 더 많은 경우에는 이 제한이 나타나지 않거나 훨씬 높은 DOP에서 나타날 수 있습니다.
하지만 웜 부팅을 나타내는 곡선은 DOP 수준이 증가함에 따라 응답 시간이 계속 감소함을 보여 줍니다. SQL Server 2005에서는 다중 파티션을 처리할 때 파티션당 스레드 수가 1로 제한되기 때문에 DOP 11 근처에서 두 곡선이 평탄해지기 시작합니다.
DOP의 숫자를 늘려 얻을 수 있는 응답 시간 감소가 실제로는 선형이 아니라는 것을 기억해야 합니다. 예상되는 동작은 선형보다는 계단 함수를 닮았습니다. 이것은 쿼리가 본질적으로 가장 느린 부분이 완료될 때까지 대기해야 한다는 현실을 반영합니다. 즉, 모든 검색이 추가 스레드를 받아 모두 함께 빨라지기 전까지는 검색에 한두 개의 스레드를 추가해도 쿼리 완료 시간이 향상되지 않습니다.
Microsoft에서는 다른 다양한 하드웨어와 파일 구성에서 새 PTP의 동작을 테스트하는 추가 실험을 했습니다. 이러한 실험에서도 파티션당 스레드가 1 이상이 되는 DOP 증가 시점에 처리량이 증가하는 유사한 동작이 관찰되었습니다.
마지막으로 한 가지 중요한 사실을 더 언급하면, SQL Server 2008의 새 PTP 기능은 쿼리 계획의 가독성을 향상시키며 특정 작업이 어떻게 실행되는지 보다 잘 이해할 수 있게 해 줍니다. 예를 들어 XML 실행 계획에서 병렬 및 직렬 계획을 표현하는 방식, 컴파일 타임 실행 계획과 런타임 실행 계획 모두에 제공되는 분할 정보 등 PTP 기능에 속하는 여러 기능이 향상되었습니다.
데이터 압축
비즈니스 인텔리전스가 보편화되면서 데이터 웨어하우스에 저장되어 분석해야 하는 데이터의 양도 함께 늘어나고 있습니다. 그에 따라 관리가 필요한 데이터 크기도 기하급수적으로 증가하고 있습니다. 1995년에 처음 수행된 Winter Corporation의 데이터베이스 크기 조사에서 보고된 세계에서 가장 큰 시스템은 테라바이트 크기의 데이터를 포함하는 것이었습니다. 십 년이 지난 지금, 규모가 큰 데이터베이스는 그보다 100배 이상 큽니다. 더 놀라운 사실은 데이터 웨어하우스 크기가 2년마다 세 배로 커지고 있다는 것입니다. 그로 인해 대량의 데이터를 관리하고 데이터 웨어하우스 쿼리의 성능을 적절하게 유지하는 것과 관련된 새로운 어려움이 생겨났습니다. 이러한 데이터 웨어하우스 쿼리는 대개 많은 조인과 집계를 포함한 복잡한 쿼리이며 엄청난 양의 데이터에 액세스합니다. 또한 작업에 사용되는 쿼리가 I/O에 바인딩되는 경우도 있습니다.
네이티브 데이터 압축의 목표는 이 문제를 해결하는 것입니다. SQL Server 2005 SP2에는 소수점과 숫자 데이터를 위한 새로운 가변 길이 저장소 형식인 vardecimal 저장소 형식이 도입되었습니다. 이 새 저장소 형식을 사용하면 데이터베이스 크기를 크게 줄일 수 있습니다. 그에 따라 공간이 절약되므로 두 가지 측면에서 I/O 바인딩된 쿼리의 성능이 향상됩니다. 첫째는, 더 적은 수의 페이지를 읽음으로써 얻게 되는 성능 향상입니다. 두 번째는, 데이터가 버퍼 풀에 압축된 상태로 유지되므로 페이지 기대 수명(요청된 페이지를 버퍼에서 찾을 가능성)이 향상되는 것입니다. 물론, 데이터 압축을 통한 공간 절약 방식의 데이터 압축과 압축 해제 프로세스 때문에 CPU 비용이 발생합니다.
SQL Server 2008은 행 압축과 페이지 압축이라는 두 종류의 압축 방식을 제공하여 vardecimal 저장소 형식을 구축합니다. 행 압축은 모든 고정 길이 데이터 형식을 가변 길이 저장소 형식으로 저장하여 vardecimal 저장소 형식을 확장합니다.
정수, 문자, 부동 소수점 등이 고정 길이 데이터 형식의 몇 가지 예입니다. SQL Server가 이러한 데이터 형식을 가변 길이 형식으로 저장하지만 데이터 형식의 의미 체계는 그대로 유지됩니다. 즉, 응용 프로그램 관점에서 데이터 형식은 계속 고정 길이 데이터 형식입니다. 이것은 응용 프로그램을 변경하지 않아도 데이터 압축의 이점을 얻을 수 있다는 의미입니다.
페이지 압축은 지정된 페이지의 하나 이상의 행에서 열의 데이터 중복을 최소화합니다. 이 압축 방식은 LZ78(Lempel-Ziv) 알고리즘의 독점적인 구현을 사용하여 중복되는 데이터를 페이지에 한 번만 저장한 후 여러 열에서 참조하도록 합니다. 페이지 압축을 사용할 때 실제로는 행 압축이 포함된다는 점에 유의하십시오.
행 및 페이지 압축은 테이블이나 인덱스 또는 분할된 테이블 및 인덱스에 대한 하나 이상의 파티션에 사용할 수 있습니다. 테이블, 인덱스 및 파티션을 압축할 수 있는 완벽한 융통성이 제공되므로 공간 절약과 CPU 사용량 간에서 적절한 균형을 이루는 선택을 할 수 있습니다. 그림 7에서는 정렬된 인덱스를 통해 서로 다른 방식으로 분할된 판매 테이블을 사용하여 이러한 균형을 보여 줍니다.
그림 7** 서로 다른 압축 설정의 분할된 테이블 **(더 크게 보려면 이미지를 클릭하십시오.)
각 파티션은 분기를 나타내며 10-12월이 마지막 분기입니다. 처음 두 파티션은 자주 액세스되지 않으며 세 번째 파티션은 중간, 마지막 파티션이 가장 활발하게 액세스된다고 가정해 보겠습니다. 이 경우 가능한 구성 중 하나는 처음 두 파티션에는 페이지 압축을 사용하여 작업 성능에 미치는 영향을 최소화하면서 공간을 최대한으로 절약하고 세 번째 파티션에는 행 압축을 사용하고 마지막 파티션에는 압축을 사용하지 않는 것입니다.
Alter Table 또는 Alter Index DDL(데이터 정의 언어) 문을 사용하여 오프라인 또는 온라인 상태에서 압축을 사용할 수 있습니다. 또한 SQL Server는 절약할 수 있는 공간 크기를 예측하는 저장 프로시저를 제공합니다. 절약할 수 있는 공간의 크기는 압축 대상 개체의 스키마와 데이터 분포에 따라 달라집니다.
많은 고객 데이터베이스를 사용한 테스트에서 확인된 결과에 따르면, 대부분의 고객이 데이터베이스 크기를 50–65% 줄이고 I/O 바인딩된 쿼리의 성능을 상당히 향상시킬 수 있습니다. 하지만 CPU 바인딩된 쿼리의 성능에 미치는 영향을 예상하는 작업에는 약간의 기술이 필요하며 쿼리의 복잡성에 따라 결과가 달라집니다. SQL Server에서 압축 해제 비용은 인덱스나 테이블에 액세스할 경우에만 발생합니다. 검색 연산자의 상대적인 CPU 비용이 쿼리의 전체 CPU 비용에 비해 낮은 경우(대부분의 데이터 웨어하우스 시나리오가 이에 해당) CPU 사용률에 미치는 영향은 20-30% 이하입니다.
파티션 정렬 인덱싱된 뷰
SQL Server 2008에서 파티션 정렬 인덱싱된 뷰를 사용하면 관계형 데이터 웨어하우스의 요약 집계를 보다 효율적으로 만들고 관리할 수 있으며 이전에는 효율이 떨어졌던 시나리오에 이 요약 집계를 사용할 수 있게 됩니다. 이를 통해 쿼리 성능이 향상됩니다. 일반적인 시나리오에는 날짜별로 분할된 팩트 테이블이 있습니다. 쿼리 속도를 높이기 위해 이 테이블에서 인덱싱된 뷰(또는 요약 집계)가 정의됩니다. 새 테이블 파티션으로 전환할 때는 파티션 정렬 인덱싱 뷰의 일치하는 파티션이 분할된 테이블 스위치에도 정의되며 이 작업은 자동으로 수행됩니다.
이것은 ALTER TABLE SWITCH 작업을 사용하여 파티션을 전환하기 전에 분할된 테이블에 정의된 모든 인덱싱된 뷰를 삭제해야 하는 SQL Server 2005에 비해 기능이 상당히 개선된 것입니다. SQL Server 2008의 파티션 정렬 인덱싱된 뷰 기능에는 분할된 전체 테이블에서 집계를 다시 구축할 필요 없이 대규모 분할된 테이블에서 인덱싱된 뷰를 사용할 수 있다는 이점이 있습니다. 또한 집계와 인덱싱된 뷰 일치가 자동으로 유지 관리된다는 이점도 있습니다.
파티션 수준 잠금 에스컬레이션
SQL Server는 관리 효율성을 위해 데이터를 분할하거나 사용 패턴에 따라 데이터를 그룹화할 수 있는 범위 분할을 지원합니다. 예를 들어 판매 데이터를 월별이나 분기별로 분할할 수 있습니다. 파티션을 고유한 파일 그룹에 매핑할 수 있으며, 파일 그룹은 파일 집합에 매핑할 수 있습니다. 이러한 매핑으로 두 가지 이점을 얻을 수 있습니다. 첫째, 파티션을 독립적인 단위로 백업하고 복원할 수 있습니다. 둘째, 파일 그룹을 사용 패턴이나 쿼리 부하에 따라 빠르거나 느린 I/O 하위 시스템에 매핑할 수 있습니다.
여기서 흥미로운 점은 데이터 액세스 패턴입니다. 쿼리와 DML 작업은 파티션의 일부분만 액세스하거나 조작해야 합니다. 예를 들어 2004년의 판매 데이터를 분석하는 경우 관련 파티션만 액세스해야 하며, 이상적인 경우 시스템 리소스를 제외하면 다른 파티션의 데이터에 동시에 액세스하는 쿼리의 영향을 받지 않아야 합니다. SQL Server 2005에서는 다른 파티션의 데이터에 대한 동시 액세스가 테이블 잠금으로 이어져 다른 파티션에 대한 액세스에 영향을 미칠 수 있습니다.
이러한 간섭을 최소화하기 위해 SQL Server 2008에는 파티션 또는 테이블 수준에서 잠금 에스컬레이션을 제어하는 테이블 수준 옵션이 도입되었습니다. SQL Server 2005와 마찬가지로 잠금 에스컬레이션은 기본적으로 테이블 수준에서 설정됩니다. 하지만 테이블에 대한 잠금 에스컬레이션 정책을 재정의할 수 있습니다. 예를 들어 다음과 같은 명령으로 잠금 에스컬레이션을 설정할 수 있습니다.
Alter table <mytable> set (LOCK_ESCALATION = AUTO)
이 명령을 사용하면 SQL Server가 테이블 스키마에 적합한 잠금 에스컬레이션 세분성을 선택합니다. 테이블이 분할되어 있지 않으면 잠금 에스컬레이션은 테이블 수준이 됩니다. 테이블이 분할되어 있으면 잠금 에스컬레이션 세분성은 파티션 수준이 됩니다. 이 옵션은 SQL Server가 테이블 수준에서 잠금 세분성을 제한하는 힌트로 사용되기도 합니다.
결론
이 문서는 관계형 데이터 웨어하우스에서 의사 결정 지원 쿼리의 성능을 향상시킬 수 있는 SQL Server 2008의 향상된 기능에 대한 간략한 개요일 뿐입니다. 또한 의사 결정 지원 쿼리의 빠른 응답 시간이 중요하기는 하지만 이 문서의 범위를 넘어서는 다른 중요한 요구 사항이 있을 수 있다는 점을 명심해야 합니다.
관계형 데이터 웨어하우스와 관련된 몇 가지 추가 기능은 다음과 같습니다.
- T-SQL에서 단일 명령으로 (차원) 데이터의 업데이트, 삭제 또는 삽입을 수행하고 데이터베이스에 라운드 트립하는 MERGE 구문 지원
- 보다 효율적인 ETL을 가능하게 만드는 SQL Server 엔진의 최적화된 로깅 성능
- T-SQL로 집계 의사 결정 지원 쿼리를 손쉽게 작성할 수 있도록 하는 Grouping Sets
- 전체 백업과 증분 백업 모두에서 I/O 요구 사항을 줄이는 백업 압축
- 서로 다른 작업에 대한 시스템 리소스 할당을 제어하는 리소스 관리
SQL Server 웹 페이지(microsoft.com/sql)에서 다른 모든 놀라운 기능을 자세하게 살펴보시기 바랍니다.
전문 기술과 관련된 조언을 해 주신 Boris Baryshnikov, Prem Mehra, Peter Zabback 그리고 Shin Zhang 씨에게 감사 드립니다.
Sunil Agarwal은 Microsoft의 SQL Server 저장소 엔진 그룹에서 선임 프로그램 관리자로 근무하고 있으며 동시성, 인덱스, tempdb, LOBS, 지원 가능성 및 대량 가져오기/내보내기 기능을 담당하고 있습니다.
Torsten Grabs는 Microsoft의 SQL Server 팀의 핵심 저장소 엔진 그룹에서 선임 프로그램 관리자로 근무하고 있습니다. Torsten Grabs는 데이터베이스 시스템 분야의 박사 학위를 보유하고 있으며 10년 이상의 SQL Server 개발 경험을 가지고 있습니다.
Joachim Hammer 박사는 Microsoft의 쿼리 처리 그룹에서 프로그램 관리자로 근무하고 있습니다. 그의 전문 분야는 대규모 데이터 웨어하우스 응용 프로그램의 쿼리 최적화이며 분산 쿼리, ETL, 정보 통합 등에도 전문 지식을 가지고 있습니다.
© 2008 Microsoft Corporation 및 CMP Media, LLC. All rights reserved. 이 문서의 전부 또는 일부를 무단으로 복제하는 행위는 금지됩니다..