sql server 데이터베이스 구조의 원리(1) - 데이터 파일

1.페이지와 익스텐트

 

데이터베이스를 구성하는 데이터 파일은 내부적으로 페이지라고 하는 8KB의 논리 단위로 구분되어 사용된다.
페이지는 테이블 등의 오브젝트에 저장된 데이터를 읽고 쓸 때의 최소 I/O 단위다.

그러나 오브젝트에 새로운 영역을 할당해야 하는 경우에는 페이지가 아니라 익스텐트라는 단위가 사용된다.
익스텐트는 8KB 페이지가 8개로 구성되어 있다.
테이블에 할당된 모든 페이지에 빈 용량이 없어지면 테이블에는 새로운 익스텐트가 할당된다.

 

 

 

 

2.단일 익스텐트와 혼합 익스텐트

 

익스텐트의 종류는 하나의 오브젝트에 8페이지 모두를 점유하는 단일 익스텐트와 
복수의 오브젝트를 공유하는 혼합 익스텐트가 존재한다.

SQL 서버는 2014까지 디폴트 동작으로 오브젝트의 초기 구성에 혼합 익스텐트를 할당했다.
디스크의 효율적인 이용이 목적이었다.

예를 들어, 신규 오브젝트에 단일 익스텐트(8개의 페이지)를 할당했는데, 
오브젝트 크기가 1페이지에서 더 이상 늘어나지 않는다.
그렇다면 나머지 7페이지를 낭비하는 셈이다.

낭비되는 디스크 영역을 가능한 줄이기 위해 신규로 할당된 오브젝트에 우선 혼합 익스텐트를 할당하고
그 뒤로 사이즈가 커지면 단일 익스텐트를 할당했다.

 

그러나 SQL 서버 2016 이후에는 오브젝트 초기 구성부터 디폴트로 단일 익스텐트를 할당하는 것으로 변경되었다.
그 이유는 혼합 익스텐트가 단일 익스텐트보다 작업량이 많아 생기는 오버헤드 때문이다.

디스크의 효율적인 사용보다 퍼포먼스를 우선했다는 것을 알 수 있다.
다만,  SQL 서버 2016 이후에는 옵션을 지정해서 디폴트의 동작을 변경할 수 는 있다.

 

--혼합 익스텐트 할당을 무효화 (디폴트 설정)
ALTER DATABASE AdventureWorks2012
SET MIXED_PAGE_ALLOCATION OFF;
GO

--혼합 익스텐트 할당을 유효화
ALTER DATABASE AdventureWorks2012 
SET MIXED_PAGE_ALLOCATION ON; 
GO

 

 

 

3.페이지의 종류

 

데이터 파일 내의 페이지는 무엇인가를 저장하는 용도로 사용된다.

IN_ROW_DATA 페이지의 데이터를 수정하여 ROW_OVERFLOW_DATA 페이지에 데이터를 저장하는 과정

 

1) 테이블 A에 소속된 페이지 (테이블 A : col1 int, col2 varchar(2000))

 

 

 

2) varchar 칼럼을 지금보다 큰 사이즈의 데이터로 갱신 update 
update 테이블 A set col2 = 2,000바이트분의 데이터 where col1 = 1

 

 

 

 

3) 오버플로가 발생한 데이터는 ROW_OVERFLOW_DATA로 다른 페이지에 저장, 원래 페이지에는 포인터가 저장됨

 

 

 

4.페이지의 배치

 

데이터 파일 내 대부분의 페이지는 데이터와 인덱스를 저장하는 데 사용되지만
극히 소수의 관리 정보만을 저장한 페이지가 있다.

해당 정보를 저장하는 페이지는 GAM, SGAM, PFS 가 존재한다.

그 페이지에는 데이터베이스를 효율적으로 관리하기 위해 데이터베이스의 할당 정보가 보관되어 있다.

 

 

1) 전역 할당 맵 GAM (Global Allocation Map)

 

GAM 페이지는 어떤 익스텐트가 할당되었는지 기록하며 GAM 페이지 하나는 64,000개 (약 4GB)의 데이터를 처리한다.
GAM은 각 익스텐트에 대해 1비트를 갖는데, 이 비트가 1인 경우 익스텐트는 비어있고 (미할당)
비트가 0인 경우 익스텐트는 할당된 상태이다.

 

 

 

 

2) 공유 전역 할당 맵 SGAM(Shared Global Allocation Map)

 

SGAM 페이지는 어떤 익스텐트가 현재 혼합 익스텐트로 사용되고 있는지와
적어도 하나 이상의 사용되지 않은 페이지를 가지는지를 기록한다.
이 비트가 1인 경우는 혼합 익스텐트 페이지이면서 빈 페이지를 가지고 있는 상태이고
비트가 0인 경우는 혼합 익스텐트 페이지가 아니거나 혼합 익스텐트 페이지이지만 빈 페이지 없이 모든 페이지가 사용 중인 상태이다.

 

 

 

3) PFS(Page Free Space) 

PFS 페이지는 개별 페이지의 할당 여부 및 각 페이지에 있는 빈 공간의 양과 같은 상태를 기록한다.
PFS는 각 페이지에 1바이트를 사용하여 페이지의 할당 여부를 기록하고 할당된 경우에는
1~50% 채워짐, 51~80% 채워짐, 81~95% 채워짐, 96~100% 채워짐으로 기록한다.

 

 

4) IAM(Index Allocation Map) 

 

clustered index , non-clustered index 및 heap 와 각각의 오브젝트에 사용되는 익스텐트를 링크하기 위해 사용되는

8KB의 페이지이다. 1개의 IAM 으로 64,000 익스텐트분(4GB)의 정보를 관리할 수 있다.

인덱스 등의 사이즈가 4GB를 넘는 경우는 2개째 IAM 이 할당된다. 

쌍방의 IAM은 상호의 링크 정보를 가지고 있다.

 

 

위처럼 IAM 페이지를 참조함으로써 오브젝트에 속하는 익스텐트를 확인할 수 있다.

때문에 SELECT 스테이트먼트 등에 의한 데이터 참조 시에 사용된다.

 

 

 

그렇다면 이 페이지들은 어디에 있을까?
익스텐트 할당 및 관리를 위해 SQL Server 데이터베이스 엔진에서 사용하는 페이지 순서는 아래와 같다.

 

DBCC PAGE 를 이용해 페이지를 확인해보자 (DB명, 파일번호, 페이지번호, 상세옵션)

1번 페이지

2번 페이지

 

3번 페이지