MS-SQL Server

SQL Server 2008 새로운 기능 – INSERT 의 최소 로깅

송혁 - HyokSong 2008. 3. 9. 23:37

SQL은 반듯이 데이터 변경에 따른 기록을 우선 로그 파일에 기록을 하게 됩니다.(Write Ahead Logging)
그리고 이러한 로그는 트랜잭션 롤백 또는 롤 포워드시에 사용하게 됩니다.

이러한 로깅으로 인해서 많은 데이터 변경 작업시 로그파일에 많은 IO가 발생하여, 전체적인 성능에 영향을 줄 수 있습니다.
그렇다면 굳이 로깅을 하지 않아도 되는 새로운 테이블에 데이터 추가 등에 대해서 최소로깅으로 처리한다면 보다 좋은 성능을 가질 수 있을것입니다.

최소 로깅이란 굳이 모든 데이터에 대해서 로깅할 필요가 없는 작업에 대해서 모든 데이터에 대해서 로깅을 하는 것이 아닌 할당 정보등에 대해서만 로깅을 하는 것입니다.
최소 로깅은 SIMPLE, BULK LOGGED 복구 모델에서만 할 수 있습니다.
보다 자세한 내용은 온라인 설명서로~

이러한 최소 로깅은 OLTP의 배치성 작업 또는 DW의 ETL에서 사용하는 것이 보통입니다. 최소 로깅을 사용하기 위해서는 SELECT INTO 또는 DTS, SSIS, BCP, BULK INSERT를 활용할 수 밖에 없었습니다.

하지만 SQL Server 2008에서는 INSERT구문에 대해서도 최소 로깅을 지원합니다.
로깅을 하지 않기 위해서는 row단위의 로깅 데이터가 없어도 롤백을 할 수 있어야 하기에 어쩔수 없이 여러가지 제약 사항은 있습니다.

  1. TABLOCK힌트 반듯이 추가
  2. 클러스터 인덱스만 존재하고, 비어있는 테이블 또는 어떠한 인덱스도 없는, 비어있지 않은 테이블

송 혁, SQL Server MVP
sqler.pe.kr
nexondbteam.tistory.com

USE MASTER

GO

DROP DATABASE TEST1

GO

CREATE DATABASE TEST1

GO

ALTER DATABASE TEST1 SET RECOVERY BULK_LOGGED

 

USE TEST1

GO

--========================================================[클러스터인덱스만있는빈테이블]

CREATE TABLE tbl9(a int not null, b char(4000))

CREATE UNIQUE CLUSTERED INDEX cl_tbl9 on tbl9 ( a )

 

DECLARE @CurrentLSN varchar(23)

SELECT @CurrentLSN = [Current LSN] FROM fn_dblog(null,null) ORDER BY [Current LSN] DESC

 

INSERT INTO tbl9

select  TOP 10000

       row_number() over(order by a.id) as col1

       , 'Hyoksong' as col2

FROM sys.sysindexes A , sys.sysindexes B

 

SELECT SUM([Log Record Length]) FROM fn_dblog(null,null)

WHERE[Current LSN] > @CurrentLSN

--=SUM([Log Record Length]) = 44261980

 

/*

 -- TABLOCK hint

*/

 

CREATE TABLE tbl10(a int not null, b char(4000))

CREATE UNIQUE CLUSTERED INDEX cl_tbl10 on tbl10 ( a )

 

DECLARE @CurrentLSN varchar(23)

SELECT @CurrentLSN = [Current LSN] FROM fn_dblog(null,null) ORDER BY [Current LSN] DESC

 

INSERT INTO tbl10 WITH(TABLOCK)

select  TOP 10000

       row_number() over(order by a.id) as col1

       , 'Hyoksong' as col2

FROM sys.sysindexes A , sys.sysindexes B

 

SELECT SUM([Log Record Length]) FROM fn_dblog(null,null)

WHERE[Current LSN] > @CurrentLSN

--=SUM([Log Record Length]) = 386876

 

--========================================================[힙이며인덱스가없는비어있지않은테이블]

CREATE TABLE tbl11(a int not null, b char(4000))

 

INSERT INTO tbl11

select  TOP 100

       row_number() over(order by a.id) as col1

       , 'Hyoksong' as col2

FROM sys.sysindexes A , sys.sysindexes B

GO

 

DECLARE @CurrentLSN varchar(23)

SELECT @CurrentLSN = [Current LSN] FROM fn_dblog(null,null) ORDER BY [Current LSN] DESC

 

INSERT INTO tbl11

select  TOP 10000

       row_number() over(order by a.id) as col1

       , 'Hyoksong' as col2

FROM sys.sysindexes A , sys.sysindexes B

 

SELECT SUM([Log Record Length]) FROM fn_dblog(null,null)

WHERE[Current LSN]>@CurrentLSN

--=SUM([Log Record Length]) = 87101448

 

/*

 -- TABLOCK hint

*/

CREATE TABLE tbl12(a int not null, b char(4000))

 

INSERT INTO tbl12

select  TOP 100

       row_number() over(order by a.id) as col1

       , 'Hyoksong' as col2

FROM sys.sysindexes A , sys.sysindexes B

GO

 

DECLARE @CurrentLSN varchar(23)

SELECT @CurrentLSN = [Current LSN] FROM fn_dblog(null,null) ORDER BY [Current LSN] DESC

 

INSERT INTO tbl12 WITH(TABLOCK)

select  TOP 10000

       row_number() over(order by a.id) as col1

       , 'Hyoksong' as col2

FROM sys.sysindexes A , sys.sysindexes B

 

SELECT SUM([Log Record Length]) FROM fn_dblog(null,null)

WHERE[Current LSN]>@CurrentLSN

--=SUM([Log Record Length]) = 929276