페이지 내부 오버헤드 7Byte (Row overhead 7 byte in page)

안녕하세요 ^^.

SQLTAG에서 후원하는 초급 SQL Server 스터디 소모임인 SQL Start에서 공부하고 있고,
CCodeTech의 DB팀에서 근무중인
Daily-SQL 블로그의 차태욱입니다.

오늘은 하나의 Page에 저장 될 수 있는 최대 길이의 행과, 페이지 내부 오버헤드에 대한 부분을 살펴보기 위한 공부를 하였습니다.

도움이 되었으면 좋겠네요 !

혹시 틀린것이 있거나, 제가 잘못 해석한것이 있다면, 댓글로 남겨주시면, 시정하고, 수정내역 남기도록 하겠습니다 ^^.

 

그럼 본문 시~작!

 지난 2012년 06월 27일 SQL Start 에서 진행된 스터디에서, '데이터 페이지 구조' 에 대해 공부 하던 중 우리는 이런 의문을 가지게 되었습니다.

"SQL Server에서 테이블을 만들 때 자주 발생하는 에러 중 하나인 1701 번 에러의 내부 오버헤드는 무엇이고, 이 크기는 고정 되어 지는가?"

그리하여...! 아래와 같은 테스트를 진행 하였습니다.

그럼

BEGIN!

==========================================

1. 테스트를 위해 다음과 같은 DB를 생성 합니다.

CREATE DATABASE PageOverHead;
GO
USE PageOverHead;
GO

 

