SQL 에 대해서 자신감을 가지고 있던 나에게 난 아무것도
모르는 하수 였구나라고 느끼게 만들어 주신분 들중 한분
강사님 잘 계시죠..^^
김정선의 좋은 글을 찾아서……
Minimal Logging changes in SQL Server 2008
김정선(jskim@sqlroad.com) ㈜씨퀄로(전 필라넷 DB사업부), 수석컨설턴트 SQLServer 아카데미 강사
Microsoft SQL Server MVP
저자: Sunil Agarwal, SQL Server Storage Engine
김정선의 의견
오랜 만에 "좋을 글을 찾아서"입니다^^
이번 글 역시 아주 흥미로운 내용입니다! SQL Server Transaction Logging 아키텍처에 관련된 아주 작지만 유용한 내용이 포함되어 있습니다.
대량 데이터 Insert 작업에 성능 부분은 관리자 혹은 사용자에게 있어서 늘 큰 이슈가 됩니다. Insert와 같은 트랜잭션 작업 성능에 영향을 미치는 부분이 바로 Logging 데이터의 크기인데요, SQL Server 2008은 이러한 작업에 Logging을 최소화하는데 더욱 노력을 기울인 듯 합니다.
이번 글을 통해서 Insert 작업에 Logging이 어떻게 발생하는지, 특히 TABLOCK 힌트를 사용한 경우에 Logging이 어떻게 달라지는지, 또한 Transaction Log를 확인하는데 사용하는 쿼리는 어떤 것이면 그 내용은 무엇을 담고 있는지 분석하는데 작지만 유용한 내용들이 포함되어 있습니다.
원문을 그대로 해석해서 올리려 했으나, 직접 테스트를 해 보니 예제 따라 하기 및 설명이 너무 생략되어 불편하실 것 같아, 제 나름대로 바꾸어 작성했습니다.
테스트하고 이해하시는데 도움이 되셨으면 하는 바램입니다.
늘 그렇듯, 제 마음대로 번역/수정/추가한 것이니 부족한 번역에도 양해 바랍니다 ^^
본문 시작
최소 로깅을 위한 기반 지식들에 대해서는 이전 글을 참조하세요. SQL Server 2008을 시작으로 최소 로깅이 향상되었습니다. 이러한 향상은 TSQL Insert에서도 가능합니다. 중요 고객 시나리오 중의 하나는 Staging 테이블에서 Target 테이블로 데이터를 전송하는 것입니다. 최소 로깅을 원한 경우에 고객의 선택은 SELECT * INTO <target> FROM <staging-table> 이었습니다. 그러나 이 방법은 대상 테이블 측면에서 DDL에 대한 제어권이 없다는 제한이 있었습니다. 이제 새로운 향상 능력으로 이러한 제한이 없어집니다.
우선 로그 레코드의 내용을 확인하기 위해 다음과 같은 쿼리를 사용할 것이며, 아래 code-2, -3에서 사용됩니다.
-- code-1)
SELECT operation,context, [log record fixed length], [log record length]
, AllocUnitId, AllocUnitName, [Lock Information]
FROM fn_dblog(null, null)
WHERE allocunitname='dbo.t_heap'
ORDER BY [Log Record Length] DESC
예제 준비
혹시 AdventureWorks DB의 복구 모델이 “단순(simple)”으로 되어 있으면 전체(full) 모델로 바꾸어서 작업해 보세요.
USE AdventureWorks
GO
-- source 테이블 생성
CREATE TABLE dbo.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 dbo.t_source VALUES (@i, @i+10000, 'indexkey', 'hello')
SELECT @i = @i + 1
END
GO
-- target Heap 테이블 생성
CREATE TABLE dbo.t_heap (
c1 int
, c2 int
, c3 char(100)
, c4 char(1000)
)
GO
시나리오-1
우선 Insert 작업에 TABLOCK 힌트를 사용하지 않은 경우에, Log 레코드가 어떻게 발생하고 기록되는지를 확인해 보고 이를 이후 TABLOCK 힌트를 사용한 시나리오가 비교를 해 봅니다.
BEGIN TRAN
-- code-3)
-- 아래가 완전 로깅의 경우
INSERT INTO t_heap
SELECT * FROM t_source
-- 위의 code-1) Log 레코드 검색 쿼리를 사용한 결과
-- 999건의 INSERT ROWS 레코드가 발생한 것을 볼 수 있다.
ROLLBACK
GO
-- 이전 Log 레코드 데이터를 제거하기 위해
CHECKPOINT
GO
-- 정리
DROP TABLE t_heap, t_source
GO
시나리오-2
HEAP에 대한 Insert에서는 TABLOCK 힌트로 작업할 때 최소 로깅이 됩니다. 이것은 고객이 계속 문의해 오던 기능 중의 하나입니다. 그런데, 한 가지 단점은 Bulk Insert가 BU 잠금을 요구하는 반면에 상대적으로 이것은 X 잠금을 요구한다는 것입니다. 그로 인해 다중 스레드(Thread)를 사용한 입력이 어렵게 됩니다.
BEGIN TRAN
-- code-2)
-- 최소 로깅이 된다
-- LOCK: 테이블에 X 잠금
-- 이는't_heap'이 비어 있지 않은 경우에도 동일합니다.
INSERT INTO dbo.t_heap WITH (TABLOCK)
SELECT * FROM dbo.t_source
-- 위의 code-1) Log 레코드 검색 쿼리를 사용한 결과
-- 999건의 INSERT ROWS 레코드가 없다
ROLLBACK
GO
-- 정리
DROP TABLE t_heap, t_source
GO
정리
SQL Server 2008에서 대량 Insert에 대한 작업 편이성 및 최소 Logging으로 인한 성능 향상을 알 수 있는 부분입니다.
위의 각 시나리오에서 sp_lock을 수행해 보면, 잠금이 어떻게 발생하는지를 알 수 있고 이를 통해 내부 동작을 이해하는데 도움을 얻을 수 있겠습니다.
어떠세요? 재미있죠?
원문 블로그에 가시면 2편, 3편이 올라와 있습니다. 관심 있는 분들은 해당 내용을 살펴보시면 더 흥미로울 겁니다.
감사합니다.
