您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

深入理解 SQL Server 檔案儲存架構：頁與應用實戰

編輯：MySQL 資料庫相關知識

前言

SQL Server 儲存引擎的基本單位是「頁」，理解頁結構、頁類型、頁面分配機制，是效能調優、碎片整理、損壞修復、容量規劃的基石。本文深入剖析 8KB 頁架構、九大頁類型、PFS/GAM/SGAM/IAM 分配位圖、Extent 混合/一致區、以及實戰診斷查詢。

一、頁架構概覽

大小固定 8 KB (8,192 位元組)，所有資料/索引/系統結構皆以頁為單位讀寫。
檔案結構：.mdf (主資料檔)、.ndf (次要資料檔)、.ldf (記錄檔) 皆由連續頁組成，頁號從 0 起算。
頁頭：96 位元組，含頁號、檔案號、物件索引 ID、頁類型、LSN、空閒空間指標、頁面校驗和等。
資料區：8,096 位元組，儲存實際資料列、索引鍵、系統記錄。
行位移陣列：頁尾反向增長，每列 2 位元組，指向資料區內列起始位置，支援頁內列移動不更新索引。

二、九大頁類型詳細解析

頁類型	代碼	名稱	用途	關鍵特徵
1	0x01	Data Page	堆表/叢集索引葉層資料列	最常見，含行位移陣列，支援轉寄指標
2	0x02	Index Page	非叢集索引葉層、所有索引中間層	只存索引鍵+書籤，無完整資料列
3	0x03	Text/Image Page	LOB 資料 (text/ntext/image/xml/varchar(max) 等)	樹狀結構，根節點在資料列，支援部分更新
4	0x04	GAM (Global Allocation Map)	記錄 Extent 分配狀態 (全域)	每 4GB (64,000 Extents) 一頁，位圖：0=已分配,1=空閒
5	0x05	SGAM (Shared Global Allocation Map)	記錄混合 Extent 分配狀態	同 4GB 間隔，位圖：1=混合且有空閒頁,0=否
6	0x06	PFS (Page Free Space)	記錄頁面空閒空間百分比	每 8,088 頁一頁，每頁 1 位元組：0=空,1=1-50%,2=51-80%,3=81-95%,4=96-100%
7	0x07	IAM (Index Allocation Map)	追蹤物件擁有的 Extents/頁	每個堆/索引分區至少一個 IAM 鏈，串聯 Extent 範圍
8	0x08	BCM (Bulk Changed Map)	大量載入後已變更的 Extents	支援差異備份、CDC，位圖標記
9	0x09	DCM (Differential Changed Map)	上次完整備份後已變更的 Extents	支援差異備份，位圖標記

三、Extent (區) 與分配機制

Extent = 8 連續頁 = 64 KB，分配最小單位。
Uniform Extent (一致區)：8 頁全屬同一物件，減少爭用、利於大表掃描。
Mixed Extent (混合區)：8 頁可屬最多 8 個物件，新建小表/索引預設使用，節省空間。
分配演算法：
1. 新物件 → 從混合區分配首頁（PFS/GAM/SGAM 協同定位）。
2. 物件增長到 8 頁 → 後續改用一致區（GAM 定位）。
3. 每 4GB 檔案區間固定位置存放 GAM(第 2 頁)、SGAM(第 3 頁)、PFS(第 1 頁)、後續每 8,088 頁一個 PFS。

四、關鍵系統頁佈局 (每檔案前幾頁固定)

頁號	類型	說明
0	File Header	檔案屬性、版本、檢查點 LSN、最大檔案大小
1	PFS	第一個 PFS 頁，覆蓋頁 0-8,087
2	GAM	第一個 GAM 頁，覆蓋前 4GB
3	SGAM	第一個 SGAM 頁，覆蓋前 4GB
4-5	DCM/BCM	第一組差異/大量變更位圖
6	Boot Page (僅主檔)	資料庫啟動資訊、版本、相容等級、檢查點

五、實戰診斷查詢

1. 查看物件頁面分佈 (sys.dm_db_database_page_allocations)

SELECT 
  allocated_page_page_id AS page_id,
  page_type_desc,
  extent_page_id,
  is_allocated,
  is_iam_page,
  is_mixed_page_allocation,
  previous_page_page_id,
  next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.Orders'), NULL, NULL, 'DETAILED');

2. 檢視頁內容 (DBCC PAGE) — 僅診斷用，生產謹慎

DBCC TRACEON(3604);  -- 輸出到客戶端
DBCC PAGE (db_id, file_id, page_id, 3);  -- 3 = 完整輸出含資料列
DBCC TRACEOFF(3604);

3. 碎片分析

SELECT 
  OBJECT_NAME(ips.object_id) AS table_name,
  i.name AS index_name,
  ips.index_type_desc,
  ips.avg_fragmentation_in_percent,
  ips.fragment_count,
  ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10 AND ips.page_count > 100
ORDER BY ips.avg_fragmentation_in_percent DESC;

4. 空間使用詳情

EXEC sp_spaceused 'dbo.Orders', @updateusage = 'TRUE';
-- 或查詢分配單元
SELECT 
  OBJECT_NAME(p.object_id) AS table_name,
  au.type_desc,
  au.total_pages,
  au.used_pages,
  au.data_pages
FROM sys.allocation_units au
JOIN sys.partitions p ON au.container_id = p.partition_id
WHERE OBJECT_NAME(p.object_id) = 'Orders';

六、應用層面最佳實踐

叢集索引鍵選擇：窄、唯一、遞增 (IDENTITY/BIGINT)，避免頁分裂、保持葉層順序。
避免大量隨機 INSERT：導致頁分裂、碎片、書籤查找成本激增；考慮排序輸入、批次大小、填充因子。
填充因子：FILLFACTOR = 80-90 為高更新表預留空間，減少頁分裂；唯讀/僅追加表設 100。
LOB 資料策略：varchar(max) 小於 8KB 存行內，大於移至 Text/Image 頁；sp_tableoption 'large value types out of row' 強制行外。
定期重建/重組索引：碎片 >30% REBUILD (ONLINE=ON)，10-30% REORGANIZE；維護窗口執行。
監控 PFS/GAM 爭用：高併發建表/大量插入可能導致 SGAM/PFS 熱頁，考慮多檔案檔案組 (每核 1 檔案) 分散分配。
備份策略結合 DCM/BCM：差異備份讀 DCM 定位變更 Extent，大量載入後 BCM 加速記錄，減少掃描。

七、總結

SQL Server 的 8KB 頁是儲存、索引、記錄、備份、恢復的統一度量單位。掌握 Data/Index/Text 頁儲存業務資料，GAM/SGAM/PFS/IAM 四大位圖管理空間分配，Extent 混合/一致區平衡小大物件，配合 dm_db_database_page_allocations、DBCC PAGE、dm_db_index_physical_stats 實戰診斷，即可從頁面層面透視資料庫行為，制定精準的效能、維護、容量策略。