SQL Server 2012 Columnstore Index

SQL Server 2012 Columnstore Index

   

-       Version : SQL Server 2012

   

SQL Server 2012 부터는 데이터베이스 테이블에 columnsotre 인덱스를 정의 할 수 있다. 클러스터형 인덱스와 비클러스터형의 rowstore(columnar) 인덱스 저장 데이터를 기존의 B-tree 구조와 달리 column-wise(in row) 형식으로 columnstore 인덱스를 저장한다.

   

Columnstore 인덱스는 인덱스를 형성하기 위해 여러 열이 함께 결합되어 구성된다. 이 구조는 많은 양의 데이터를 요약하여 쿼리 할 때 상당한 성능 향상을 제공한다. 일반적으로 BI 및 DW 환경에서 많이 사용된다.

   

[columnstore 구조]

Columnstore 인덱스는 xVelocity(VertiPaq 이라고도 함)를 기반으로 한다. 이는 고급 스토리지와 PowerPivot 및 분석 서비스에서 유래하지만 SQL Server 2012의 압축 기술을 기반으로 한다.

   

이 모델의 핵심 구조는 그 그룹의 행이 아닌 컬럼으로 데이터를 그룹화 한다. 간단히 예제를 통해서 살펴보도록 하자. 스크립트에서 AutoID는 기본키로 SQL Server에서 AutoID를 기준으로 클러스터형 인덱스를 생성한다.

CREATE TABLE AutoType ( AutoID INT PRIMARY KEY, Make VARCHAR(20) NOT NULL, Model VARCHAR(20) NOT NULL, Color VARCHAR(15) NOT NULL, ModelYear SMALLINT NOT NULL );

   

아래 그림은 데이터를 입력 하였을 때 데이터 페이지에 저장된 구조를 나타내고 있다. 데이터는 5개의 행이 3페이지에 분할되어 저장되어 있다. (사실 이보다 더 많은 데이터(1page = 8K)가 저장되지만 예제를 위한 그림이니 오해 하지 말기를 바란다.)

   

다음과 같은 쿼리를 실행한다고 한다고 가정하였을 때 데이터베이스에서는 3페이지의 데이터가 메모리로 로드되고 쿼리 요청의 결과 값을 추출 한다.

SELECT Make, Year FROM AutoType;

   

 

   

즉 데이터베이스 엔진이 쿼리를 처리할 때 전체 테이블을 가져오는 작업에서 필요하지 않는 열을 함께 가져오기 때문에 3페이지에 걸쳐 불필요한 I/O가 발생하고 메모리 자원이 낭비 된다.

   

이제 columnstore 인덱스를 만들면 어떻게 작업이 이루어지는지 알아 보자. Columnstore 인덱스를 생성하면 다음과 같이 사용자가 지정한 열만 구성하여 인덱스를 구성 한다. 데이터는 더 이상 행으로 저장되지 않는다.

   

Columnstore 인덱스에서 각 컬럼은 그 자체가 세그먼트가 된다. 세그먼트는 각 컬럼의 데이터가 독립적으로 액세스 할 수 있는 하나의 컬럼 값만을 포함 할 수 있다. 그러나 컬럼은 여러 세그먼트로 확장 될 수 있으며 각 세그먼트는 여러 데이터 페이지로 구성 할 수 있다. 페이지가 없을 경우에는 디스크에서 메모리로 데이터가 전송 된다. 세그먼트는 백만개의 행을 포함할 정도로 고도로 압축된 LOB 형식이다.

   

Columnstore 인덱스 생성 후 다시 쿼리를 실행하면 쿼리 프로세스에서는 클러스터형 인덱스보다 columnstore 인덱스를 사용한다. 결과적으로 make컬럼과 year컬럼의 연관된 세그먼트를 따라 쿼리를 처리하는데 필요한 자원을 메모리로 가져오며 columnstore 구조는 I/O를 줄일 수 있다.

   

물론 실제 운영환경에서는 대부분의 데이터가 하나의 세그먼트에 모두 포함되지 못할 정도로 큰 데이터 이다. 이 경우 여러 세그먼트는 각 열에 대해 생성된 여러 행 그룹이 하나의 세그먼트로 그룹화 된다.

 

   

Columnstore 인덱스가 여러 행 그룹일 때 각 행 그룹은 완전한 행 세트가 포함되어 있다. 예를 들어 columnstore 인덱스가 지금은 3개의 행 그룹으로 나누어 보여주지만 각 행 그룹은 각 열에 대한 세그먼트를 포함하고 그 세그먼트 행의 전체 세트가 함께 포함되어 있다.

   

또한 특정 열과 관련하여 각각의 사전을 구성 한다. 사전은 문자열 데이터 형식이나 문자열이 아닌 열에 대해서도 고유한 값이 포함되어 있는 경우 값을 인코딩 한다.

   

사전을 사용할 경우 사전에 컬럼의 실제 데이터 값을 저장하고 숫자 참조 값은 해당 값 대신 세그먼트로 삽입된다. 이것은 많은 반복 값을 포함하는 컬럼에 대해 큰 성능 이점을 제공할 수 있지만 고유 값이 많은 컬럼에 부정적인 영향을 미칠수도 있다. 문자열은 항상 기본 사전과 보조 사전을 사용한다.

   

   

[Columnstore 인덱스 실행]

Columnstore의 가장 큰 이점은 I/O를 줄여 성능을 높이는 것이다. AdventureworksDW2012 예제 데이터베이스를 이용하여 결과를 살펴 보자.

SELECT ProductKeyM, UnitPrice, CustomerPONumber, OrderDate FROM FactResellerSales;

   

Columnstore 인덱스를 생성하고 결과를 살펴 보자.

CREATE NONCLUSTERED COLUMNSTORE INDEX csi_FactResellerSales ON dbo.FactResellerSales (ProductKey, UnitPrice, CustomerPONumber, OrderDate);

   

   

실행계획을 살펴보면 I/O 및 CPU 비용이 줄어든 것을 확인 할 수 있다. 압축은 SQL Server 2012에서 새로운 기능은 아니지만 columnstore 인덱스가 데이터를 컬럼으로 그룹화 화기 때문에 데이터를 보다 효율적으로 압축 할 수 있다.

   

[정리]

Columnstore 인덱스를 BI 워크로드에 적용하면 고도로 압축된 컬럼 인덱스 작업이 확실히 좋은 효율을 얻을 수 있다. 배치 모드 처리와 함께 팩트 테이블을 만들 때 데이터웨어하우스에 최적화 되어 있다. 스타스키마의 대형 테이블과 중소 테이블에서 최적의 성능을 보여준다.

   

하지만 columnstore 인덱스는 데이터를 업데이트 할 수 없는 단점이 있다. 그러나 columnstore 인덱스의 한계에도 불구하고 클러스터 인덱스와 비클러스터 인덱스를 사용하는 것보다 향상된 성능을 제공하기에 많이 사용되고 있다.

   

Columnstore 인덱스가 모든 쿼리에 만병 통치약은 아니지만 적절하게 사용할 경우 디스크 I/O를 줄이고 효율적으로 메모리를 사용할 수 있어 성능을 높일 수 있다.

   

   

[참고자료]

l  https://www.simple-talk.com/sql/database-administration/columnstore-indexes-in-sql-server-2012/

l  SQL Server Columnstore Performance Tuning :

http://social.technet.microsoft.com/wiki/contents/articles/4995.sql-server-columnstore-performance-tuning.aspx

   

   

   

2013-08-07 / 강성욱 / http://sqlmvp.kr