MS-SQL Server 2008 부터 지원되는 데이터 압축 기능

MS-SQL Server 2008 부터 지원되는 데이터 압축 기능

 

•압축방식
  –행압축
    •고정길이데이터형식을가변길이데이터형식처럼저장
    •행메타데이터최적화
  –페이지압축

    •행압축
    •접두사및사전

 

 

 

 데이터를 압축하여 보관 하게 되므로 아래와 같은 장단점이 있다

 

•장점
–물리적인저장공간감소
–메모리내데이터압축
–동일한쿼리의결과반환을위해더적은데이터페이지를참조하므로I/O가감소됨
–어플리케이션수정필요없음

•단점
–CPU 자원사용률이증가함

 

  압축 전후의 차이는 CPU 사용율이 증가하나 Query속도는 비슷하다.

 

DW 또는대용량데이터마트의경우CPU 자원에여유가있으면페이지압축검토

행압축이데이터공간을절약하고,CPU 사용률이10% 정도증가할경우행압축검토

 

•인덱스재작성시평균CPU 자원사용량
  –행압축: 약1.5배필요
  –페이지압축: 약2~5배필요

 

(실제 구현)

 

http://msdn.microsoft.com/ko-kr/library/cc280449.aspx

 

 

(펌 자료)

 

SQL Server 2008(Enterprise Edition)에서는 테이블 및 인덱스 모두에 대해 행 압축과 페이지 압축을 지원합니다.

 

１     행 압축
행 압축을 사용하면 데이터 형식과 연결된 데이터의 물리적 저장소 형식이 변경됩니다. 예를 들어 테이블에  데이터 형식을 BIGINT 를 사용했다고 가정한고 값이 10이 입력되었다고 하면 행 압축을 하면 10을 저장하기 위해서 BIGINT 데이터 형식으 크기 8 바이트가 아니라 값을 기준으로 필요한 바이트 즉 1바이트만 사용하게 됩니다.

 

２     페이지 압축
페이지 압축은 테이블, 테이블 파티션, 인덱스 및 인덱스 파티션에 대한 압축과 유사합니다. 페이지 압축을 사용할 경우 인덱스의 리프 수준이 아닌 페이지는 행 압축만 사용하여 압축됩니다. 테이블 및 인덱스의 리프수준을 압축은 접두사 압축 후 사전 압축 작업을 진행하여 압축을 합니다.  

가)    접두사 압축

접두사 압축을 진행하면 접두사는 헤더로 이동하고 열 값은 접두사의 참조로 변경됩니다.

접두사 압축전 테이블 페이지	접두사 압축 후 테이블 페이지

 

나)    사전 압축

사전 압축은 페이지에서 반복된 값을 검색하여 CI 영역에 저장합니다.

접두사 압축 후 테이블 페이지	사전 압축 후 테이블 페이지

페이지 압축은 데이터가 첫 데이터 페이지에 추가되는 시점에 데이터의 행이 압축됩니다. 페이지가 가득 차지 않으면 페이지 압축으로 얻는 이득이 없어 행이 가륵 차면 추가할 다음 행에서 페이지 압축 작업을 시작합니다. 그리고 압축하기 전에 페이지 압축으로 얻는 공간에서CI 구조에 필요한 공간을 뺸 값이 크지 않으면 해당 페이지에 대한 페이지 압축을 하지 않습니다. 
 

 

3     압축된 테이블 쿼리

가)    테이블 생성

CREATE TABLE T1 (c1 int, c2 nvarchar(1024) ) WITH (DATA_COMPRESSION = PAGE | ROW);

 

나)    테이블 변경

ALTER TABLE T1 REBUILD WITH (DATA_COMPRESSION = PAGE | ROW);

 

4    

성능 테스트
가)    개요

①     샘플 데이터 1000만 건에 대해서 압축안함, 행 압축, 페이지 압축에 대해서 데이터 크기 및 Select 쿼리 성능 및 Insert 쿼리 성능 측정

나)    데이터 크기

 

데이터 사이즈는 테이블 구조에 따라 틀리겠지만 Page 압축을 했을떄 48%, Row 압축시77%의 압축율을 보입니다.

 

  

가)    Select 쿼리 성능

           Page

압축의 경우 압축안함과 비교하여 CPU는 118%로 조금 더 사용하지만 Reads 에서
           208% Duaraion에서 155%의 쿼리 성능이 향상 된다.

 

나)    대량 데이터 Insert 쿼리 성능

 

 

단일 데이터 입력은 거의 성능차이가 없기떄문에 대량 데이터 입력으로 쿼리 성능을 측정하였습니다. 대량 데이터 입력(백만건)시 Page 압축은 압축 안함과 비교하여 CPU 자원을 압 235%로 자원을 더 사용하며 Duration 또한 249% 더 증가한다. Write 의 경우는 압축율에 비래하여 50% 정도로 감소한다.

 

 

 

5   결론

테이블 압축을 사용을 하면서 데이터 공간의 절약을 할 수 있으며 또한 데이터 조회시 압축을 함으로써 압축을 복원하는데 CPU 자원을 약간 더 사용을 하지만 Reads 및 Duration 에 많은 성능 이점을 가지고 있습니다. 대량 데이터 Insert 시점에는 다소 성능이 떨어집니다. OLTP 환경에서 데이터 건수가 많고 조회가 많은 테이블에 대해서는 테이블 압축을 적용하면 많은 이점을 얻을 수 있을거라 판단됩니다. 또한 OLAP 환경에서도 대량 데이터를 입력하는 Insert 쿼리는 다소 떨어지지만 저장공간 및 집계 및 조회 성능 이점이 많을거라 판단됩니다.

[출처] MS-SQL 2008 데이터 압축기능 |작성자 야생곰탱