보고있는 책에서 SQL 2008에서는 복구모델 Full인 경우에 TABLOCK을 주고 insert하면 로깅 최적화가 된다고 이야기를 하고 있어서 한번 테스트를 해 보았습니다.
-- 테스트 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
