SQL Server 페이지 및 익스텐트 아키텍처(1/4)
– 페이지 및 익스텐트 이해
Version : SQL Server 2005, 2008, 2008R2, 2012
SQL Server의 기본 저장 단위는 페이지 이다. 익스텐트는 물리적인 페이지 8개의 모음이다. 앞으로 총 4부에 걸쳐 페이지 및 익스텐트의 아키텍처를 살펴 보자.
페이지 및 익스텐트 아키텍처를 이해 함으로써 데이터베이스를 효율적인 디자인 및 개발 하는데 많은 도움이 된다.
SQL Server의 기본 저장 단위는 페이지라고 하였다. 데이터베이스에서 할당되는 데이터 파일(MDF, LDF)의 디스크 공간은 논리적인 페이지로 나누어 지며 0 ~ N 까지의 숫자가 연속적으로 페이지에 부여 된다.
디스크 I/O 작업은 페이지 수준에서 수행되며 익스텐트는 8개의 페이지가 모여 있는 것으로 모든 페이지는 익스텐트로 관리 된다.
[페이지]
SQL Server의 페이지 크기는 8KB 이다. 따라서 메가바이트당 128 페이지가 존재 한다. 각 페이지는 96바이트의 머리글로 시작하여 머리글에는 시스템 정보가 저장 된다.
다음 그림은 데이터 페이지의 구조이다. 데이터 행은 머리글 바로 다음부터 시작하며 행 오프셋 테이블은 페이지 끝에서 시작하는데 각 행 오프셋 테이블에는 해당 페이지에 있는 각 행에 대한 항목이 있다. 오프셋의 항목은 각 행의 첫째 바이트가 페이지 시작 지점에서 얼마나 떨어져 있는지 기록 한다. 행 오프셋 테이블의 항목 순서는 페이지의 행 순서의 역순이다.
실제 페이지의 구조를 확인 해 보자.
실습을 위해 테이블을 생성하고 데이터를 입력 한다.
create table TBL_B( ID int, name nvarchar(50) ) GO
insert into TBL_B values (1, 'Kangsungwook') insert into TBL_B values (2, 'http://sqlmvp.kr') insert into TBL_B values (3, 'http://datawaffle.com') GO
select * from TBL_B |
데이터 페이지를 확인하기 위하여 DBCC 명령을 이용 한다.
dbcc traceon(3604) GO
DBCC IND (SW_TEST, TBL_B, 1) GO
DBCC PAGE(SW_TEST, 1, 170, 1) GO |
다음과 같이 데이터 페이지에 대한 정보를 확인 할 수 있다.
우선 메시지에서 상단 부분을 보면 96 바이트의 헤더 정보를 확인 할 수 있다. 여기에는 페이지 번호, 페이지 유형, 해당 페이지의 사용 가능한 공간, 해당 페이지를 소유하고 있는 개체의 할당 단위 ID 정보를 제공한다.
헤더 정보 다음에 실제 입력되어 있는 행의 데이터 정보를 확인 할 수 있다. 다음 그림을 보면 위의 예제에서 입력하였던 데이터를 확인 할 수 있다.
마지막으로 데이터 페이지 하단에 행 오프셋이 있으며 시작 위치에서 얼마 떨어져 있는지 확인 할 수 있다.
대용량 행(varchar, nvarchar, varvinarym sql_variant 등)의 경우에는 8KB를 초과하는 경우가 있다. 실제 행들이 여러 페이지에 걸쳐 있을 수 없지만 이러한 행들은 전체 행의 8060바이트를 초과하면 SQL Server는 가변 길이 열을 가장 넓이가 넓은 열부터 ROW_OVERFLOW_DATA 할당 단위에 있는 페이지로 동적으로 옮긴다. ROW_OVERFLOW_DATA 할당 단위의 페이지로 이동하면 IN_ROW_DATA 할당 단위에 있는 원래 페이지에는 24바이트 포인터가 유지 된다.
[익스텐트]
익스텐트는 페이지 관리의 기본 단위이다. 익스텐트는 8개의 페이지가 모여있는 64KB 이다. 메가바이트당 16개의 익스텐트가 존재 한다.
SQL Server 에서는 효율적인 공간 할당을 위해 적은 양의 데이터를 포함하는 테이블에 전체 익스텐트를 할당하지 않는다. 따라서 다음과 같이 2가지 유형의 익스텐트를 제공한다.
- 혼합 익스텐트 : 아래 그림과 같이 하나의 익스텐트에 서로 다른 소유자의 페이지 8개가 존재 한다.
- 단일 익스텐트 : 하나의 익스텐트에 단일 소유자로 구성되어 있다, 주로 대량 삽입의 경우 단일 익스텐트로 할당 된다.
실제 익스텐트의 할당 정보를 살펴 보자.
- TBL_A
Column_name | Type | Computed | Length |
Col_1 | char | no | 4000 |
Col_2 | char | no | 4000 |
- TBL_B
Column_name | Type | Computed | Length |
ID | int | no | 4 |
name | nvarchar | no | 100 |
- TBL_C
Column_name | Type | Computed | Length |
ID | int | no | 4 |
name | text | no | 16 |
다음과 같이 테이블에 데이터가 저장 되어 있다.
익스텐트 할당 정보를 확인 하여 보자.
DBCC EXTENTINFO(SW_TEST, TBL_A, -1) DBCC EXTENTINFO(SW_TEST, TBL_B, -1) DBCC EXTENTINFO(SW_TEST, TBL_C, -1) |
TBL_A의 경우 대량 데이터 삽입으로 인하여 단일 익스텐트로 페이지 할당이 된 것을 확인 할 수 있다.
TBL_C의 경우 대량 행 사이즈로 인하여 LOB 영역으로 따로 할당 된 것을 확인 할 수 있다.
[참고자료]
http://msdn.microsoft.com/ko-kr/library/ms190969(v=sql.105).aspx
http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/23/328.aspx
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
