최소 로깅하기 위한 조건 테스트

 좀 더 빠르게 데이터를 저장하기 위해 최소 로깅을 하는 것은 필수입니다. 데이터를 저장하는데 초점을 맞추기 때문에 로그를 쓰는 부분을 줄여 보자는 이야기죠~!

이러한 최소 로깅을 하기 위한 기본 조건들을 정리해 보았습니다.

 

1.       복구 모델
“전체 복구 모델”의 경우 모든 변경작업을 로깅 하므로 “대량 로그 복구 모델” 또는 “단순 복구 모델”이 필요합니다.

2.      이미 생성된 테이블에 데이터를 입력하는 경우 요구조건.

A.       테이블이 복제되고 있지 않아야 합니다.

B.        TABLOCK 잠금을 사용하여 데이터를 입력해야 합니다.

C.       데이터 입력시 로그에 익스텐트 할당에 대한 기록을 합니다.

 

         3.    인덱스 상황에 따른 최소 로깅 여부

	인덱스 페이지 로깅	데이터 페이지 로깅
힙 테이블	없음	최소
비어있는 테이블 + 넌클러스터드 인덱스	최소	최소
데이터가 있는 테이블 + 넌클러스터드 인덱스	전체	최소
비어있는 테이블 + 클러스터드 인덱스	최소	최소
데이터가 있는 테이블 + 클러스터드 인덱스	전체	전체

 

@@ 실제 테스트 

위 내용을 토대로 실제 테스트를 해 보았습니다.

500 MB , 3000만건 데이터 입력시 ldf 크기.

 

-- 테이블 생성

create table t1 (col1 bigint)

 

-- 각 경우에 따라 인덱스 생성

create index NC_t1 on t1 (col1)
create clustered index NC_t1 on t1 (col1)

 

-- 약 500MB 입력

insert into t1 with (tablock)
select top 30840470 ROW_NUMBER() over(order by (select 1))
from sysindexes a
 , sysindexes b
 , sysindexes c
 , sysindexes d

 

-- 각 사이즈 확인 및 초기화

dbcc showfilestats
dbcc sqlperf(logspace)
checkpoint
dbcc shrinkfile ('DB1_log', 1)

	Ldf 크기(MB)
힙 테이블	7.56
비어있는 테이블 + 넌클러스터드 인덱스	최소로깅이 안됨. -_- 뭐지??
데이터가 있는 테이블 + 넌클러스터드 인덱스	위처럼 최소로깅 안됨.
비어있는 테이블 + 클러스터드 인덱스	2.75 (하지만 tempdb 사용하며, tempdb 사이즈가 입력데이터만큼 커짐. 정렬하여 익스텐트 단위로 데이터를 넣어야 하기 때문으로 생각됨.)
데이터가 있는 테이블 + 클러스터드 인덱스	위처럼 tempdb 사용도 하고 ldf에 전체로그를 기록함.

 

결과적으로 힙 테이블 이외에는 BOL을 보고 생각했던것만큼 좋은 결과가 나오지는 않았습니다. 

힙 테이블이 아닌 이상 만족할만한 성능을 보이지는 않았기에 bcp, bulk insert등의 방식으로도 테스트 해 보았지만 동일한 결과가 나왔습니다.

다음 포스팅에서는 좀 더 여러가지 테스트를 해보기로 하겠습니다. ^_^

하만철 / Ha Man-cheol