저번시간까지는 서버부터 데이터베이스까지 셋팅을 변경하는 부분에 대해서 알아 보았습니다. 이번 시간에는 테이블에 대한 부분 입니다. 그 중에서 테이블 압축기능에 먼저 살펴 보겠습니다.

MSSQL 2008부터 지원하는 압축기능은 너무 너무 좋습니다. DW시스템에는 반드시 적용되어야 할 옵션입니다. 정수열 기반으로 저장되는 팩트 테이블에는 평균적으로 1/4으로 사이즈가 줄어 들게 되는데 사이즈가 1/4로 줄어 든 만큼 속도가 1/4으로 줄어 듭니다. 압축하고 해제 하는 데에는 물론 CPU 비용이 더 들게 마련이지만, OLTP에 비해서 많은 사용자가 이용하지 않고 대용량을 저장하고 읽어 오는 OLAP 워크로드라면 적용에 망설임이 없습니다.

테이블압축에는 크게 ROW단위 와 PAGE단위로 두 단위를 제공하고 있으며, PAGE압축의 경우, ROW압축을 한 후, 다시 접두사와 사전압축 과정을 거치므로 좀더 향상된 압축을 하게 됩니다. 그래서 PAGE압축이 더 많이 압축된 효과를 볼 수 있습니다.

테이블 압축을 하기 전에 적용 전후 용량을 알 수 있다면 좋을 것 같습니다. 다음 SP를 통하여 알 수 있습니다.

EXEC sp_estimate_data_compression_savings 'dbo', 'FactInternetSales', NULL, NULL, 'ROW' ;

 

압축을 하는 과정은 다음과 같습니다.

01.png

 

02.png

로 마법사를 실행한 후,

 

03.png

 

압축유형을 선택한 후, 계산버튼 눌러서 압축이 얼마나 되는지 계산해 봅니다.

다음을 누른 후,

04.png

 

새 쿼리 창으로 스크립팅을 해 봅니다.

 

USE [AdventureWorksDW2012]

ALTER TABLE [dbo].[FactInternetSales] REBUILD PARTITION = ALL

WITH

(DATA_COMPRESSION = PAGE)

 

해당 스크립트를 실행한 후, 개체 탐식개의 테이블정보에서 사용된 데이터 공간이 줄어 든 것을 확인 하실 수 있습니다.

 

<압축실행 전>

05.png

 

<압축실행 후>

06.png

 

10080에서 2336 4배 이상의 압축 효율을 확인할 수 있습니다.

 

용량이 줄어들면 얼마나 쿼리 시간이 단축되는지 알아 보겠습니다. 천만 건 정도 테스트 테이블을 만들어 봅니다.

 

USE [AdventureWorksDW2012]

--천만건을 만들어서, SUM값의 차이에 대해서 알아 보기.

SELECT TOP 10000000 *

        INTO BIG_FactInternetSales

        FROM FactInternetSales

        CROSS JOIN DimDate

테이블을 ROW압축용과 PAGE압축용으로 복사하고 각 타입으로 압축합니다.

 

--ROW압축 테이블 복사

SELECT * INTO BIG_FactInternetSales_ROW

FROM BIG_FactInternetSales

;

--ROW단위로 테이블 압축

ALTER TABLE BIG_FactInternetSales_ROW REBUILD PARTITION = ALL

WITH (DATA_COMPRESSION = ROW)

;

--PAGE압축 테이블 복사

SELECT * INTO BIG_FactInternetSales_PAGE

FROM BIG_FactInternetSales

;

--PAGE단위로 테이블 압축

ALTER TABLE BIG_FactInternetSales_PAGE REBUILD PARTITION = ALL

WITH (DATA_COMPRESSION = PAGE)

;

 

준비가 끝났으면, 다음 쿼리를 각각 실행하여 읽기페이지수 와 시간을 확인하여 봅니다.

 

SET NOCOUNT ON

SET STATISTICS IO ON

SET STATISTICS TIME ON

SET STATISTICS PROFILE ON

 

SELECT SUM(SalesAmount)

FROM BIG_FactInternetSales

;

SELECT SUM(SalesAmount)

FROM BIG_FactInternetSales_ROW

;

SELECT SUM(SalesAmount)

FROM BIG_FactInternetSales_PAGE

;

 

쿼리 결과는 다음과 같았습니다.

 

07.png

 

용량은 ROW의 경우 2배정도, PAGE의 경우는 4배정도 줄어 들면서 쿼리시간 또한 비례하여 단축된 것을 확인할 수 있습니다. DISK IO가 줄어 들게 되므로 특별한 튜닝 없이도 4배이상 성능으로 극대화 할 수 있습니다. 또한 디스크 용량을 절약함으로써 경제적인 이익도 있습니다.

이처럼 DW에서 압축은 매우 중요합니다. 그러나 이렇게 좋은 기능이 아쉽게도 엔터프라이즈에서만 지원하고 있습니다. 다음시간에 배우게 되는 파티션과 Column Store Index 또한 대용량 DB에서 빼먹을 수 없는 기능이므로 많은 기대 바랍니다.

 

 

<목차>

  1. BI, Microsoft BI 소개
  2. DW, DM, OLAP의 이해
  3. 다차원모델링(1/2) – 스키마, Fact테이블
  4. 다차원모델링(2/2) - Dimension테이블, 다차원DB구축 과정 정리
  5. MSSQL 2012 DW 셋팅
  6. 압축
  7. 파티션
  8. 컬럼스토어 인덱스
  9. MSSQL 2012 Semantic Model 소개
  10. MSSQL 2012 Power Pivot
  11. MSSQL 2012 Tabular Model(1/2) – 기본편
  12. MSSQL 2012 Tabular Model(2/3) – 고급편
  13. MSSQL 2012 Tabular Model(3/3) - DAX
  14. MSSQL 2012 Dimension Model(1/5) - 차원
  15. MSSQL 2012 Dimension Model(2/5) - 측정값그룹, 큐브
  16. MSSQL 2012 Dimension Model(3/5) - 파티션
  17. MSSQL 2012 Dimension Model(4/5) - 집계 디자인
  18. MSSQL 2012 Dimension Model(5/5) - 계산된 메져, MDX
  19. MSSQL 2012 DQ
  20. MSSQL 2012 MDS

 





profile

안녕하세요, SQLER의 바쉬*^^*, 김상수입니다. 행복 하세욤~. ^.^
위세아이텍 연구소 책임 / SQLER 시샵 / BI Developer
E-Mail: bash1130@hotmail.com / Mobile: +82 10-6231-7672 / NateOn:bash1130@nate.com