[MSSQL][advanced] 데이터의 물리적 구조

데이터 파일

• 주 데이터 파일[.mdf]과 보조데이터 파일[.ndf]로 구분
• 기본[디스크가 꽉 찰 때까지 10%씩 자동 증가]

로그 파일

• 가상 로그파일 [.ldf]
• 최소 단위 256kb로 구성
• 기본[최대 2TB (2008 기준) 10%씩 자동 증가]

page

page에 들어가는 항목 8가지 : [msdn 링크]

페이지 유형	내용
데이터	text in row가 ON으로 설정된 경우에 text, ntext, image, nvarchar(max), varchar(max), varbinary(max) 및 xml 데이터를 제외한 모든 데이터가 있는 데이터 행
인덱스	인덱스 항목
텍스트/이미지	큰 개체 데이터 형식: text, ntext, image, nvarchar(max), varchar(max), varbinary(max) 및 xml 데이터 데이터 행이 8KB를 초과하는 경우 가변 길이 열: varchar, nvarchar, varbinary 및 sql_variant 해당 페이지에 대해서 LOB (Large Object)로 지칭
전역 할당 맵(GAM) & 공유 전역 할당 맵(SGAM)	익스텐트가 할당되었는지 여부에 대한 정보 각 맵별로 64,000개의 익스텐트 또는 거의 4GB의 데이터를 처리
페이지의 사용 가능한 공간	페이지 할당 및 페이지의 사용 가능한 공간에 대한 정보
인덱스 할당 맵(IAM)	테이블 또는 인덱스에서 할당 단위당 사용하는 익스텐트에 대한 정보
대량 변경 맵(BCM)	마지막 BACKUP LOG 문 이후에 할당 단위당 대량 작업에 의해 수정된 익스텐트에 대한 정보
차등 변경 맵(DCM)	마지막 BACKUP DATABASE 문 이후에 할당 단위당 변경된 익스텐트에 대한 정보

IAM : Index Allocation Map
BCM : Bulked Change Map
DCM : Differential Change Map

extent

연속된 8개의 page로 구성[8 x 8 = 64KB] MB 당 16개의 extent로 구성되어 있음

• 균일 extent -> 1개의 객체(table)이 해당 extent를 사용
• 혼합 extent -> 여러개의 객체(table)이 해당 extent를 사용
• 기본적으로 테이블을 처음 생성시 혼합 extent

PFS (Page Free Space)

데이터 파일의 앞부분에 존재하는 특수 page.

개별 페이지의 할당 여부 및 각 페이지에 있는 빈 공간의 양과 같은 페이지의 할당 상태를 기록

각 페이지에 1바이트를 사용하여 페이지의 할당 여부를 기록
페이지가 할당된 경우 페이지의 상태를 [비어 있음], [1~50% 채워짐], [51~80% 채워짐], [81~95% 채워짐] 또는 [96~100% 채워짐]으로 기록
빈공간 추적에 사용


전역 할당 맵(GAM), 공유전역 할당 맵(SGAM)

익스텐트 할당 및 공간 관리 : [msdn 링크]


인덱스 할당 맵(IAM)

인덱스 및 테이블에 대해 extent 단위로 인덱스를 관리 : [msdn 링크]
새 행을 삽입해야 하는데 현재 페이지에 사용 가능한 공간이 없으면 IAM 및 PFS 페이지를 사용하여 할당할 페이지를 확인

힙 또는 텍스트/이미지 페이지의 경우 행을 보관하기에 충분한 공간이 있는 페이지를 확인

데이터 파일의 첫부분

위에서 나열된 요소들이 데이터 파일에 존재하는 순서는 다음과 같다.

DBCC TRACEON(3604) -- 추적 플래그 : 3604[메세지를 화면에 출력], 3605[메세지를 로그에 출력]
DBCC PAGE(AION, 1, 1, 3)
DBCC TRACEOFF

실제 데이터 내용보기

비공식적인(문서화되지 않은) DBCC PAGE 명령어를 이용하면 실제 데이터 페이지 내용을 확인할 수 있다.

 

데이터 파일의 가장 작은 단위. page[8KB]

1 page : 8KB(8*1024 = 8192 byte)

헤더 - 96byte
데이터 영역의 크기 : 8192byte - 헤더(96byte) - 한 행당 2byte를 차지하는 오프셋 - 각 행에 들어가는 4byte = 최대 한 행의 영역 8060byte

