Extent 2번째 이야기.. 그리고 IAM(Index Allocation Map)

안녕하세요. 비전팀 1기 강동운입니다.

요즘 데이터베이스 아키텍처 부분이 푹 빠져있습니다 ^^..

이번에는 IAM 과 저번 설명드린 Extent의 부가적인 설명을 드리기 위해 작성하게 되었습니다.

IAM은.. Index Allocation Map 의 약자이며... 테이블에 인덱스(또는 힙)당 기본적으로 한개씩 만들어지게 됩니다.

(인덱스가 하나도 없는 경우에도.. Heap에 대해서 하나의 IAM 페이지가 생성이 됩니다.)

(IAM은 인덱스(또는 힙)당 두개 이상이 될 수도 있는데요.. 그 부분은 이 글 후반부에 설명을 드리겠습니다.)

즉.. IAM을 통해 인덱스(또는 힙)가 체인으로 부모로써 연결하는 연결고리가 되는 셈이죠..

예를들어...

1. 테이블에 인덱스 가 없다면.. (Heap만 존재하겠죠~~)

     Heap의 페이지를 관리하기 위해 하나의 IAM 생성

2. 테이블에 클러스터드 인덱스(또는 힙)가 있다면

     클러스터드 인덱스의 페이지를 관리하기 위해 하나의 IAM..

3. 테이블에 클러스터드 와 넌클러스터드 인덱스(또는 힙)가 2개가 있다면

    각각의 인덱스 별로 하나씩 총 3개의 IAM이 생성이 됩니다.

IAM은..  SQL 기본적인 I/O 단위인 한개의 page(8kb)에 저장이 됩니다.

(각 page는 모두 page header를 가지고 있는데요.. 이 부분은 96 bytes입니다.

따라서 실제로 데이터가 저장이 되는건.. 8192(8kb) - 96 = 8096 bytes가 됩니다.

하지만 실제로 데이터가 저장되는건 8096보다 작습니다. offset 부분도 따로 저장이 되기 때문에 그렇습니다.

여기서 말하는 offset은.. row당 끝나는 바이트의 위치입니다. 이것까지 설명드리면 길어질 것 같아서 생략하도록 하겠습니다.)

그렇다면.. IAM이 하는 역할은 무엇이냐???

바로 ... 해당 인덱스(또는 힙)에 대해서 할당되어진 페이지 정보들을 알기 위해 사용합니다.

즉... IAM안에.. 이 인덱스(또는 힙)가 파일내에 어떤 페이지를 사용하는지 저장을 하고 있다는 말과도 같습니다.

IAM이 한개의 page라 8096 byte만큼 데이터를 저장할 수 있다고 하는데요.. 과연 어떤 식으로 저장을 할까요?

일단 잠시 Extent 얘기로 넘어가보도록 하겠습니다.

전에 Uniform Extent와 Mixed Extent 에 관련되서 설명을 드렸습니다.

링크: http://www.sqler.com/347983

각 인덱스(또는 힙)에 대해서.. 데이터 입력시 처음 8개의 페이지는 Mixed Extent가 할당이 되고.. 

그 뒤에 입력되는 데이터에 대해서는.. Uniform Extent로 관리된다고 했습니다.

그리고 Extent는 순차적인 8개 페이지를 무조건 하나의 익스텐트로 취급을 하게 됩니다.

그리고.. Uniform Extent로 설정이 된다면.. 할당된 페이지부터 + 7까지는 그 객체만 할당이 되도록 한다고 했습니다.

(다른 객체는 Unform Extent가 설정한 페이지에 저장이 될 수 없습니다.)

그리고 중요한 부분이 있습니다. Extent는 무조건 순차적인 8개 page를 하나의 익스텐트로 봅니다.

Extent1: 0 ~ 7 page

Extent2: 8 ~ 15 page

Extent3: 16 ~ 23 page

...

형태로 저장이 됩니다. 그러기 때문에... Uniform Extent의 첫번째 페이지는 ... 무조건

8 * n 번째 번호가 할당이 됩니다.

즉...

8,16,24,32,40,48,56,64,72,80,88,96,104,112,120,128,136,144,152,160,168,176,184,192,200,208,216,224,232,

240,248,256,264,272,280,288,296,304,312 .... 번호만이.. Uniform Extent의 첫번째 페이지번호가 될 수 있다는 말입니다.

8 page는.. 1번째 익스텐트

16 page 는 2번째 익스텐트

240 page.. 30번째 익스텐트에 있다는 말이죠!

예를들어..

A라는 테이블에 IDX라는 인덱스가 192 페이지에 Mixed Extent로 할당이 된다면..

192 ~ 199번의 페이지는.. Mixed Extent로 설정이 되기 때문에.. 어떤 인덱스(또는 힙)든 이 페이지에 저장이 될 수 있습니다.

예를들어 A라는 테이블의 IDX라는 인덱스가 240번째 Uniform Extent로 할당을 받았다면..

240 ~ 247번의 페이지는.. A라는 테이블의 IDX에 해당하는 값만 저장합니다.

