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

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

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


No. Subject Author Date Views
1771 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (4/4) – 분산형 분할 뷰(View) 확인 jevida(강성욱) 2016.09.27 1337
1770 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (3/4) – 뷰(View)의 인덱스 확인 jevida(강성욱) 2016.09.27 973
1769 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (2/4) – 뷰(View) 확인 jevida(강성욱) 2016.09.27 1447
1768 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (1/4) – SQL 문 최적화 및 Worktables jevida(강성욱) 2016.09.27 933
1767 SQL Server DMV를 이용한 통계 정보 확인 jevida(강성욱) 2016.09.27 1487
1766 DMV를 이용한 플랜 캐시 사용 정보 확인 jevida(강성욱) 2016.09.27 1172
1765 SQL Server 테이블 및 인덱스 구조 아키텍처(4/4) – 비클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1066
1764 SQL Server 테이블 및 인덱스 구조 아키텍처(3/4) – 클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1370
1763 SQL Server 테이블 및 인덱스 구조 아키텍처(2/4) – 힙 구조 jevida(강성욱) 2016.09.27 1069
1762 SQL Server 테이블 및 인덱스 구조 아키텍처(1/4) – 테이블 및 인덱스 구성 jevida(강성욱) 2016.09.27 1127
1761 SQL Server 트랜잭션 로그 아키텍처(4/4) – 미리 쓰기 트랜잭션 로그 jevida(강성욱) 2016.09.27 1537
1760 SQL Server 트랜잭션 로그 아키텍처(3/4) – 검사점 및 로그의 활성 부분 jevida(강성욱) 2016.09.27 1048
1759 SQL Server 트랜잭션 로그 아키텍처(2/4) – 트랜잭션 로그 물리 아키텍처 jevida(강성욱) 2016.09.27 1096
1758 SQL Server 트랜잭션 로그 아키텍처(1/4) – 트랜잭션 로그 논리 아키텍처 jevida(강성욱) 2016.09.27 1254
1757 파일 및 파일 그룹 아키텍처 jevida(강성욱) 2016.09.27 801
1756 SQL Server 페이지 및 익스텐트 아키텍처(4/4) – 수정된 익스텐트 추적 jevida(강성욱) 2016.09.27 1130
1755 SQL Server 페이지 및 익스텐트 아키텍처(3/4) – 개체에서 사용하는 공간 관리 jevida(강성욱) 2016.09.27 975
1754 SQL Server 페이지 및 익스텐트 아키텍처(2/4) – 익스텐트 할당 및 빈공간 관리 jevida(강성욱) 2016.09.27 1457
» SQL Server 페이지 및 익스텐트 아키텍처(1/4) – 페이지 및 익스텐트 이해 jevida(강성욱) 2016.09.27 3318
1752 SQL Server Error Log 보관 주기 설정 jevida(강성욱) 2016.09.15 2184





XE Login