PAGE 명령어의 사용

DBCC PAGE (DB 이름, 파일 번호, 페이지 번호, 옵션)

옵션 : 0[헤더만], 1[행 단위로], 2[페이지 그대로], 3[행 그리고 컬럼 값]

데이터의 내용을 보기 위해서는 page의 주소를 알아야 함 -> SQL SERVER에서 제공하는 시스템 뷰를 사용

sysindexes 뷰의 indid(index ID) 속성

• 0 : 힙상의 데이터
• 1 : 클러스터된 인덱스
• 2~250 : 클러스터되지 않은 인덱스 또는 통계
• 255 : text, ntext 형 데이터

--1. 해당 테이블의 첫번째 데이터가 있는 root 페이지의 주소값은 아래 시스템 뷰의 root 컬럼 확인
SELECT * FROM SYSINDEXES WITH (READUNCOMMITTED) WHERE ID = OBJECT_ID('테이블이름') -- first 또는 root 컬럼의 첫 4byte(8자리)를 확인하여 byte swapping (대략 0XB6FD00000100 형태->0000FDB6)

--2. 위에서 확인한 root 컬럼의 첫 4자리 16진수 값을 10진수로 변환
SELECT CONVERT(INT, 0xB4) -- 이경우 180

--3.해당 값의 페이지를 추적하여 페이지를 확인
DBCC TRACEON(3604) -- 추적 플래그 : 3604[메세지를 화면에 출력], 3605[메세지를 로그에 출력]
DBCC PAGE(AION, 1, 180, 3)
DBCC TRACEOFF

 

행 구조

구성 요소

• 헤더 : 4byte
  • 상태 비트 : 2byte
  • 고정 컬럼 길이 : 2byte : 헤더(4byte)를 포함한 바이트 수를 지정
• 고정 컬럼 데이터 : [m]byte
• 전체 컬럼 수 : 2byte
• NULL 비트맵 : 컬럼 당 1bit
  • 테이블의 5번째 컬럼 값이 null이면 10 (0x10 -> 00010000)으로 5번째 컬럼의 null 값 여부를 체크
• 가변 컬럼 수 : 2byte
• 컬럼 오프셋 : 2byte x 가변 컬럼 수
• 가변 컬럼 데이터 : n byte

문자열의 경우

• byte swapping하여 해석 - [byte swap 링크],[엔디언 링크] [Universal Binary Programming Guildelines 링크]
• 공백은 0x20으로 저장

숫자값의 경우 그대로 해석
기록 단위 : 16bit = 256 = 2^8 = 1byte 2자리
 
2005의 경우는 기존과 다르게 3번 옵션이 2번 옵션과 동일하게 byte swap 상태로 출력 (2, 3 옵션 출력 통일), 실제 내부 저장은 동일
고정컬럼 데이터가 우선 저장된 이후 가변컬럼 데이터가 저장
가변 컬럼의 경우 저장된 위치를 파악하기 위한 오프셋을 가짐

• 장점 : 공간 절감 효과가 큼, null값인 경우 아예 공간을 차지하지 않음
• 단점 : 변동적인 오프셋을 이용하여 실제 컬럼의 위치를 찾아가야 하므로 CPU 부하가 증가됨

2자리 8bit -> 1byte
 
text, ntext, image 형 컬럼의 경우 데이터 행에는 루트를 가리키는 16byte의 포인터만 저장, 루트에서 B-트리 구조로 데이터를 분산 저장

행 구조 데이터 확인을 위한 간단한 명령어

SELECT CONVERT(데이터타입, 저장된 데이터)
-- 또는 역검색
SELECT CONVERT(BINARY, 호출되길 원하는 데이터)

 

tip

할당된 page에 대한 정보 조회 sp
sp_로 시작되는 프로시져 (master db에 존재하는 sp로 해당 db 서버의 모든 catalog에서 사용 가능한 system procedure)

EXEC sp_spaceused [테이블명]

기타 물리적 데이터들에 대한 내용 확인은 SQL SERVER에서 제공하는 시스템 뷰를 통해 확인 가능 [msdn 링크 - 호환성 뷰 참조]
DBCC TRACEON 명령어의 추적플래그 정의 - [msdn 링크]