[MSSQL] 압축된 테이블 및 인덱스 만들기

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

압축된 테이블 및 인덱스 만들기

SQL Server 2008 R2

다른 버전

SQL Server 2008에서는 테이블 및 인덱스 모두에 대해 행 압축과 페이지 압축이 모두 지원됩니다. 다음과 같은 데이터베이스 개체에 대해 데이터 압축을 구성할 수 있습니다.

• 힙으로 저장되는 전체 테이블

• 클러스터형 인덱스로 저장되는 전체 테이블

• 전체 비클러스터형 인덱스

• 전체 인덱싱된 뷰

• 분할된 테이블과 인덱스의 경우 파티션별로 압축 옵션을 구성할 수 있고 개체의 다양한 파티션에 동일한 압축 설정을 구성할 필요가 없습니다.

테이블의 압축 설정은 비클러스터형 인덱스에 자동으로 적용되지 않습니다. 즉, 각 인덱스를 개별적으로 설정해야 합니다. 시스템 테이블에는 압축을 사용할 수 없습니다. 테이블과 인덱스는 CREATE TABLE 및 CREATE INDEX 문을 사용하여 만들 때 압축할 수 있습니다. 테이블, 인덱스 또는 파티션의 압축 상태를 변경하려면 ALTER TABLE 또는 ALTER INDEX 문을 사용하십시오.

참고
기존 데이터가 조각화된 경우 압축을 사용하지 않고 인덱스를 다시 작성하여 인덱스의 크기를 줄일 수 있습니다. 인덱스를 다시 작성할 때 인덱스의 채우기 비율이 적용되므로 인덱스 크기가 증가할 수도 있습니다. 자세한 내용은 채우기 비율을 참조하십시오.

행 및 페이지 압축 시 고려 사항

---

행 및 페이지 압축을 사용할 때 다음 사항을 고려해야 합니다.

• 압축은 SQL Server 2008 Enterprise Edition 및 Developer Edition에서만 사용할 수 있습니다.

• 압축하면 한 페이지에 더 많은 행을 저장할 수 있지만 테이블 또는 인덱스의 최대 행 크기는 변경되지 않습니다.

• 최대 행 크기와 압축 오버헤드를 더한 값이 최대 행 크기 8060바이트를 초과하는 테이블에서는 압축을 사용할 수 없습니다. 예를 들어 c1 char(8000) 및 c2char(53) 열이 있는 테이블은 추가 압축 오버헤드 때문에 압축할 수 없습니다. vardecimal 저장소 형식이 사용되는 경우 해당 형식을 사용하면 행 크기 검사가 수행됩니다. 행 및 페이지 압축의 경우 개체가 처음 압축될 때 행 크기 검사가 수행된 다음 각 행을 삽입하거나 수정할 때 검사됩니다. 압축에는 다음 두 가지 규칙이 적용됩니다.

  • 고정 길이 형식으로의 업데이트가 항상 성공해야 합니다.

  • 데이터 압축 비활성화에 항상 성공해야 합니다. 압축된 행이 8060바이트 미만으로 페이지에 맞더라도 SQL Server에서는 압축하지 않을 경우 행에 맞지 않는 업데이트 내용을 적용할 수 없도록 합니다.

• 파티션 목록을 지정하는 경우 개별 파티션에 대한 압축 유형을 ROW, PAGE 또는 NONE으로 설정할 수 있습니다. 파티션 목록을 지정하지 않은 경우에는 모든 파티션이 문에 지정된 데이터 압축 속성을 사용하여 설정됩니다. 테이블이나 인덱스를 만들 때 달리 지정하지 않는 한 데이터 압축이 NONE으로 설정됩니다. 테이블을 수정할 경우에는 달리 지정하지 않는 한 기존 압축이 유지됩니다.

• 범위를 벗어난 파티션 목록을 지정하면 오류가 발생합니다.

• 비클러스터형 인덱스는 테이블의 압축 속성을 상속하지 않습니다. 인덱스를 압축하려면 인덱스의 압축 속성을 명시적으로 설정해야 합니다. 기본적으로 인덱스를 만들 때 인덱스의 압축 설정은 NONE으로 설정됩니다.

• 힙에 클러스터형 인덱스를 만드는 경우 이 클러스터형 인덱스는 다른 압축 상태를 지정하지 않는 한 힙의 압축 상태를 상속합니다.

