데이터에 대한 이해와 spill in tempdb
상황은 tempdb 데이터가 30GB에서 120GB로 증가 하였지만 tempdb의 로그 파일은 1GB밖에 증가하지 않았다. 이런 상황이 가능 할까?
Tempdb에 대해 고려해야 할 때 tempdb에 로깅하는 것은 매우 효율적이다. tempdb는 다른 데이터베이스처럼 롤백 할 수 있도록 전과 후의 이미지를 필요로 한다. 그래서 업데이트 하기 전 성공적인 롤백이 가능하도록 이미지를 기록 한다.
위의 질문에 대한 답은 tempdb에 발생하는 정렬 유출(sort spill)을 고려하여 설명 할 수 있다. 다음 스크립트는 수 백만 행의 결과를 정렬한다.
SELECT S.*, P.* from Sales S JOIN Products P ON P.ProductID = S.ProductID ORDER BY P.Name; GO |
정렬작업이 메모리에서 tempdb로 spill 되는 것을 알 수 있다. tempdb에서 checkpoint 를 실행하고 쿼리를 실행한 후 tempdb의 트랜잭션 로그를 분석해 보자. 다음 스크립트를 실행하여 로그를 확인 할 수 있다.
SELECT [Current LSN], [Operation], [Context], [Transaction ID], [Log Record Length], [Description] FROM fn_dblog (null, null); GO |
Current LSN Operation Context Transaction ID Len Description
———————- ————— ——– ————– — ———————————————————-
000000c0:00000077:0001 LOP_BEGIN_XACT LCX_NULL 0000:00005e4d 120 sort_init;<snip>
000000c0:00000077:0002 LOP_BEGIN_XACT LCX_NULL 0000:00005e4e 132 FirstPage Alloc;<snip>
000000c0:00000077:0003 LOP_SET_BITS LCX_GAM 0000:00005e4e 60 Allocated 1 extent(s) starting at page 0001:0000aa48
000000c0:00000077:0004 LOP_MODIFY_ROW LCX_PFS 0000:00005e4e 88 Allocated 0001:0000aa48;Allocated 0001:0000aa49;
000000c0:00000077:0005 LOP_MODIFY_ROW LCX_PFS 0000:00005e4d 80 Allocated 0001:00000123
000000c0:00000077:0006 LOP_FORMAT_PAGE LCX_IAM 0000:00005e4d 84
000000c0:00000077:0007 LOP_SET_BITS LCX_IAM 0000:00005e4e 60
000000c0:00000077:0009 LOP_COMMIT_XACT LCX_NULL 0000:00005e4e 52
000000c0:00000077:000a LOP_BEGIN_XACT LCX_NULL 0000:00005e4f 128 soAllocExtents;<snip>
000000c0:00000077:000b LOP_SET_BITS LCX_GAM 0000:00005e4f 60 Allocated 1 extent(s) starting at page 0001:0000aa50
000000c0:00000077:000c LOP_MODIFY_ROW LCX_PFS 0000:00005e4f 88 Allocated 0001:0000aa50;Allocated 0001:0000aa51;<snip>
000000c0:00000077:000d LOP_SET_BITS LCX_IAM 0000:00005e4f 60
000000c0:00000077:000e LOP_SET_BITS LCX_GAM 0000:00005e4f 60 Allocated 1 extent(s) starting at page 0001:0000aa58
000000c0:00000077:000f LOP_MODIFY_ROW LCX_PFS 0000:00005e4f 88 Allocated 0001:0000aa58;Allocated 0001:0000aa59;<snip>
000000c0:00000077:0010 LOP_SET_BITS LCX_IAM 0000:00005e4f 60
000000c0:00000077:0011 LOP_SET_BITS LCX_GAM 0000:00005e4f 60 Allocated 1 extent(s) starting at page 0001:0000aa60
000000c0:00000077:0012 LOP_MODIFY_ROW LCX_PFS 0000:00005e4f 88 Allocated 0001:0000aa60;Allocated 0001:0000aa61;<snip>
000000c0:00000077:0013 LOP_SET_BITS LCX_IAM 0000:00005e4f 60
000000c0:00000077:0014 LOP_COMMIT_XACT LCX_NULL 0000:00005e4f 52
000000c0:00000077:0015 LOP_BEGIN_XACT LCX_NULL 0000:00005e50 128 soAllocExtents;<snip>
000000c0:00000077:0016 LOP_SET_BITS LCX_GAM 0000:00005e50 60 Allocated 1 extent(s) starting at page 0001:0000aa68
000000c0:00000077:0017 LOP_MODIFY_ROW LCX_PFS 0000:00005e50 88 Allocated 0001:0000aa68;Allocated 0001:0000aa69;<snip>
000000c0:00000077:0018 LOP_SET_BITS LCX_IAM 0000:00005e50 60
000000c0:00000077:0019 LOP_SET_BITS LCX_GAM 0000:00005e50 60 Allocated 1 extent(s) starting at page 0001:0000aa70
000000c0:00000077:001a LOP_MODIFY_ROW LCX_PFS 0000:00005e50 88 Allocated 0001:0000aa70;Allocated 0001:0000aa71;<snip>
000000c0:00000077:001b LOP_SET_BITS LCX_IAM 0000:00005e50 60
000000c0:00000077:001c LOP_SET_BITS LCX_GAM 0000:00005e50 60 Allocated 1 extent(s) starting at page 0001:0000aa78
000000c0:00000077:001d LOP_MODIFY_ROW LCX_PFS 0000:00005e50 88 Allocated 0001:0000aa78;Allocated 0001:0000aa79;<snip>
000000c0:00000077:001e LOP_SET_BITS LCX_IAM 0000:00005e50 60
000000c0:00000077:001f LOP_SET_BITS LCX_GAM 0000:00005e50 60 Allocated 1 extent(s) starting at page 0001:0000aa80
000000c0:00000077:0020 LOP_MODIFY_ROW LCX_PFS 0000:00005e50 88 Allocated 0001:0000aa80;Allocated 0001:0000aa81;<snip>
000000c0:00000077:0021 LOP_SET_BITS LCX_IAM 0000:00005e50 60
000000c0:00000077:0022 LOP_COMMIT_XACT LCX_NULL 0000:00005e50 52
000000c0:00000077:0023 LOP_BEGIN_XACT LCX_NULL 0000:00005e51 128 soAllocExtents;<snip> <snip> 000000cd:00000088:01d3 LOP_SET_BITS LCX_GAM 0000:000078fc 60 Deallocated 1 extent(s) starting at page 0001:00010e50
000000cd:00000088:01d4 LOP_COMMIT_XACT LCX_NULL 0000:000078fc 52
000000cd:00000088:01d5 LOP_BEGIN_XACT LCX_NULL 0000:000078fd 140 ExtentDeallocForSort;<snip>
000000cd:00000088:01d6 LOP_SET_BITS LCX_IAM 0000:000078fd 60
000000cd:00000088:01d7 LOP_MODIFY_ROW LCX_PFS 0000:000078fd 88 Deallocated 0001:00010e68;Deallocated 0001:00010e69;<snip>
000000cd:00000088:01d8 LOP_SET_BITS LCX_GAM 0000:000078fd 60 Deallocated 1 extent(s) starting at page 0001:00010e68
000000cd:00000088:01d9 LOP_COMMIT_XACT LCX_NULL 0000:000078fd 52
000000cd:00000088:01da LOP_MODIFY_ROW LCX_PFS 0000:00005fac 80 Deallocated 0001:00000109
000000cd:00000088:01db LOP_SET_BITS LCX_SGAM 0000:00005fac 60 ClearBit 0001:00000108
000000cd:00000088:01dc LOP_SET_BITS LCX_GAM 0000:00005fac 60 Deallocated 1 extent(s) starting at page 0001:00000108
000000cd:00000088:01dd LOP_COMMIT_XACT LCX_NULL 0000:00005fac 52 |
로그를 확인해 보면 모든 범위 할당을 통해 매우 큰 트랜잭션이 포함되어 있는 것을 알 수 있다.
soSAllocExtents 트랜잭션과 트랜잭션 ID 00005e50을 보자. 4개의 익스텐트(1extent = 64K) 256KB가 할당이 되었다. 이 트랜잭션에 대한 전체 로그 레코드의 크기는 1012byte이다.(트랜잭션ID 00005e50의 Log Record Length 합)
정렬이 종료되면 한 번에 하나씩 범위에 있는 ExtentDeallocForSort라는 시스템 트랜잭션의 할당이 취소 된다. 위의 결과에서는 트랜잭션 ID 000078fd (파란색 표시)에 해당 한다. 이는 400byte에 달하는 로그 레코드를 생성한다. 이 뜻은 4 * 400 = 1600Byte의 할당해제를 뜻한다.
할당 및 취소 작업을 결합하여 256KB를 spill 할 때 tempdb에 2612byte의 로그 레코드를 기록 한다.
따라서 90GB의 정렬로 사용된 경우 다음과 같이 유추 할 수 있다.
90GB = 90 * 1024 * 1024 = 94371840KB, 94371840KB / 256 = 368640 x 256KB 청크가 된다.
256KB 청크를 할당하고 2612byte 할당 해제를 하면 90GB의 로그는 368640 x 2612 = 962887680 bytes의 로그가 생성되며 이는 약 962887680 / 1024 / 1024 = 918MB 정도 된다.
Tempdb는 sort spill 이 발생 할 경우 할당과 해제를 반복하면서 효율적으로 기록 하는 것을 확인 할 수 있다.
[참고자료]
http://www.sqlskills.com/blogs/paul/understanding-data-vs-log-usage-for-spills-in-tempdb/
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