2. SQL Server에서 데이터 저장에 사용하는 페이지에 대해 알아 보기 위해
    다음 링크의 BOL에서 아래와 같은 내용을 유추 하였습니다.
    (http://msdn.microsoft.com/ko-kr/library/ms190969(v=sql.105).aspx)

--SQL Server 에서 이용하는 Data가 저장되는 공간인 Page는 8KB (8192Byte) 의 크기를 사용합니다.
--이는 1MB당 (1024KB) 128개의 Data가 저장되는 Page를 생성 할 수 있음을 알려 줍니다.
--그럼 8개의 Page가 모여있는 단위인 익스텐트는(64KB) 총 16개가 생성이 된다는 것을 알 수 있지요.

 

3. 그럼 정말로... 8192 바이트의 데이터를 사용하여 하나의 페이지를 구성하는지
    확인해 봅시다.

 --한 Row 에 8192Byte를 사용하는 테이블을 만들어 보겠습니다.

--다음 쿼리를 실행 하니, 다음과 같은 에러를 출력 하네요
--최소 행 크기가 8060Byte 만큼 허용이 된다는 부분을 통해 한 로우당 최대 8060Byte를 초과하지
   않도록 테이블을 생성 해야 한다는 점을 유추 할 수 있습니다.

 

--단 예외 상황은 존재 한다고 합니다.
    (http://msdn.microsoft.com/ko-kr/library/ms186981(v=SQL.90).aspx)
--위 링크를 통해 BOL을 살펴 보면, 
   varchar(max),nvarchar(max),varbinary(max),xml,text,ntext,image
   와 같은 형식의 데이터는 일반 열 데이터와 다른 곳에 저장이 된다는 점을 확인 할 수 있겠네요.
   하지만 위에 정의된 형식 외의 데이터는 한 로우당 8060Byte의 한계를 넘지 못한다는 것을 
   알 수 있습니다.

 

--다음 링크의 BOL을 통해 살펴보면, 8060Byte 외의 나머지 132Byte 중 96Byte는 각 페이지의
   머리글로 사용 된다는 점을 확인 할 수 있습니다. (페이지 헤더)
   (http://msdn.microsoft.com/ko-kr/library/ms190969(v=sql.105).aspx)

 

--8192-96-8060=......36 Byte...나머지 36Byte는 어디서 사용 하는 걸까요?
   아래 링크를 통해 살펴보면, 
   36Byte의 공간은 페이지 내에 저장된 열이, 페이지 내의 어느 위치에 존재하는지 확인
   할 수 있도록 정리되어진 Row-Offset 의 용도로 사용 되어 진다는 것을 알아 볼 수 있습니다.
--(http://social.msdn.microsoft.com/Forums/eu/sqldatabaseengine/thread/0bb88137-f4f6-4277-aed1-be5fd6d62463)

4. 이제.. 열 당 8060Byte를 사용 하도록 하여 테이블을 생성 하여 봅시다.

 

--위 쿼리를 실행 하면, 아까 로우당 최대 8192Byte를 할당했을때와 숫자만 바뀌고 
   동일한 에러 메세지가 반환 됩니다.
   에러 메세지를 통해 살펴 보면 내부 오버헤드가 7Byte라고 하네요.
   내부 오버헤드..가 무엇일까요?

--친철한 지식 사전을 통해 살펴보죠.

--다음을 통해 살펴보면, 위 에러 메세지에서의 내부 오버헤드란
   페이지 내부에서, 데이터를 담기 위해 필요로 하는 부분 등을 담는 공간이라고
   유추 할 수 있겠네요.

5. 그럼.. 한 열의 최대 길이가 8053Byte 가 되는 (8060Byte-7Byte)
    테이블을 생성 해 보겠습니다.

--드디어..테이블이 생성 되었습니다 T_T...!
   아무런 제약조건도,인덱스도 없는 테이블 하나 만들기 참 힘드네요...

6. 오늘 공부한 부분의 목적지인.. 내부 오버헤드의 크기는 고정되어 지는가?
    에 대한 해답을 유추 해 보도록 하겠습니다.

6.Case 1. 한 페이지 내부에서 내부 오버 헤드의 크기가 7Byte로 고정될 것이다.

 --한 행의 최대 크기가 4026바이트인 테이블을 생성하고,
    두개의 열에 해당하는 데이터를 입력 합니다.

--두 행의 총 데이터 크기를 합치면 8052Byte 인 이유는
   데이터 크기 값을 통해 8053을 정확히 2등분 할 수 없었기 때문입니다.

 

--다음 명령을 통해 Heap 영역에 몇개의 데이터 페이지가 생겼는지 확인 해보겠습니다.

--Index Allocation Page 인 80번 페이지를 제외하면, 다음 테이블은 하나의 데이터 페이지만
   가지고 있다라는 것을 확인 하였습니다.

 

--다음의 결과로 미루어 보아, 열(Row) 마다 내부 오버 헤드가 따로 할당 될 것이다 라는
   Case2번에 해당하는 테스트는 진행 해 볼 필요가 없겠군요.

--실제적으로 내부에서 OverHead 인 7Byte가 어떻게 할당 되는지는 확인
   할 수 없었지만. 내부 오버헤드의 크기가 행의 갯수에 상관 없이, 7Byte로
   (일반적인 경우에) 고정되어 진다는 부분은 확인 할 수있는
   테스트 였습니다.

--제가 부족하여..혹시 잘못 테스트 하였거나, 잘못 생각한점, 틀린 점이
   있다면, 댓글 남겨주시면, 공부하여 꼭 시정하도록 하겠습니다.

--현재 테스트에서, 클러스터 인덱스의 존재 여부에 따른 차이점, 페이지 내의
   행수가 2행이 아닌 다수의 행이었을때의 차이점에 대해서는 확인 하지 못하
   였기에, 기회가 닿는대로 더 테스트해보고 내용 추가하여 더 좋은 글이 될
   수 있도록 노력 하겠습니다.

==========================================

처음에 말씀 드린 것 처럼...
혹시 틀린것이 있거나, 제가 잘못 해석한것이 있다면, 댓글로 남겨주시면, 시정하고, 수정내역 남기도록 하겠습니다 ^^.

이 글을 읽어주신 모든분께.. 감사드립니다 ^^~!