Uniform Extent 와 Mixed Extent

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

이번에는 Extent에 관련되서 얘기를 꺼내볼까 합니다.

SQL Server에서 Extent는 공간 관리의 최소 단위라고 합니다. 그리고 8개의 page가 하나의 Extent안에 들어있다고 합니다.

하나의 Page가 8kb 이므로.. 하나의 Extent는 64kb 를 관리할 수 있게 됩니다.

SQL Server에서 Extent는 두가지 형태로 존재됩니다.

1. Mixed Extent(혼합 익스텐트)

2. Uniform Extent(균일 익스텐트)

Mixed Extent는 말그대로 혼합 익스텐트 입니다. 아까 말씀드렸다 싶이 하나의 Extent는 8개의 page가 존재한다고 했습니다.

하지만 이 Mixed Extent는 같은 개체가 아닌 다른 개체의 page도 포함을 하게끔 합니다.

아래의 형식 처럼 하나의 익스텐트의 다양한 테이블에 해당하는 페이지를 가지고 있습니다.

Mixed Extent 의 구조

Character	Mail	Inventory	Character	CharacterInfo	Gift	Order	Buy
192 page	193 page	194  page	195 page	196 page	197 page	198 page	199 page

Uniform Extent의 구조

Character	Character	Character	Character	Character	Character	Character	Character
304 page	305pag	306 page	307 page	308 page	309 page	400 page	401 page

처음 테이블을 생성해서.. 8개의 페이지가 생성 될때 까지는 무조건.. Mixed Extent에 할당이 됩니다.

그리고 9번째 페이지부터는.. Uniform Extent를 만들어서 페이지를 그곳에 할당하게 됩니다.

예제를 통해서 한번 확인해보겠습니다.

IF OBJECT_ID('FragmentTest','U') IS NOT NULL

       DROP TABLE FragmentTest

GO

CREATE TABLE FragmentTest

(

       IDX          CHAR(800)    NOT NULL

,      DATA   CHAR(1400)   NOT NULL

)

GO

CREATE CLUSTERED INDEX CL_FragmentTest_IDX ON FragmentTest(IDX)

 

 

DECLARE @I INT = 1

 

WHILE(@I < 100)

BEGIN

       INSERT INTO FragmentTest SELECT CONVERT(CHAR(800),@I), REPLICATE('A',1400)

       SET @I = @I + 1

END

GO

DBCC EXTENTINFO('Eastluck','FragmentTest')

위의 그림처럼.. 처음 8개의 page는 Mixed Extent로 할당이 되고, 그 후부터는 Uniform Extent에 할당이 됩니다.

Mixed 와 Uniform 의 구분은.. ext_size로 볼 수 있습니다.

(1: Mixed Extent, 8: Uniform Extent)

아래 이탤릭채는 4월 1일 수정되었습니다.

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번째 익스텐트에 있다는 말이죠!

그리고.. Uniform Extent의 page_id값을 주목해서 보자면..

4000 ~ 4007: 500번째 Extent(4000/8 = 500)

4008 ~ 4015: 501번째 Extent(4008/8 = 501)

4016 ~ 4023: 502번째 Extent(4016/8 = 502)

자세한 Table에 할당된 Page 정보를 보기 위해 ..

아래 명령어를 이용할 수 있습니다.

DBCC IND('디비명','테이블명',번호)

번호값

-2: IAM 페이지

-1: 모든 페이지

0: Heap 

1: Clustered Index

2 ~ : Non-Clustered Index

이제 왜 SQL Server는 Extent를 두개를 만들었을까라는 고민을 하게 됩니다.

만약 Mixed Extent만 있다면 어떻게 될까요?

Mixed Extent만 존재하게 된다면, OLTP 환경 처럼 실시간으로 INSERT와 UPDATE가 되는 환경에서는.. 

새로운 페이지를 할당 받아 특정 익스텐트에 할당하게 될 경우 할당 경합이 발생하게 됩니다.

즉... 이런 경우를 위해 EXTENT LOCK이 걸릴 수 있는 소지도 분명히 있겠죠..

