SQL Server 2008 儲存引擎深度剖析：堆表、行溢位、LOB 大值類型的物理佈局

承接上篇系統檢視，本文進入頁面級物理結構：堆表 的 IAM 管理機制、行溢位 的 24 位元組指標結構、varchar(max)/varbinary(max) 與舊 text/image 的根本差異。所有分析基於 DBCC PAGE、DBCC IND、sys.system_internals_allocation_units 實測，還原儲存引擎最真實的面貌。

一、堆表：無序中的秩序 —— IAM 頁面地圖

堆表 = 無叢集索引的表。資料頁無雙向鏈表，唯一邏輯連接在 IAM (Index Allocation Map) 頁面。

實測環境建立

CREATE TABLE testheap (
    ID INT IDENTITY(1,1) NOT NULL,
    name VARCHAR(20) NOT NULL,
    type CHAR(100) NOT NULL,
    other VARCHAR(50) NOT NULL,
    describle VARCHAR(500) NULL
);
-- 插入 1000 列隨機資料
DECLARE @i INT = 1;
WHILE @i <= 1000 BEGIN
    INSERT INTO testheap(name,type,other,describle)
    VALUES('name'+CAST(@i AS VARCHAR(3)), REPLICATE(@i%4,100), FLOOR(RAND()*10), NULL);
    SET @i += 1;
END

配置單元概覽

SELECT total_pages, used_pages, data_pages,
       dbo.f_get_page(first_page)   AS FirstPageAddr,
       dbo.f_get_page(root_page)    AS RootPageAddr,
       dbo.f_get_page(first_iam_page) AS IAMPageAddr
FROM sys.system_internals_allocation_units
WHERE container_id IN (
    SELECT partition_id FROM sys.partitions
    WHERE object_id = OBJECT_ID('testheap')
);

結果： 總 25 頁、已用 18 頁、資料頁 17 頁、IAM 頁 1 頁 (1:119)、首資料頁 (1:224)。

DBCC IND 完整頁面分佈

CREATE TABLE dbo.tablepage (...頁面欄位...);
TRUNCATE TABLE dbo.tablepage;
INSERT INTO dbo.tablepage EXEC('DBCC IND(testdb, testheap, 1)');
SELECT PagePID, IAMPID, IndexID, PageType, IndexLevel, NextPagePID, PrevPagePID
FROM dbo.tablepage;

• PageType=10 IAM 頁 (1:119)，PageType=1 資料頁 (17 頁)。
• IndexID=0 確認為堆，IndexLevel=NULL (IAM) / 0 (葉級資料頁)。
• 資料頁間無 Next/Prev 鏈接 (均為 0)，完全仰賴 IAM 定位。
• 前 8 頁為混合區 (Mixed Extent) 零星分佈，後 16 頁為整區連續配置。

IAM 頁面結構

IAM 頁包含 配置位圖：每位對應一個區 (64KB/8頁)。掃描堆表時，儲存引擎讀取 IAM 位圖 → 找到已配置區 → 讀取區內所有資料頁。這也是 SELECT * FROM heap 無序輸出的根本原因。

資料頁結構解剖 (頁頭 96 位元組 + 資料列 + 位移陣列)

-- DBCC PAGE(testdb, 1, 224, 2) 輸出關鍵欄位
m_pageId = (1:224)          -- 頁面編號
m_type = 1                  -- 資料頁
m_level = 0                 -- 葉級
m_slotCnt = 62              -- 頁面列數
m_freeCnt = 293             -- 剩餘空間位元組
m_freeData = 7775           -- 從頁首到最後資料位元組
pminlen = 108               -- 固定長度部分 (ID 4 + type 100 + 2*2 管理位元組)
m_prevPage = (0:0), m_nextPage = (0:0)  -- 堆表無鏈接

資料列結構逐位元組解讀 (Slot 0)

-- 狀態位元組 A (30h = 00110000b)
Bit 4=1: 有變長欄位  Bit 5=1: 有 NULL 位圖 (SQL 2005+ 永遠有)
-- 狀態位元組 B: 00 (未使用)
-- 固定長度長度: 6Ch = 108 位元組
-- 固定資料: ID=1 (01 00 00 00) + type='111...' (100 位元組)
-- 欄位總數: 05 00 (5 個欄位)
-- NULL 位圖: F0h = 11110000b (前 4 欄非 NULL, 第 5 欄 NULL, 6-8 未用)
-- 變長欄位計數: 02 00 (2 個: name, other)
-- 變長欄位結束位移: 7A00h=122 (name 結束), 7B00h=123 (other 結束)
-- 變長資料: 6E 61 6D 65 31 ('name1'), 38 ('8')

UPDATE 導致列前移現象

UPDATE testheap SET describle='abc' WHERE ID=1;

• 原位置資料**未刪除**，新列寫入頁面尾部 (位移陣列指向新位置)。
• NULL 位圖變化: F0→E0 (第 5 欄變非 NULL)。
• 變長欄位計數 2→3，結束位移各 +2 (新增 2 位元組位移)。
• 這是 **「就地更新」失敗、轉為「刪除+插入」** 的典型證據 (頁面剩餘空間不足以容納擴展後的列)。