• 힙이 페이지 수준 압축을 사용하도록 구성된 경우 페이지는 다음과 같은 방식으로만 페이지 수준 압축을 받습니다.

  • BULK INSERT 구문을 사용하여 데이터를 삽입합니다.

  • INSERT INTO ... WITH (TABLOCK) 구문을 사용하여 데이터를 삽입합니다.

  • ALTER TABLE ... REBUILD 문을 PAGE 압축 옵션과 함께 실행하여 테이블을 다시 작성합니다.

• DML 작업의 일부로 힙에 할당된 새 페이지에서는 힙이 다시 작성될 때까지 PAGE 압축을 사용하지 않습니다. 압축을 제거하고 다시 적용하거나, 클러스터형 인덱스를 만들거나 제거하여 힙을 다시 작성하십시오.

• 힙의 압축 설정을 변경하는 경우 힙의 새 행 위치에 대한 포인터를 포함하도록 테이블의 모든 비클러스터형 인덱스를 다시 작성해야 합니다.

• 온라인이나 오프라인으로 ROW 또는 PAGE 압축을 사용하거나 사용하지 않도록 설정할 수 있습니다. 힙에서 압축을 사용하도록 설정하는 것은 온라인 작업의 경우 단일 스레드입니다.

• 행 또는 페이지 압축을 사용하거나 사용하지 않도록 설정하기 위한 디스크 공간 요구 사항은 인덱스를 만들거나 다시 작성하는 경우와 같습니다. 분할된 데이터의 경우 한 번에 하나의 파티션에 대해 압축을 사용하거나 사용하지 않도록 설정하여 필요한 공간을 줄일 수 있습니다.

• 분할된 테이블에서 파티션의 압축 상태를 확인하려면 sys.partitions 카탈로그 뷰의 data_compression 열을 쿼리합니다.

• 인덱스를 압축하는 경우 리프 수준 페이지는 행 압축과 페이지 압축을 모두 사용하여 압축될 수 있습니다. 리프 수준이 아닌 페이지는 페이지 압축을 받지 않습니다.

• 큰 값 데이터 형식은 해당 크기 때문에 일반 행 데이터와 별도로 특수 용도 페이지에 저장되기도 합니다. 별도로 저장되는 데이터에는 데이터 압축을 사용할 수 없습니다.

• SQL Server 2005에서 vardecimal 저장소 형식을 구현한 테이블을 업그레이드해도 해당 설정은 유지됩니다. vardecimal 저장소 형식의 테이블에 행 압축을 적용할 수 있습니다. 그러나 행 압축이 vardecimal 저장소 형식의 상위 집합이므로 vardecimal 저장소 형식을 유지할 필요가 없습니다. vardecimal 저장소 형식과 행 압축을 함께 사용해도 10진수 값이 추가로 압축되지 않습니다. vardecimal 저장소 형식의 테이블에 페이지 압축을 적용할 수 있지만 vardecimal 저장소 형식 열이 추가로 압축되지는 않습니다.

  참고
  SQL Server 2008에서는 vardecimal 저장소 형식을 지원하지만 행 수준 압축으로 동일한 결과를 얻을 수 있으므로 vardecimal 저장소 형식은 더 이상 사용되지 않습니다. Microsoft SQL Server의 이후 버전에서는 이 기능이 제거됩니다. 새 개발 작업에서는 이 기능을 사용하지 않도록 하고, 현재 이 기능을 사용하는 응용 프로그램은 수정하십시오.

압축 구현

---

데이터 압축 구현에 대한 간략한 설명은 행 압축 구현, 페이지 압축 구현 및 유니코드 압축 구현을 참조하십시오.

압축 전후 크기 변경 사항 예상

---

압축 상태를 변경할 경우 테이블 또는 인덱스에 어떤 영향을 주는지 확인하려면 sp_estimate_data_compression_savings 저장 프로시저를 사용합니다. sp_estimate_data_compression_savings 저장 프로시저는 데이터 압축을 지원하는 SQL Server에서만 사용할 수 있습니다.

압축이 분할된 테이블 및 인덱스에 주는 영향

---

분할된 테이블 및 인덱스에서 데이터 압축을 사용할 때는 다음 사항을 고려해야 합니다.

• 범위 분할

  ALTER PARTITION 문을 사용하여 파티션을 분할할 경우 두 파티션이 모두 원래 파티션의 데이터 압축 특성을 상속합니다.

