데이터가 디스크에 어떻게 저장되는지 페이지와 B-트리 수준에서 들여다봅니다.
스토리지 엔진 내부 구조: 페이지, 익스텐트, B-트리
입문 과정에서 "인덱스가 있으면 빠르다"는 사실과 클러스터형·비클러스터형의 개념적 차이를 살펴보았습니다. 그렇다면 실제로 SQL Server는 데이터를 디스크에 어떤 단위로 배치하고, B-트리를 따라 어떻게 탐색하며, 인덱스가 느려지는 물리적 원인은 무엇일까요? 이 강의는 스토리지 엔진의 물리적 구조를 해부함으로써 그 질문에 답합니다.
쿼리 튜닝, 인덱스 유지관리, 고가용성 설계 등 이후 강의들의 논리적 토대가 되는 내용이므로, 개념과 실제 관찰 명령을 함께 익혀두는 것이 중요합니다.
학습 목표
- 8KB 페이지와 익스텐트가 SQL Server 스토리지의 기본 단위임을 이해한다.
- **힙(Heap)**과 클러스터형 인덱스의 물리적 저장 방식 차이를 설명할 수 있다.
- B-트리의 루트·중간·리프 레벨을 거치는 페이지 탐색 경로를 추적할 수 있다.
- **페이지 분할(Page Split)**이 발생하는 조건과 Fill Factor 설정의 관계를 파악한다.
DBCC IND,sys.dm_db_index_physical_stats등으로 내부 구조를 직접 관찰하고, REBUILD vs REORGANIZE 전략을 선택할 수 있다.
8KB 페이지와 익스텐트
SQL Server가 디스크와 메모리 사이에서 데이터를 주고받는 최소 단위는 **8KB 페이지(Page)**입니다. 테이블에 행 한 줄을 읽든 수백 줄을 읽든, I/O는 항상 페이지 단위로 발생합니다. 96바이트의 페이지 헤더와 실제 데이터 영역, 그리고 페이지 끝의 행 오프셋 배열(Row Offset Array)로 구성됩니다.
┌────────────────────────────────────────┐ 0 byte
│ Page Header (96 bytes) │
│ PageID, AllocUnitID, FreeCount, │
│ IndexID, PrevPage, NextPage 등 │
├────────────────────────────────────────┤ 96 byte
│ Data Records │
│ (행 데이터 / 인덱스 키 + 포인터 등) │
│ ... │
├────────────────────────────────────────┤
│ Row Offset Array (역방향 증가) │
└────────────────────────────────────────┘ 8192 byte
**익스텐트(Extent)**는 연속된 8개 페이지(총 64KB)의 묶음입니다. 익스텐트에는 두 종류가 있습니다.
| 종류 | 설명 |
|---|---|
| 균일 익스텐트(Uniform Extent) | 단일 객체(테이블 또는 인덱스)가 8페이지 전부를 독점 |
| 혼합 익스텐트(Mixed Extent) | 최대 8개의 서로 다른 객체가 페이지를 공유 |
작은 테이블이 처음 생성되면 혼합 익스텐트에서 시작하고, 크기가 커지면 균일 익스텐트로 전환됩니다. 이 정보는 GAM(Global Allocation Map)과 SGAM(Shared Global Allocation Map) 페이지에 비트맵 형태로 기록됩니다.
-- 데이터베이스에서 파일별 페이지·익스텐트 사용 현황 확인
SELECT
df.name AS file_name,
df.physical_name,
df.size * 8 / 1024 AS size_mb,
df.type_desc
FROM sys.database_files df;
데이터 파일(.mdf/.ndf) 구조
SQL Server 데이터 파일은 페이지들의 선형 배열입니다. 파일 내 각 페이지는 (FileID:PageID) 쌍으로 고유하게 식별됩니다. 예를 들어 (1:127)은 파일 1의 127번 페이지를 의미합니다.
파일의 첫 번째 페이지(PageID 0)는 파일 헤더 페이지이며, 그 뒤로 PFS·GAM·SGAM 등 시스템 할당 페이지들이 규칙적인 간격으로 배치됩니다. PFS는 페이지마다 1바이트로 여유 공간을 추적하므로 한 PFS 페이지가 약 8,088개의 페이지를 관리하며, GAM/SGAM은 한 페이지가 비트맵으로 약 64,000개의 익스텐트(약 4GB)를 추적합니다.
PageID 0 : 파일 헤더
PageID 1 : PFS (Page Free Space) - 약 8,088 페이지(약 64MB)마다 반복
PageID 2 : GAM (Global Allocation Map) - 약 64,000 익스텐트(약 4GB)를 추적하며 그 간격으로 반복
PageID 3 : SGAM (Shared GAM) - GAM과 동일하게 약 64,000 익스텐트(약 4GB)를 추적하며 반복
PageID 4 : 부트 페이지 (파일 1에만 존재)
PageID 6 : DCM (Differential Changed Map) - GAM과 동일하게 약 4GB를 추적하며 반복
PageID 7 : BCM (Bulk Changed Map) - GAM과 동일하게 약 4GB를 추적하며 반복
PageID 8 ~ : 사용자 데이터/인덱스 페이지
💡 TIP
.mdf는 주 데이터 파일(Primary),.ndf는 보조 데이터 파일(Secondary)입니다. 파일그룹(Filegroup) 단위로 묶여 논리적으로 관리되며, 여러 파일그룹을 활용해 I/O 부하를 분산할 수 있습니다.
힙(Heap)과 클러스터형 인덱스의 물리적 저장 방식 차이
힙(Heap)
클러스터형 인덱스가 없는 테이블을 힙이라고 합니다. 행이 삽입되는 순서대로 사용 가능한 페이지에 배치되며, 별도의 정렬 구조가 없습니다. IAM(Index Allocation Map) 페이지가 테이블에 속한 모든 익스텐트를 추적합니다.
-- 힙 테이블 생성 예시 (클러스터형 인덱스 없음)
CREATE TABLE dbo.HeapTest (
ID INT NOT NULL,
Val VARCHAR(100) NOT NULL
);
-- 클러스터형 인덱스를 추가하지 않으면 이 테이블은 힙으로 유지됩니다.
-- 힙인지 확인: index_id = 0이면 힙
SELECT
OBJECT_NAME(object_id) AS table_name,
index_id,
type_desc
FROM sys.indexes
WHERE OBJECT_NAME(object_id) = 'HeapTest';
힙에서의 행 주소는 RID(Row Identifier): FileID:PageID:SlotNumber입니다. 비클러스터형 인덱스는 리프 레벨에 이 RID를 저장해 힙 행을 직접 가리킵니다.
클러스터형 인덱스
클러스터형 인덱스가 있는 테이블의 데이터는 인덱스 키 순서대로 물리적으로 정렬된 B-트리 구조에 저장됩니다. 리프 레벨 페이지가 곧 데이터 페이지입니다. 행의 물리적 위치가 곧 클러스터 키 값으로 결정됩니다.
| 특성 | 힙 | 클러스터형 인덱스 |
|---|---|---|
| 물리적 정렬 | 없음 | 클러스터 키 기준 정렬 |
| 리프 레벨 | IAM 기반 임의 배치 | 실제 데이터 행 포함 |
| 행 주소 | RID (FileID:PageID:Slot) | 클러스터 키 값 |
| 비클러스터형 인덱스 포인터 | RID | 클러스터 키 |
| 범위 스캔 | 비효율 (분산) | 효율 (연속 페이지) |
⚠️ 주의 클러스터형 인덱스의 리프 레벨은 데이터 자체이므로, 테이블당 하나만 존재할 수 있습니다.
PRIMARY KEY를 정의하면 기본적으로 클러스터형 인덱스가 생성됩니다.
B-트리의 루트·중간·리프 레벨과 페이지 탐색 경로
SQL Server의 인덱스(클러스터형 및 비클러스터형)는 **B+ 트리(B-Plus Tree)**로 구현됩니다. 모든 실제 데이터(또는 RID 포인터)는 리프 레벨에만 존재하고, 중간 레벨과 루트 레벨은 탐색 경로를 안내하는 키와 페이지 포인터만 담습니다.
[ Root Page ]
| 500 | 1000 |
/ | \
[Mid] [Mid] [Mid]
|100|300| |700|900| |1200|
/ | \ / | \ / \
[L][L][L] [L][L][L] [L] [L] ← Leaf Pages (실제 데이터 또는 포인터)
리프 페이지들은 이중 연결 리스트(Doubly Linked List)로 연결되어 있어 범위 스캔 시 순차적으로 탐색할 수 있습니다. 루트에서 리프까지 탐색하는 과정을 Seek, 리프 레벨을 수평으로 훑는 것을 Scan이라 합니다.
-- 실행 계획에서 탐색 경로 확인
SET STATISTICS IO ON;
SELECT CustomerID, OrderDate
FROM dbo.Orders
WHERE CustomerID = 12345;
-- 실행 계획에서 Index Seek가 보이면 루트→리프 경로를 타는 것입니다.
SET STATISTICS IO OFF;
-- DBCC IND로 테이블의 페이지 구조 확인
-- 인자: 데이터베이스명, 테이블명, 인덱스ID (-1이면 모든 인덱스)
DBCC IND('AdventureWorks2019', 'Sales.SalesOrderHeader', -1);
/*
결과 컬럼 설명:
PageFID : 파일 ID
PagePID : 페이지 ID
IndexID : 인덱스 번호 (1 = 클러스터형)
PageType : 1=데이터, 2=인덱스, 10=IAM (PFS/GAM/SGAM 등 다른 할당 페이지 유형도 나타날 수 있음)
IndexLevel : 0=리프, 1=중간, 2=루트 (IAM/할당 페이지에서는 NULL로 표시될 수 있음)
NextPagePID : 다음 리프 페이지 (연결 리스트)
PrevPagePID : 이전 리프 페이지
*/
💡 TIP
DBCC IND는 비공식 명령이지만 내부 구조 학습용으로 널리 쓰입니다. 프로덕션에서는sys.dm_db_database_page_allocations(SQL Server 2012+)를 대안으로 사용할 수 있습니다.
페이지 분할(Page Split)이 일어나는 원리와 성능 영향
리프 페이지에 새로운 행을 삽입해야 하는데 해당 페이지에 여유 공간이 없다면 페이지 분할이 발생합니다. SQL Server는 기존 페이지의 행을 절반으로 나눠 새 페이지에 옮기고, 두 페이지에 각각 절반씩 데이터를 배치합니다.
삽입 전:
┌─────────────────────────┐
│ [100][200][300][400][X] │ ← 페이지 꽉 참, X 삽입 불가
└─────────────────────────┘
페이지 분할 후:
┌─────────────────┐ ┌─────────────────┐
│ [100][200] │ │ [300][400][X] │
└─────────────────┘ └─────────────────┘
페이지 분할의 성능 영향은 다음과 같습니다.
- 쓰기 성능 저하: 새 페이지 할당 + 행 이동 + 여러 페이지의 동시 잠금이 필요합니다.
- 외부 단편화 증가: 논리적으로 연속된 리프 페이지들이 물리적으로 불연속 위치에 배치되어 순차 I/O 효율이 떨어집니다.
- 내부 단편화 증가: 분할 후 각 페이지가 50%만 채워지므로 같은 데이터를 저장하는 데 더 많은 페이지가 필요합니다.
⚠️ 주의 IDENTITY 기반의 순차 증가 키(1, 2, 3, ...)는 항상 마지막 페이지에만 삽입되므로 분할이 거의 발생하지 않습니다. 반면 GUID(NEWID())나 무작위 키는 임의의 위치에 삽입되어 분할을 자주 유발합니다. 클러스터 키 선택이 중요한 이유입니다.
-- 페이지 분할 횟수 모니터링 (Performance Monitor 카운터)
SELECT
object_name,
counter_name,
cntr_value AS page_splits_per_sec
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page Splits/sec'
AND object_name LIKE '%Access Methods%';
Fill Factor와 인덱스 단편화의 발생 메커니즘
Fill Factor
Fill Factor는 인덱스를 생성하거나 재구성할 때 리프 레벨 페이지를 얼마나 채울지 결정하는 백분율 값입니다. 예를 들어 FILLFACTOR = 80이면 각 리프 페이지를 80%만 채우고 20%를 빈 공간으로 남겨둡니다.
-- Fill Factor 80으로 인덱스 생성
CREATE INDEX IX_Orders_CustomerID
ON dbo.Orders (CustomerID)
WITH (FILLFACTOR = 80);
-- Fill Factor 90으로 인덱스 재구성 (REBUILD 시 적용)
ALTER INDEX IX_Orders_CustomerID
ON dbo.Orders
REBUILD WITH (FILLFACTOR = 90);
Fill Factor는 인덱스 생성/REBUILD 시점에만 적용되며, 이후 DML 연산으로 페이지가 점점 채워져도 자동으로 유지되지는 않습니다.
내부 단편화 vs 외부 단편화
| 구분 | 정의 | 측정 지표 | 영향 |
|---|---|---|---|
| 내부 단편화 | 페이지 내 빈 공간이 많음 | avg_page_space_used_in_percent 낮음 | 동일 데이터 저장에 페이지 수 증가 → 더 많은 I/O |
| 외부 단편화 | 논리적 페이지 순서와 물리적 위치 불일치 | avg_fragmentation_in_percent 높음 | 순차 I/O 불가 → 랜덤 I/O로 처리 → 느린 스캔 |
-- sys.dm_db_index_physical_stats로 단편화 수준 확인
SELECT
OBJECT_NAME(ips.object_id) AS table_name,
i.name AS index_name,
ips.index_type_desc,
ips.index_depth,
ips.index_level,
ips.avg_fragmentation_in_percent, -- 외부 단편화 (%)
ips.avg_page_space_used_in_percent, -- 내부 채움률 (%)
ips.page_count
FROM sys.dm_db_index_physical_stats(
DB_ID(), -- 현재 데이터베이스
NULL, -- 모든 테이블
NULL, -- 모든 인덱스
NULL, -- 모든 파티션
'DETAILED' -- 상세 모드 (SAMPLED는 대용량에서 빠름)
) ips
JOIN sys.indexes i
ON ips.object_id = i.object_id
AND ips.index_id = i.index_id
WHERE ips.index_level = 0 -- 리프 레벨만
AND ips.page_count > 100 -- 소규모 인덱스 제외
ORDER BY ips.avg_fragmentation_in_percent DESC;
💡 TIP
'SAMPLED'모드는 대규모 테이블에서'DETAILED'보다 훨씬 빠르게 결과를 반환합니다. 프로덕션 환경의 정기 점검 쿼리에는'SAMPLED'를 권장합니다.
DBCC IND / sys.dm_db_index_physical_stats로 내부 구조 관찰하기
실제 테이블을 만들고 데이터를 넣어보면서 내부 구조가 어떻게 변하는지 관찰해 봅니다.
-- 실습 환경 준비
USE tempdb;
GO
CREATE TABLE dbo.PageDemo (
ID INT IDENTITY(1,1) NOT NULL,
Name CHAR(100) NOT NULL DEFAULT REPLICATE('A', 100),
CONSTRAINT PK_PageDemo PRIMARY KEY CLUSTERED (ID)
);
GO
-- 1000행 삽입
INSERT INTO dbo.PageDemo (Name)
SELECT TOP 1000 REPLICATE('X', 100)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;
GO
-- 1) DBCC IND로 페이지 목록 확인
DBCC IND(tempdb, 'dbo.PageDemo', 1);
/*
IndexLevel = 0인 행: 리프(데이터) 페이지
IndexLevel = 1인 행: 루트 또는 중간 레벨 인덱스 페이지
*/
-- 2) 특정 페이지의 실제 내용 확인 (위에서 얻은 PagePID 사용)
DBCC PAGE(tempdb, 1, <PagePID>, 3) WITH TABLERESULTS;
-- 3번 출력 모드: 행 단위로 파싱된 내용 표시
-- 3) 단편화 수준 측정
SELECT
index_type_desc,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent,
page_count,
index_depth
FROM sys.dm_db_index_physical_stats(
DB_ID('tempdb'),
OBJECT_ID('dbo.PageDemo'),
1, NULL, 'DETAILED'
)
WHERE index_level = 0;
GO
-- 단편화 유발 실험: 무작위 위치에 삽입·삭제 반복
DELETE FROM dbo.PageDemo WHERE ID % 3 = 0;
INSERT INTO dbo.PageDemo (Name)
SELECT TOP 500 REPLICATE('Y', 100)
FROM sys.all_objects;
-- 단편화 재측정
SELECT
avg_fragmentation_in_percent,
avg_page_space_used_in_percent,
page_count
FROM sys.dm_db_index_physical_stats(
DB_ID('tempdb'),
OBJECT_ID('dbo.PageDemo'),
1, NULL, 'SAMPLED'
)
WHERE index_level = 0;
REBUILD vs REORGANIZE의 동작 차이와 선택 기준
인덱스 단편화를 해소하는 방법에는 두 가지가 있습니다.
REBUILD (인덱스 재생성)
- B-트리를 처음부터 새로 구성합니다.
- 기존 인덱스를 버리고 새 페이지에 데이터를 재배치하므로 내부·외부 단편화 모두 완전히 제거됩니다.
- Fill Factor가 다시 적용됩니다.
ONLINE = ON옵션을 사용하면 Enterprise Edition에서 작업 중 테이블에 대한 DML을 허용합니다(잠금 최소화).- 트랜잭션 로그 사용량이 많습니다.
-- ✅ 단편화 30% 이상일 때 권장
ALTER INDEX IX_Orders_CustomerID ON dbo.Orders
REBUILD WITH (FILLFACTOR = 80, ONLINE = ON, SORT_IN_TEMPDB = ON);
-- 테이블의 모든 인덱스 재구성
ALTER INDEX ALL ON dbo.Orders
REBUILD WITH (FILLFACTOR = 80, ONLINE = ON);
REORGANIZE (인덱스 재구성)
- 리프 레벨 페이지를 논리적 순서에 맞게 물리적으로 재정렬합니다.
- 단일 페이지씩 이동하는 방식이라 트랜잭션 로그를 적게 사용하고 언제든 중단할 수 있습니다.
- 내부 단편화(빈 페이지 공간)를 완전히 제거하지는 못합니다. REORGANIZE는 FILLFACTOR 옵션을 받을 수 없어 Fill Factor 값을 변경하지는 못하지만, 메타데이터에 저장된 기존 Fill Factor를 목표로 리프 페이지를 압축(compact)하므로 내부 단편화를 어느 정도 줄입니다.
- 항상 온라인으로 동작합니다(별도 옵션 불필요).
-- ✅ 단편화 5~30%일 때 권장
ALTER INDEX IX_Orders_CustomerID ON dbo.Orders
REORGANIZE;
-- LOB 데이터(text, image 등)의 단편화도 함께 처리
ALTER INDEX IX_Orders_CustomerID ON dbo.Orders
REORGANIZE WITH (LOB_COMPACTION = ON);
선택 기준 요약
-- 실무에서 자주 사용하는 자동화 유지관리 쿼리 패턴
SELECT
OBJECT_NAME(ips.object_id) AS table_name,
i.name AS index_name,
ips.avg_fragmentation_in_percent,
CASE
WHEN ips.avg_fragmentation_in_percent < 5 THEN '유지 (작업 불필요)'
WHEN ips.avg_fragmentation_in_percent < 30 THEN 'REORGANIZE'
ELSE 'REBUILD'
END AS recommended_action
FROM sys.dm_db_index_physical_stats(
DB_ID(), NULL, NULL, NULL, 'SAMPLED'
) ips
JOIN sys.indexes i
ON ips.object_id = i.object_id
AND ips.index_id = i.index_id
WHERE ips.index_level = 0
AND ips.page_count > 100
AND i.index_id > 0; -- 힙(index_id=0) 제외
| 조건 | 권장 작업 |
|---|---|
| 단편화 < 5% | 작업 불필요 |
| 단편화 5% ~ 30% | REORGANIZE |
| 단편화 > 30% | REBUILD |
| 페이지 수 < 100 | 작업 불필요 (비용 대비 효과 없음) |
⚠️ 주의 REBUILD는 Standard Edition에서
ONLINE = OFF(기본값)로 실행되면 전체 테이블 잠금이 걸립니다. 운영 시간대를 반드시 피하거나ONLINE = ON을 사용하세요(Enterprise Edition 필요).
요약
- SQL Server는 8KB 페이지를 I/O의 최소 단위로 사용하며, 8개 페이지가 묶인 64KB 익스텐트 단위로 공간을 할당합니다.
- 힙은 정렬 없이 행을 배치하고 RID로 행을 식별하며, 클러스터형 인덱스는 리프 레벨에 클러스터 키 순서대로 데이터를 저장합니다.
- B+ 트리의 탐색은 루트 → 중간 레벨 → 리프 순으로 이루어지며, 리프 페이지들은 이중 연결 리스트로 이어져 범위 스캔을 지원합니다.
- 페이지 분할은 무작위 키 삽입 시 빈번하게 발생하며 쓰기 성능 저하와 단편화를 유발합니다. Fill Factor로 분할 빈도를 조절할 수 있습니다.
- 단편화 수준은
sys.dm_db_index_physical_stats로 측정하고, 5~30%면 REORGANIZE, 30% 이상이면 REBUILD를 선택합니다.
연습문제
-
tempdb에 클러스터형 인덱스가 없는 힙 테이블dbo.HeapExercise를 만들고 100개의 행을 삽입한 뒤,DBCC IND와sys.dm_db_index_physical_stats를 사용해 페이지 수와index_id값을 확인하세요. 힙과 클러스터형 인덱스를 구분하는index_id값은 각각 무엇인가요?힌트 힙은
sys.indexes에서index_id = 0으로 나타납니다. -
위
dbo.HeapExercise에 클러스터형 인덱스를 추가한 뒤,DBCC IND의 결과가 어떻게 달라지는지IndexLevel컬럼을 중심으로 비교하세요.힌트
CREATE CLUSTERED INDEX를 사용하고, 추가 전/후의DBCC IND결과를 메모해 두세요. -
sys.dm_db_index_physical_stats를 사용해 현재 데이터베이스의 인덱스 중 외부 단편화(avg_fragmentation_in_percent)가 가장 높은 인덱스 3개를 조회하는 쿼리를 작성하세요. 단, 페이지 수가 50 이하인 소규모 인덱스는 제외하세요.힌트
ORDER BY avg_fragmentation_in_percent DESC와TOP 3을 조합하세요. -
Fill Factor가 인덱스 유지관리에서 어떤 역할을 하는지 설명하고, 주로 INSERT가 많은 테이블에 적합한 Fill Factor 범위(낮음/높음)와 그 이유를 서술하세요. 또한 Fill Factor 70으로 인덱스를 REBUILD하는 SQL 문을 작성하세요.
힌트 Fill Factor가 낮을수록 빈 공간이 많아져 분할이 줄어드는 대신 페이지 수가 늘어납니다.
💡 연습문제 풀이
불러오는 중…
댓글 0
“MSSQL 심화” 강좌에 대한 댓글입니다.