따라서 테이블 사이즈가 어느정도 이상 커지면 Uniform Extent 로만 할당받아 이런 문제를 없애고자 했습니다.

그렇다면.. Uniform Extent만 존재하면 어떻게 될까요?

Uniform Extent는 무조건 8페이지를 예약으로 잡아두기 때문에.. 페이지 사이즈가 아무리 작아도.. 64kb를 이용하게끔 될 수 밖에 없을 것입니다.

이러한 문제 때문에 Mixed Extent를 쓰게 된 것이겠죠.

테이블이 커지더라도 Mixed Extent를 안쓰게 할 순 없을까? ... 바로 인덱스 리빌드를 하면 가능합니다.

예제를 통해서 확인해보겠습니다.

IF OBJECT_ID('FragmentTest','U') IS NOT NULL

       DROP TABLE FragmentTest

GO

CREATE TABLE FragmentTest

(

       IDX          CHAR(800)    NOT NULL

,      DATA   CHAR(1400)   NOT NULL

)

GO

CREATE CLUSTERED INDEX CL_FragmentTest_IDX ON FragmentTest(IDX)

 

 

DECLARE @I INT = 1

 

WHILE(@I < 240) --// 239개만 넣어봤습니다.

BEGIN

       INSERT INTO FragmentTest SELECT CONVERT(CHAR(800),@I), REPLICATE('A',1400)

       SET @I = @I + 1

END

DBCC SHOWCONTIG('FragmentTest')

DBCC SHOWCONTIG이(가) 'FragmentTest' 테이블을 검색하는 중...

테이블: 'FragmentTest'(373576369); 인덱스 ID: 1, 데이터베이스 ID: 5

TABLE 수준 검색을 수행했습니다.

- 검색한 페이지................................: 89

- 검색한 익스텐트 ..............................: 17

- 익스텐트 스위치..............................: 45

- 익스텐트당 평균 페이지 수........................: 5.2

- 검색 밀도[최적:실제].......: 26.09% [12:46]

- 논리 검색 조각화 상태 ..................: 59.55%

- 익스텐트 검색 조각화 상태 ...................: 11.76%

- 페이지당 사용 가능한 평균 바이트 수.....................: 2164.0

- 평균 페이지 밀도(전체).....................: 73.26%

DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

DBCC EXTENTINFO('Eastluck','FragmentTest')

이제 인덱스 리빌드를 하겠습니다. FILLFACTOR 는 100으로 정했습니다.

DBCC DBREINDEX ('Eastluck.dbo.FragmentTest', 'CL_FragmentTest_IDX', 100)

DBCC SHOWCONTIG('FragmentTest')

DBCC SHOWCONTIG이(가) 'FragmentTest' 테이블을 검색하는 중...

테이블: 'FragmentTest'(405576483); 인덱스 ID: 1, 데이터베이스 ID: 5

TABLE 수준 검색을 수행했습니다.

- 검색한 페이지................................: 80

- 검색한 익스텐트 ..............................: 10

- 익스텐트 스위치..............................: 9

- 익스텐트당 평균 페이지 수........................: 8.0

- 검색 밀도[최적:실제].......: 100.00% [10:10]

- 논리 검색 조각화 상태 ..................: 0.00%

- 익스텐트 검색 조각화 상태 ...................: 10.00%

- 페이지당 사용 가능한 평균 바이트 수.....................: 1496.6

- 평균 페이지 밀도(전체).....................: 81.51%

DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

리빌드를 수행했더니 검색한 페이지 수가 80개로 줄었습니다. 당연한 이치 입니다. 인덱스가 조각화가 난 상태라 .. 조각모음이 100%로 설정이 되었으니.. 익스텐트당 8개씩 페이지가 들어가도록 적용되었습니다.

DBCC EXTENTINFO('Eastluck','FragmentTest')

인덱스를 리빌드를 했는데도 불구하고 아직 Mixed Extent에 할당된 애들은 존재합니다.

여기서 데이터 두건을 더 넣어보겠습니다.