• 범위 병합

  두 파티션을 병합할 경우 결과 파티션이 대상 파티션의 데이터 압축 특성을 상속합니다.

• 파티션 전환

  파티션을 전환하려면 파티션의 데이터 압축 속성이 테이블의 압축 속성과 일치해야 합니다.

• 한 파티션 또는 모든 파티션 다시 작성

  분할된 테이블 또는 인덱스의 압축을 수정하는 데에는 다음과 같은 두 가지 구문 변형을 사용할 수 있습니다.

  • 다음 구문에서는 참조된 파티션만 다시 작성합니다.

    복사

    ALTER TABLE <table_name> 
    REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  <option>)
    

  • 다음 구문에서는 참조되지 않는 파티션의 기존 압축 설정을 사용하여 전체 테이블을 다시 작성합니다.

    복사

    ALTER TABLE <table_name> 
    REBUILD PARTITION = ALL 
    WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(<range>),
    ... )
    

  분할된 인덱스에서는 같은 원칙에 따라 ALTER INDEX를 사용합니다.

• 분할된 클러스터형 인덱스 삭제

  클러스터형 인덱스를 삭제한 경우 파티션 구성표를 수정하지 않으면 해당 힙 파티션에서 데이터 압축 설정이 유지됩니다. 파티션 구성표를 변경하는 경우 모든 파티션이 압축되지 않은 상태로 다시 작성됩니다. 클러스터형 인덱스를 삭제하고 파티션 구성표를 변경하려면 다음 단계를 수행해야 합니다.

  1. 클러스터형 인덱스를 삭제합니다.

  2. 압축 옵션을 지정하는 ALTER TABLE ... REBUILD ... 옵션을 사용하여 테이블을 수정합니다.

  클러스터형 인덱스를 OFFLINE으로 삭제하면 클러스터형 인덱스의 상위 수준만 제거되므로 작업이 상당히 빠르게 수행됩니다. 클러스터형 인덱스를 ONLINE으로 삭제하는 경우 SQL Server에서는 1단계와 2단계에서 한 번씩, 총 두 번에 걸쳐 힙을 다시 작성해야 합니다.

압축이 복제에 주는 영향

---

데이터 압축과 복제를 함께 사용할 때는 다음 사항을 고려해야 합니다.

• 스냅숏 에이전트에서 초기 스키마 스크립트를 생성할 때 새 스키마는 테이블과 해당 인덱스 모두에 대해 동일한 압축 설정을 사용합니다. 압축을 인덱스에 사용하지 않고 테이블에만 사용할 수는 없습니다.

• 트랜잭션 복제의 경우 아티클 스키마 옵션에 따라 스크립팅할 종속 개체 및 속성이 결정됩니다. 자세한 내용은 sp_addarticle을 참조하십시오.

  배포 에이전트에서는 스크립트를 적용할 때 하위 구독자를 확인하지 않습니다. 압축 복제를 선택하는 경우 하위 구독자에서 테이블을 만들 수 없습니다. 혼합 토폴로지의 경우 압축 복제를 사용하지 마십시오.

• 병합 복제의 경우 게시 호환성 수준이 스키마 옵션을 재정의하며 스크립팅될 스키마 개체를 결정합니다. 호환성 수준에 대한 자세한 내용은 복제 토폴로지에 SQL Server의 여러 버전 사용을 참조하십시오.

  혼합 토폴로지의 경우 새 압축 옵션을 지원할 필요가 없으면 게시 호환성 수준을 하위 구독자 버전으로 설정해야 합니다. 새 압축 옵션이 필요하면 테이블을 만든 후 구독자에서 테이블을 압축합니다.

다음 표에서는 복제하는 동안 압축을 제어하는 복제 설정을 보여 줍니다.

사용자 의도	테이블 또는 인덱스에 대한 파티션 구성표 복제	압축 설정 복제	스크립팅 동작
파티션 구성표를 복제하고 파티션의 구독자에서 압축 사용	True	True	파티션 구성표와 압축 설정을 모두 스크립팅합니다.
파티션 구성표를 복제하지만 구독자에서 데이터를 압축하지 않음	True	False	파티션 구성표를 스크립팅하지만 파티션의 압축 설정은 스크립팅하지 않습니다.
파티션 구성표를 복제하지 않고 구독자에서 데이터를 압축하지 않음	False	False	파티션이나 압축 설정을 스크립팅하지 않습니다.
게시자에서 모든 파티션이 압축된 경우 구독자에서 테이블을 압축하지만 파티션 구성표를 복제하지 않음	False	True	모든 파티션에서 압축을 사용할 수 있는지 확인합니다. 테이블 수준에서 압축을 스크립팅합니다.