DELETE 操作

DELETE FROM testheap WHERE ID IN (2,3);

• 資料實體**保留在原位**，僅從位移陣列移除 Slot 1、2 → 邏輯刪除，物理未清除 (Ghost Record 機制，後續由 Ghost Cleanup Task 異步清理)。

二、行溢位：突破 8060 位元組限制的 24 位元組指標

當列內變長欄位總長超過 8060 位元組，SQL Server 將最大變長欄位移至 ROW_OVERFLOW_DATA 配置單元。

實測建立

CREATE TABLE testOverFlow (
    ID INT IDENTITY(1,1),
    NAME1 VARCHAR(5000),
    NAME2 VARCHAR(5000)
);
INSERT INTO testOverFlow(NAME1,NAME2)
SELECT REPLICATE('A',5000), REPLICATE('AB',2500)
UNION ALL SELECT REPLICATE('B',5000), REPLICATE('BA',2500);

列內資料結構 (NAME2 溢位)

NAME2 在列內僅保留 24 位元組溢位指標：

02 00 00 00 01 00 00 00 9D 75 00 00 88 13 00 00 77 CC 00 00 01 00 00 00 00
解析:
02 00 00 00  -- 溢位欄位類型 (TEXT/IMAGE/OVERFLOW)
01 00 00 00  -- 節點類型
9D 75 00 00  -- LOB 更新版本號
88 13 00 00  -- 未知/保留
77 CC 00 00  -- 欄位長度 (5000 = 0x1388)
01 00 00 00  -- 行溢位指標: 檔案 1, 頁面 0xCC77 = 52343, Slot 0
00 00 00 00  -- 保留

溢位頁面 (1:52343) 結構

LOB 資料頁 (PageType=3)，含：狀態位元組、長度、關聯 ID (匹配列內指標)、實際資料內容。

08 00 96 13 00 00 9D 75 00 00 00 00 03 00 ...
狀態A 狀態B 長度    關聯ID   未知   類型(LOB資料列)  實際資料...

查詢溢位列時：讀取列內指標 → 定位溢位頁面 → 驗證關聯 ID → 回傳資料。**雙重 I/O 開銷**，故大量存取長字串建議改用 varchar(max) 配合 sp_tableoption 'large value types out of row' 控制。

三、varchar(max) vs text：大值類型的儲存革命

實測對比

CREATE TABLE testVARCHARMAX (ID INT IDENTITY, name VARCHAR(20), remark VARCHAR(MAX));
CREATE TABLE testTEXT         (ID INT IDENTITY, name VARCHAR(20), remark TEXT);
-- 各插入 2 列，remark 2500 位元組

• testVARCHARMAX：無 LOB_DATA 頁面，資料全部在 IN_ROW_DATA 頁面 (2500 位元組 < 8000，頁面容許)。
• testTEXT：**強制產生 LOB_DATA 配置單元 (3 頁)**，列內留 16 位元組指標指向 LOB 樹根頁。

VARCHAR(MAX) 行內結構 (完整顯示)

• 結構與普通 VARCHAR(n) **完全一致**：變長欄位計數、結束位移、資料內容直接在列內。
• 無額外指標、無 LOB 樹遍歷、**單次 I/O 讀取完整列**。

TEXT 行外結構 (指標 + LOB 樹)

• 列內：D1 07 00 00 00 DC 00 00 01 00 01 00 → 16 位元組指標 (檔案/頁面/Slot)。
• LOB 樹根頁：Slot 0 存長度/類型/資料；Slot 1/2 存子頁指標 (B+ 樹結構)。
• 讀取需：列內指標 → 根頁 → 遍歷樹 → 資料頁，**多次 I/O**。

給 DBA 與開發者的建議

1. 新開發全面禁用 text/ntext/image，改用 varchar(max)/nvarchar(max)/varbinary(max)。
2. 大量小型 LOB (≤ 8KB)：預設行內儲存最佳，避免雙重 I/O。
3. 大量大型 LOB (> 8KB)：評估 sp_tableoption 'large value types out of row', 'ON'，避免頁面分裂、保持掃描效能。
4. 檢測行溢位比例：

   SELECT
       OBJECT_NAME(p.object_id) AS TableName,
       au.type_desc,
       au.total_pages, au.used_pages, au.data_pages
   FROM sys.partitions p
   JOIN sys.allocation_units au
       ON (au.type IN (1,3) AND au.container_id = p.hobt_id)
       OR (au.type = 2 AND au.container_id = p.partition_id)
   WHERE p.index_id IN (0,1) -- 堆或叢集索引
     AND au.type_desc IN ('ROW_OVERFLOW_DATA','LOB_DATA');

5. 頁面分裂監控：sys.dm_db_index_operational_stats 的 page_splits、page_merge_attempts，大值類型更新是主要誘因。

從堆表的 IAM 地圖、行溢位的 24 位元組指標，到大值類型的行內優先革命，SQL Server 2008 儲存引擎在「頁面 8KB」這一物理約束下，演化出精巧的多層次管理機制。深入理解這些結構，才能在效能調優、容量規劃、故障診斷中做出準確判斷。