이런 EXTENT 할당에 대한 정보는 GAM과 SGAM이 관리를 합니다. 이 부분은 다음에 설명드리도록 하겠습니다. ^^

그럼 여기서 익스텐트 얘기를 접어두고.. IAM으로 되돌아 와서....

IAM은... 반드시 두가지 형태로 저장을 해야합니다.

1. Mixed Extent로 할당된 페이지는 무엇이냐? (총 8개)

2. Uniform Extent로 할당된 익스텐트는는 무엇이냐?

근데 여기서 중요한건... 1번의 경우는 반드시 페이지 번호를 알아야 하지만.. 2번의 경우에는.. 익스텐트 번호만 알면됩니다.

왜냐면... Extent는 8개가 순차적인 페이지로 구성이 되었으니까요. ^^

그래서 IAM에서는... Mixed Extent에 대해서 8개의 페이지번호(파일 번호포함)를 저장을 하게됩니다.

파일번호는 2bytes를 사용하고, 페이지 번호는 4 bytes를 사용하게 됩니다. (총 6 bytes* 8 개  48 bytes)

이 2와 4는 두가지 숫자는 참으로 의미가 깊은 숫자 입니다. 파일 번호가 2 bytes로 사용한다는 말은..

SQL Server에서 디비당 파일 갯수가 SMALLINT의 MAX값인 2의 15승 -1 => 32,768 - 1= 32,767개까지 가질 수 있는 말입니다.

또한.. 페이지번호로 4바이트까지 가능하다는건, 테이블당 INT의 MAX값인 2의 31승 -1 개의 페이지를 사용할 수 있고, 

페이지는 8k이기 때문에.. (2의 31승 -1) * 8k 하면.. 총 16TB가 나옵니다..

결국 SQL Server는 한개의 데이터베이스 파일 또는 한개의 테이블에 대해서 최대 16TB까지만 저장이 가능합니다 ^^

(한개의 테이블이 16TB면 다른 테이블은 저장할 수 없겠죠~~ )

이거와 관련된 글은 윤선식님 글: http://www.sqler.com/334903   을 참고해주세요~~!

잠깐 얘기가 삼천포로 빠진 것 같네요~~! 

IAM은 48 bytes를 이용해서 Mixed Extent의 페이지 정보(파일번호, 페이지번호) 를 저장하게 됩니다.

그렇다면.. Uniform Extent의 정보는 어떤식으로 저장을 하는지에 알아볼 차례입니다.

아까 말씀드렸다 싶이.. 익스텐트의 번호를 알면.. 시작 페이지와 끝페이지를 알 수 있다고 했습니다.

그리고.. Uniform Extent로 할당이 되었다는 말은.. 이미 8개의 페이지가 예약이 되었다는 말입니다.

결국 IAM은 이것을 활용하기 위해.. Extent당 1 byte가 아닌.. 1 bit만 사용하게 됩니다.

bit 값이 1이면.. Uniform Extent로 할당이 되었다는 말이고, 0이란 말은.. 아직 할당이 되지 않거나 다른 인덱스(또는 힙)가 할당했다는 말이겠죠~!

아까 페이지당 데이터를 8096 bytes(오프셋 포함)에 까지 저장할 수 있다고 했습니다.

여기서 48 bytes를 뺴면 대략 8000 bytes가 나옵니다... 

8000 bytes를 bit를 환산하면 64,000 bits 가 되고.. 각 비트당 Uniform Extent를 하나씩 관리하게 되므로..

약 64,000 * 8 = 512,000 pages를 관리하게 됩니다.

512,000 pages 는 약 4G에 해당하는 수치입니다.

하나의 인덱스(또는 힙) 사이즈가 4G 이상으로 커진다면.. IAM페이지는 .. 두개가 될 것입니다.

아래 그림은 IAM의 예제 입니다.

 

IAM 페이지는.. DBCC IND 명령어로 확인해볼 수 있습니다.

DBCC IND('디비명','테이블명',옵션값)

DBCC IND 의 3번째 옵션값은

-2: 모든 IAM 페이지에 대한 정보

-1: 모든 페이지에 대한 정보

0: Heap에 대한 정보

1: Clustered Index에 대한 정보

2~254: Non Clustered Index에 대한 정보

(참고로 옵션값이 0보다 큰값이면... sys.indexes의 index_id와 같습니다. ^^)

를 통해 보실 수 있습니다.

DBCC IND('디비명','테이블명',-2)   --//테이블에 할당된 모든 IAM 페이지 조회

IAM에 대해 상세 정보를 보고 싶으시면... 아래 명령어로 상세 정보를 보실 수 있습니다.

파일번호는 select * from sysfiles 통해 조회가능하며.. MDF가 여러개가 아니라면.. 1번입니다.

DBCC TRACEON(3604)

GO

DBCC PAGE('디비명',파일번호, IAM페이지번호, 3) WITH TABLERESULTS

GO

감사합니다.

책을 봐도 머리가 아픈 부분이라.. 쉽게 설명을 하려고 풀어쓰긴 했는데... ㅜ.ㅜ... 글쓰긴 힘들군요;;