압축이 다른 SQL Server 구성 요소에 주는 영향

---

압축은 저장소 엔진에서 발생하므로 데이터는 SQL Server의 다른 구성 요소 대부분에 압축되지 않은 상태로 제공됩니다. 따라서 압축이 다른 구성 요소에 주는 영향은 다음으로 제한됩니다.

• 대량 가져오기 및 내보내기 작업

  데이터를 내보낼 때는 네이티브 형식으로 내보내더라도 데이터가 압축되지 않는 행 형식으로 출력됩니다. 따라서 내보낸 데이터 파일의 크기가 원본 데이터보다 훨씬 커질 수 있습니다.

  데이터를 가져올 때는 대상 테이블이 압축을 사용하도록 설정된 경우 저장소 엔진에서 데이터를 압축된 행 형식으로 변환합니다. 이로 인해 데이터를 압축되지 않은 테이블로 가져올 때보다 CPU 사용량이 증가할 수 있습니다.

  페이지 압축을 사용하여 힙으로 데이터를 대량으로 가져오는 경우 대량 가져오기 작업에서는 데이터를 삽입할 때 페이지 압축을 사용하여 데이터를 압축하려고 합니다.

• 백업 및 복원에는 압축이 영향을 주지 않습니다.

• 로그 전달에는 압축이 영향을 주지 않습니다.

• 데이터는 서로 다른 페이지 수 및 페이지당 행 수를 사용하여 저장되므로 압축을 사용하도록 설정하면 쿼리 계획이 변경될 수도 있습니다.

• 데이터 압축은 SQL Server Management Studio에서 데이터 압축 마법사를 통해 지원됩니다.

데이터 압축 마법사를 시작하려면

• 개체 탐색기에서 테이블, 인덱스 또는 인덱싱된 뷰를 마우스 오른쪽 단추로 클릭하고 저장소를 가리킨 다음 압축을 클릭합니다.

압축 모니터링

---

SQL Server의 전체 인스턴스에 대해 압축을 모니터링하려면 SQL Server, Access Methods 개체의 Page compression attempts/sec 및 Pages compressed/sec 카운터를 사용합니다.

개별 파티션에 대한 페이지 압축 통계를 얻으려면 sys.dm_db_index_operational_stats 동적 관리 함수를 쿼리합니다.

예

---

다음 예 중 일부는 분할된 테이블을 사용하며 파일 그룹이 있는 데이터베이스를 필요로 합니다. 파일 그룹이 있는 데이터베이스를 만들려면 다음 문을 실행합니다.

복사

