데이터 압축 상태에 대한 개체 크기 예상

데이터 압축 상태에 대한 개체 크기 예상

 

• Version : SQL Server 2008, 2008R2, 2012

 

SQL Server 2008 이상부터 데이터 압축에 대한 기능이 추가 되었다. 데이터베이스의 전체 테이블, 일부 테이블을 압축 할 수 있게 되었다. 압축 상태에 대한 개체 크기를 예상하는 방법으로 sp_estimate_date_compression_savings 프로시저를 통해 알아 보자.

 

sp_estimate_date_compression_savings 프로시저를 이용하면 전체 테이블 및 일부 테이블에 대해 압축 전후의 크기를 계산 할 수 있다. 여기에는 힙, 클러스터형 인덱스, 비클러스터형 인덱스, 인덱싱된 뷰 및 테이블인덱스 파티션이 포함 된다.

개체는 행 압축 또는 페이지 압축을 사용하여 압축 할 수 있다. 테이블, 인덱스, 파티션이 이미 압축된 경우 다시 압축되는 크기를 예상할 수 있다.

 

sp_estimate_data_compression_savings [ @schema_name = ] 'schema_name' , [ @object_name = ] 'object_name' , [@index_id = ] index_id , [@partition_number = ] partition_number , [@data_compression = ] 'data_compression' [;]

 

• @schma_name : 테이블 또는 인덱싱된 뷰를 포함하는 데이터베이스 스키마 이름. NULL 이면 기본 스키마 사용
• @object_name : 인덱스가 있는 테이블 또는 인덱싱된 뷰의 이름
• @index_id : 인덱스 ID, NULL 또는 0(힙인 경우)값 중 하나일 수 있다. 기본 테이블 또는 뷰에 대한 모든 인덱스 정보를 반환하려면 NULL를 지정 한다. NULL을 지정하는 경우 @partition_number에도 NULL을 지정해야 한다.
• @partition_number : 개체의 파티션 번호. NULL 또는 1 값 중 하나일 수 있다.
• @data_compression : 계산할 압축 유형. NONE, ROW, PAGE 값 중 하나일 수 있다.

 

 

sp_estimate_date_compression_savings 저장 프로시저는 요청된 압축 설정을 사용할 경우 개체 크기를 예상하기 위해 원본 개체를 샘플링하고 이 데이터를 tempdb에 생성된 해당 테이블 및 인덱스에 로드 한다. 그런 다음 tempdb에 생성된 테이블 또는 인덱스가 요청된 설정으로 압축되고 예상된 압축 전후 크기 변경 사항이 계산된다.

테이블, 인덱스, 파티션 압축 상태를 변경하려면 ALTER TABLE, ALTER INDEX 문을 사용한다.

 

USE AdventureWorks2008R2; GO EXEC sp_estimate_data_compression_savings 'Production', 'WorkOrderRouting', NULL, NULL, 'ROW' ; GO   EXEC sp_estimate_data_compression_savings 'Production', 'WorkOrderRouting', NULL, NULL, 'PAGE' ; GO   EXEC sp_estimate_data_compression_savings 'Production', 'WorkOrderRouting', NULL, NULL, 'NONE' ; GO

 

 

열 이름	데이터 형식	설명
Object_name	Sysname	테이블 또는 인덱싱된 뷰의 이름
Schema_name	Sysname	테이블 또는 인덱싱된 뷰의 스키마
Index_id	Int	인덱스 ID 0 = 힙 1 = 클러스터형 인덱스 > 1 = 비클러스터형 인덱스
Partition_number	Int	파티션 번호. 분할 되지 않은 경우 1반환
size_with_current_compression_setting (KB)	Bigint	요청된 테이블, 인덱스 또는 현재 파티션 크기
size_with_requested_compression_setting (KB)	Bigint	요청된 압축 설정을 사용하는 테이블, 인덱스 또는 파티션의 예상 크기이며 해당되는 경우 조각화가 없는 것으로 가정하고 기존 채우기 비율이 사용
sample_size_with_current_compression_setting (KB)	Bigint	현재 압축 설정을 사용하는 샘플의 크기. 조각화가 포함
sample_size_with_requested_compression_setting (KB)	bigint	요청된 압축 설정을 사용하여 만든 샘플의 크기. 해당되는 경우 조각화가 없는 것으로 가정하고 기존 채우기 비율을 사용

 

 

기존 데이터가 조각화된 경우는 인덱스를 다시 작성하면 압축을 사용하지 않아도 크기를 줄일 수 있다. 인덱스를 다시 작성하는 동안은 인덱스 채우기(Fill Facter) 비율이 적용 된다. 이때 인덱스 사이즈가 커질 수도 있다.

 

• 채우기(Fill Facter) 관련 아티클 : http://sqlmvp.kr/140184752222

 

예를 들어 평균 행 크기가 40%줄어드는 대신 개체 크기를 40% 줄일 수 있다. 공간 크기는 채우기 비율과 행 크기에 따라 달라지므로 공간이 절약 되지 않을 수도 있다. 예를 들어 8000바이트 길이의 행이 있고 행 크기를 40% 줄인 경우에도 여전히 데이터 페이지 하나에 행 하나만 넣을 수 있다. 이 경우 공간이 절약되지 않는다.

 

sp_estimate_data_compression_savings 실행결과에서 모든 테이블이 확장됨을 나타내는 경우 테이블의 많은 행이 데이터 형식의 전체 자릿수를 거의 모두 사용하며 압축작업 필요한 오버헤드가 압축으로 얻을 수 있는 장점보다 큰 경우 사용하지 않는 것이 좋다.

 

압축 작업은 테이블이 커밋된 읽기 격리 수준에서 진행 된다. 작업시 테이블에 대한 잠금은 IS 잠금을 획득할 수 있다. IS 잠금을 획득할 수 없는 경우는 프로시저가 차단이 된다. 테이블에 압축을 사용하도록 설정한 경우 sp_estimate_data_compression_savings를 사용하면 테이블을 압축하지 않을 경우의 평균 행 크기를 예상할 수 있다.

 

요청된 압축 설정이 현재 압축 설정과 동일한 경우 저장 프로시저는 기존 채우기 비율을 사용하여 데이터 조각화가 없을 경우의 예상 크기를 반환 한다. 인덱스 또는 파티션 ID가 없으면 결과가 반환되지 않는다.

 

 

[참고 자료]

sp_estimate_date_compression_savings :

http://msdn.microsoft.com/ko-kr/library/cc280574(v=sql.105).aspx

 

 

2013-05-24 / 강성욱 / http://sqlmvp.kr