데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

데이터에 대한 이해와 spill in tempdb

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

상황은 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

No. Subject Author Date Views
» 데이터에 대한 이해와 spill in tempdb jevida(강성욱) 2016.10.13 1764
1931 로그 파일이 많으면 왜 안 좋은가 jevida(강성욱) 2016.10.13 2051
1930 트랜잭션 백업 실패와 전체 백업 성공 그리고 대처 방안 jevida(강성욱) 2016.10.13 1543
1929 Fast recovery 와 로그 잠금 jevida(강성욱) 2016.10.13 2021
1928 고스트 클린업 jevida(강성욱) 2016.10.13 2251
1927 페이지 분할이 발생 하였을 때 롤백을 하면 어떻게 될까? jevida(강성욱) 2016.10.13 1646
1926 DBCC WRITEPAGE - DBCC 명령을 사용한 데이터 파괴하기 jevida(강성욱) 2016.10.13 1641
1925 SQL Server Backup Error 3023 jevida(강성욱) 2016.10.13 2256
1924 Delete 작업과 페이지 offset 변화 jevida(강성욱) 2016.10.13 1477
1923 트랜잭션 로그 및 LSN을 이용한 삭제된 데이터 복구 jevida(강성욱) 2016.10.13 5127
1922 PFX 형식의 인증서를 SQL Server에서 사용하기 jevida(강성욱) 2016.10.13 1150
1921 SQL Server NUMA 메모리 노드와 Operating System 접근 jevida(강성욱) 2016.10.13 1292
1920 SQL Server 에러 핸들링 비용 비교 jevida(강성욱) 2016.10.13 1302
1919 압축 백업 시 Checksum 옵션으로 손상 확인하기 jevida(강성욱) 2016.10.13 1638
1918 SQL Server 2012 Memory Manager 구성 jevida(강성욱) 2016.10.13 1263
1917 SQL Server Memory Manager 변화 jevida(강성욱) 2016.10.13 1483
1916 Ring_Buffer_Resource_Monitor jevida(강성욱) 2016.10.13 1000
1915 SQL Server 프로파일러 템플릿 만들기 jevida(강성욱) 2016.10.13 1337
1914 SQL Server 메모리 병목 현상 식별 jevida(강성욱) 2016.10.13 1786
1913 SQL Server 시작 옵션 사용 jevida(강성욱) 2016.10.13 1803





XE Login