CREATE DATABASE TestDatabase
ON  PRIMARY
( NAME = TestDatabase,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\TestDB.mdf'),
FILEGROUP test1fg
( NAME = TestDBFile1,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\TestDBFile1.mdf'),
FILEGROUP test2fg
( NAME = TestDBFile2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\TestDBFile2.ndf'),
FILEGROUP test3fg
( NAME = TestDBFile3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\TestDBFile3.ndf'),
FILEGROUP test4fg
( NAME = TestDBFile4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\TestDBFile4.ndf') ;
GO

새 데이터베이스로 전환하려면

복사

USE TestDatabase
GO

1. 행 압축을 사용하는 테이블 만들기

다음 예에서는 테이블을 만들고 압축을 ROW로 설정합니다.

복사

CREATE TABLE T1 
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = ROW);
GO

2. 페이지 압축을 사용하는 테이블 만들기

다음 예에서는 테이블을 만들고 압축을 PAGE로 설정합니다.

복사

CREATE TABLE T2 
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = PAGE);
GO

3. 분할된 테이블에서 DATA_COMPRESSION 옵션 설정

다음 예에서는 이 섹션의 앞부분에 나오는 코드를 사용하여 만든 TestDatabase 테이블을 사용합니다. 이 예에서는 파티션 함수와 구성표를 만든 다음 분할된 테이블을 만들고 테이블의 파티션에 대해 압축 옵션을 지정합니다. 이 예에서 파티션 1은 ROW 압축을 사용하도록 구성되고 나머지 파티션은 PAGE 압축을 사용하도록 구성됩니다.

파티션 함수를 만들려면

복사

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO

파티션 구성표를 만들려면

복사

CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg) ;
GO

압축된 파티션을 포함하는 분할된 테이블을 만들려면

복사

CREATE TABLE PartitionTable1 
(col1 int, col2 varchar(max))
ON myRangePS1 (col1) 
WITH 
(
  DATA_COMPRESSION = ROW ON PARTITIONS (1),
  DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4)
);
GO

4. 분할된 테이블에서 DATA_COMPRESSION 옵션 설정

다음 예에서는 예 3에서 사용한 데이터베이스를 사용합니다. 이 예에서는 인접하지 않은 파티션에 대한 구문을 사용하여 테이블을 만듭니다.

복사

CREATE TABLE PartitionTable2 
(col1 int, col2 varchar(max))
ON myRangePS1 (col1) 
WITH 
(
  DATA_COMPRESSION = ROW ON PARTITIONS (1,3),
  DATA_COMPRESSION = NONE ON PARTITIONS (2,4)
);
GO

5. 테이블을 수정하여 압축 변경

다음 예에서는 예 1에서 만든 분할되지 않은 테이블에 대한 압축을 변경합니다.

복사

ALTER TABLE T1 
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO

6. 분할된 테이블에서 하나의 파티션에 대한 압축 수정

다음 예에서는 예 3에서 만든 분할된 테이블에 대한 압축을 변경합니다. REBUILD PARTITION = 1 구문을 사용하면 파티션 번호 1만 다시 작성됩니다.

복사

ALTER TABLE PartitionTable1 
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE) ;
GO

동일한 작업에서 다음과 같은 대체 구문을 사용하면 테이블의 모든 파티션이 다시 작성됩니다.

복사

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO

7. 분할된 테이블에서 여러 파티션에 대한 압축 수정

REBUILD PARTITION = ... 구문은 하나의 파티션만 다시 작성할 수 있습니다. 두 개 이상의 파티션을 다시 작성하려면 여러 문을 실행하거나 다음 예를 실행하여 지정되지 않은 파티션에 대한 현재 압축 설정으로 모든 파티션을 다시 작성해야 합니다.

복사

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1), 
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4) 
) ;
GO

8. 인덱스에서 압축 수정

다음 예에서는 예 1에서 만든 테이블을 사용하고 C2 열에 인덱스를 만듭니다.

복사

CREATE NONCLUSTERED INDEX IX_INDEX_1 
    ON T1 (C2) 
WITH ( DATA_COMPRESSION = ROW ) ; 
GO

다음 코드를 실행하여 인덱스를 페이지 압축으로 변경합니다.

복사

ALTER INDEX IX_INDEX_1 
ON T1
REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;
GO

9. 분할된 인덱스에서 단일 파티션에 대한 압축 수정

다음 예에서는 인덱스의 모든 파티션에서 행 압축을 사용하는 분할된 테이블에서 인덱스를 만듭니다.

복사

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO

파티션마다 다른 압축 설정을 사용하도록 인덱스를 만들려면 ON PARTITIONS 구문을 사용합니다. 다음 예에서는 인덱스의 파티션 1에서는 행 압축을 사용하고 인덱스의 파티션 2에서 4까지는 페이지 압축을 사용하는 분할된 테이블에서 인덱스를 만듭니다.

복사

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = ROW ON PARTITIONS(1),
    DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4 ) ) ;
GO

다음 예에서는 분할된 인덱스의 압축을 변경합니다.

복사

ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL 
WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO

10. 분할된 인덱스에서 여러 파티션에 대한 압축 수정

REBUILD PARTITION = ... 구문은 하나의 파티션만 다시 작성할 수 있습니다. 두 개 이상의 파티션을 다시 작성하려면 여러 문을 실행하거나 다음 예를 실행하여 지정되지 않은 파티션에 대한 현재 압축 설정으로 모든 파티션을 다시 작성해야 합니다.

복사

ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL 
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1), 
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4) 
) ;
GO

참고 항목

---

참조

sys.partitions(Transact-SQL)

CREATE PARTITION SCHEME(Transact-SQL)

CREATE PARTITION FUNCTION(Transact-SQL)

CREATE TABLE(Transact-SQL)

ALTER TABLE(Transact-SQL)

CREATE INDEX(Transact-SQL)

ALTER INDEX(Transact-SQL)

개념

유니코드 압축 구현