데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

  • 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) 비율이 적용 된다. 이때 인덱스 사이즈가 커질 수도 있다.

 

 

예를 들어 평균 행 크기가 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

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

 

No. Subject Author Date Views
1790 메모리 관리 아키텍처 – 버퍼 관리_페이지 읽기 jevida(강성욱) 2016.09.28 1250
1789 메모리 관리 아키텍처 – 버퍼 관리 jevida(강성욱) 2016.09.28 1808
1788 메모리 관리 아키텍처 – Min/Max Server Memory 효과 jevida(강성욱) 2016.09.28 2549
1787 메모리 관리 아키텍처 – 동적 메모리 관리 jevida(강성욱) 2016.09.28 1364
1786 메모리 관리 아키텍처 – 프로세스 주소 공간 jevida(강성욱) 2016.09.28 1363
1785 메모리 관리 아키텍처 – 메모리 아키텍처 jevida(강성욱) 2016.09.28 1867
» 데이터 압축 상태에 대한 개체 크기 예상 jevida(강성욱) 2016.09.28 1365
1783 sp_MSforeachdb, sp_MSforeachtable 프로시저 활용하기 jevida(강성욱) 2016.09.28 2861
1782 SQL Server 쿼리 처리 아키텍처_분산 쿼리 아키텍처 jevida(강성욱) 2016.09.28 1145
1781 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 인덱스 작업 jevida(강성욱) 2016.09.28 1338
1780 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 처리 수준 jevida(강성욱) 2016.09.28 1846
1779 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 jevida(강성욱) 2016.09.28 1781
1778 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - Preparing SQL Statements jevida(강성욱) 2016.09.28 1008
1777 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 강제 매개 변수화 jevida(강성욱) 2016.09.28 998
1776 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 단순 매개 변수화 jevida(강성욱) 2016.09.28 879
1775 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 매개 변수 및 실행 계획 재사용 jevida(강성욱) 2016.09.28 1096
1774 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 jevida(강성욱) 2016.09.28 1434
1773 DMV를 이용한 캐시된 저장 프로시저 통계 정보 확인 jevida(강성욱) 2016.09.28 979
1772 SQL Server 쿼리 처리 아키텍처_저장 프로시저 및 트리거 실행 jevida(강성욱) 2016.09.27 941
1771 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (4/4) – 분산형 분할 뷰(View) 확인 jevida(강성욱) 2016.09.27 1337





XE Login