[MS SQL Server New Feature 1] 최소 로깅 Insert

본  포스트에서는 MSDN Blog에 MS Server 2008 부터 도입된 신기능에 대한 유용한 정보가 있어 소개하고자 한다.

 

원문 블로그:

 http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/06/minimal-logging-changes-in-sql-server-2008-part-1.aspx

작성자: Sunil Agarwal

 

RDMBS에서는 장애 발생시 혹은 특점 시점으로 롤백 또는 롤포워드를 하기 위해 데이터 변경 작업 (일반적으로 DML)에 대해 로그를 기록한다. 트랜잭션 작업 성능에 영향을 미치는 것이 바로 이러한 로깅인데, 특히 배치 및 ETL 등과 같은 대량 데이터의 변경작업시 로그파일에 많은 IO를 발생시켜 성능상의 이슈가 되고 있다.

 

이에 MS SQL Server에서는 로깅을 최소화하는 방법으로 데이터베이스 복구 모델의 형태로 Simple 및 Bluk Logged 복구 모델을, 그리고 SSIS(DTS), BCP, SELECT INTO.., BULK INSERT와 같은 툴과 구문을 제공하고 있다.

 

MS SQL Server 2008부터는 위에서 언급한 방법이외에 INSERT 구문에서도 로그를 최소화할 수 있는 기능이 추가되었는데 제법 유용하게 사용될 것이라고 생각된다.

 

아래는 이 기능에 대해 원문 블로그에 있는 내용을 발췌하여 재정리한 것으로 참고하길 바란다.

 

1. 테스트를 위해 테스트하고자 하는 DB의 복구 모델을 "전체(Full)"로 바꾸도록 하자.

 

2. 소스 테이블 생성

-- create the source table create table t_source (c1 int, c2 int, c3 char (100), c4 char(1000)) go   declare @i int select @i = 1 while (@i < 1000) begin             insert into t_source values (@i, @i+10000, 'indexkey', 'hello')             select @i= @i + 1 end

   

3. 타겟 힙 테이블 생성

-- create the target heap create table t_heap (c1 int, c2 int, c3 char(100), c4 char(1000)) go

   

4. INSERT 시 TABLOCK 힌트를 줘서 최소 로깅한 경우

-- this is minimally logged. -- LOCK: X lock on the table -- this behavior is same even when ‘t_heap’ is not empty begin tran insert into t_heap with (TABLOCK) select * from t_source

 

4의 경우 로그 정보 확인

select top 10 operation,context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName from fn_dblog(null, null) where allocunitname='dbo.t_heap' order by [Log Record Length] Desc

 

-- here are the top-10 log records. You can see that it is minimally logged

 

5. INSERT 시 TABLOCK 힌트를 주지 않고 전체 로깅한 경우

-- this is fully logged insert into t_heap select * from t_source

 

5의 경우 로그 정보 확인

select top 10 operation,context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName from fn_dblog(null, null) where allocunitname='dbo.t_heap' order by [Log Record Length] Desc

 

-- here are the top 10 log records. You can see that it is fully logged