-- 테스트 DB 생성

CREATE DATABASE DBFull

CREATE DATABASE DBFull_TABLock

GO

ALTER DATABASE DBFull SET RECOVERY FULL

ALTER DATABASE DBFull_TABLock SET RECOVERY FULL

GO

-- Full backup

BACKUP DATABASE DBFull to disk = 'd:\temp\DBFull.BAK' with init

BACKUP DATABASE DBFull_TABLock to disk = 'd:\temp\DBFull_TABLock.BAK' with init

go

-- 테스트 Table 생성

USE DBFull

GO

create table t1 (col1 bigint)

USE DBFull_TABLock

GO

create table t1 (col1 bigint)

-- log 파일 사이즈 확인

select name, size

from master..sysaltfiles

where name like '%log'

             and dbid >= db_id('DBFull') – 이건 방금 생성한 DB만 나오게 필터

order by name

name                                             size

--------------------------

DBFull_log                                     72

DBFull_TABLock_log         72

모두 72page(576KB) 입니다.

일단 테스트 데이터를 tempdb에 생성한 다음 DBFull DB로 복사해 보겠습니다.

-- 테스트 데이터 생성(100만건, 17MB)

select top 1000000

 row_number() over (order by (select 1)) as col1

into tempdb.dbo.t1

from sysindexes A

, sysindexes B

, sysindexes C

, sysindexes D

insert into DBFull.dbo.t1

select * from tempdb.dbo.t1

insert into DBFull_TABLock.dbo.t1 WITH(TABLOCK)

select * from tempdb.dbo.t1

-- log 파일 사이즈 확인

select name, size

from master..sysaltfiles

where name like '%log'

             and dbid >= db_id('DBFull') – 이건 방금 생성한 DB만 나오게 필터

order by name

name                                             size

--------------------------

DBFull_log                                     40840 -- 40840*8KB = 약320MB

DBFull_TABLock_log         2552   -- 2552*8KB = 약20MB

DBFull DB는 로그 사이즈가 320MB인데 DBFull_TABLock DB는 20MB정도 밖에 되지 않는다.

전체 복구 모델인데 이거참;;

도대체 어떻게 쌓는 차이가 있을까???

로그에 쌓인 모양을 한번 뒤져보자.

-- log 확인

-- 위에서 로그를 열라 많이 적었기 때문에 시간이 좀 걸린다.

-- 너무 오래 걸리면 위의 테스트의 건수를 좀 줄여서 하자.

use dbfull

go

select * from ::fn_dblog(null, null)

use dbfull_tablock

go

select * from ::fn_dblog(null, null)

@ DBFull DB

전체 복구 모델에 충실하게 1row씩 열심히 로그에 기록하고 있습니다.

@ DBFull DB with TABLOCK

전체 복구 모델이지만 PAGE단위로 할당하고 데이터를 집어 넣는 것을 로그에 쓰고 있습니다.

오호~ 페이지 단위로만 기록하니 로그 사이즈가 그렇게 작았구나~!

이친구 전체 복구모델인데 꽤 쓸만 하네?

 

-- 테스트 DB 정리

use master

go

drop database dbfull

drop database dbfull_tablock

 

전체 복구 모델 이더라도 TABLOCK 걸고 데이터를 넣으면 로깅을 최적화 해서 이전 버전에 비해 적은량의 로그를 쓰게 됩니다. 때문에 대량 데이터 작업을 할때는 전체 복구 모델이더라도 TABLOCK을 주어야 겠습니다.

  

아래는 보너스~ 인덱스 상황별 로그 사이즈~ ㅎㅎ

	DBFull	DBFull_TABLock
HEAP	320 MB	20 MB
Clustered index	220 MB	20 MB
Non-clustered index	510 MB	510 MB
Clustered index + Non-clustered index	430 MB	380 MB

 

 

PS : 최소로깅이라는 단어와는 맞지 않아 약간 용어를 수정 했습니다.

나중에 좀 더 고민을 해보고 비슷한 내용으로 한번 더 아티클을 작성해 보도록 하겠습니다~ ^^

하만철 / Ha Man cheol