INSERT INTO FragmentTest SELECT CONVERT(CHAR(800),240), REPLICATE('A',1400)

INSERT INTO FragmentTest SELECT CONVERT(CHAR(800),241), REPLICATE('A',1400)

DBCC SHOWCONTIG('FragmentTest')

DBCC SHOWCONTIG이(가) 'FragmentTest' 테이블을 검색하는 중...

테이블: 'FragmentTest'(405576483); 인덱스 ID: 1, 데이터베이스 ID: 5

TABLE 수준 검색을 수행했습니다.

- 검색한 페이지................................: 81

- 검색한 익스텐트 ..............................: 11

- 익스텐트 스위치..............................: 11

- 익스텐트당 평균 페이지 수........................: 7.4

- 검색 밀도[최적:실제].......: 91.67% [11:12]

- 논리 검색 조각화 상태 ..................: 2.47%

- 익스텐트 검색 조각화 상태 ...................: 9.09%

- 페이지당 사용 가능한 평균 바이트 수.....................: 1550.8

- 평균 페이지 밀도(전체).....................: 80.84%

DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

검색한 페이지 수가 81로 증가 했으며, Extent도 10개에서 11개로 늘어났습니다.

DBCC EXTENTINFO('Eastluck','FragmentTest')

다시 인덱스 리빌드

DBCC DBREINDEX ('Eastluck.dbo.FragmentTest', 'CL_FragmentTest_IDX', 100)

DBCC SHOWCONTIG('FragmentTest')

DBCC SHOWCONTIG이(가) 'FragmentTest' 테이블을 검색하는 중...

테이블: 'FragmentTest'(437576597); 인덱스 ID: 1, 데이터베이스 ID: 5

TABLE 수준 검색을 수행했습니다.

- 검색한 페이지................................: 81

- 검색한 익스텐트 ..............................: 11

- 익스텐트 스위치..............................: 10

- 익스텐트당 평균 페이지 수........................: 7.4

- 검색 밀도[최적:실제].......: 100.00% [11:11]

- 논리 검색 조각화 상태 ..................: 0.00%

- 익스텐트 검색 조각화 상태 ...................: 9.09%

- 페이지당 사용 가능한 평균 바이트 수.....................: 1523.5

- 평균 페이지 밀도(전체).....................: 81.18%

DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

페이지 수와 익스텐트 수도 1개씩 늘었습니다.

DBCC EXTENTINFO('Eastluck','FragmentTest')

이제 모든 Extent가 Uniform Extent로 전환된 것을 볼 수 있습니다.

SQL Server는.. 검색한 페이지 수가 80개가 넘었을 때 인덱스 리빌드를 수행하면, 모든 페이지를 Uniform Extent로 전환을 합니다.!

그렇다면 여기서 데이터를 지운다음에 리빌드를 하면 어떻게 될까요??

 

DELETE FROM FragmentTest WHERE IDX > '9'  --//10개의 데이터 삭제

다시 인덱스 리빌드

DBCC DBREINDEX ('Eastluck.dbo.FragmentTest', 'CL_FragmentTest_IDX', 100)

DBCC EXTENTINFO('Eastluck','FragmentTest')

데이터를 삭제했더니.. 다시 Mixed Extent가 생겼습니다. 참으로 놀랍습니다 ^^

SQL Server는 정말 똑똑한 녀석인 것 같네요.

Page 와 Extent 관련 해서 한번 읽어보시기 바랍니다.

Page & Extent 1탄: http://www.sqler.com/271775

Page & Extent 2탄: http://www.sqler.com/316316

헉... 놀라운 사실을 알아내서 추가했습니다.

데이터를 241개 넣은 후에 인덱스 리빌드를 하면... 모두 Uniform Extent로 변경됩니다.

하지만.. 여기서 다시 데이터를 넣으면.. Mixed Extent로 먼저 데이터가 할당이 되네요.

참으로 신기하군요 -_-;;

두건을 더 넣고 한번 다시 봤습니다.

감사합니다!