SQL Server 2008 物件儲存元數據核心：sys.partitions、sys.allocation_units、sys.system_internals_allocation_units 深度解析

SQL Server 物件儲存的「真相」藏在三張系統檢視中：sys.partitions (分區層級)、sys.allocation_units (配置單元層級)、sys.system_internals_allocation_units (內部頁面指標層級)。本文詳細解析三張檢視的欄位含義、關聯邏輯，並透過兩個實測案例 (含叢集/非叢集索引表、堆表) 展示如何從元數據重構完整儲存拓撲。

三大核心檢視結構對照

1. sys.partitions — 分區層級 (表/索引的每個分區一列)

2. sys.allocation_units — 配置單元層級 (每個配置單元一列)

3. sys.system_internals_allocation_units — 內部擴充 (未文件化)

在 sys.allocation_units 基礎上新增三個頁面指標：

• first_page：首資料頁 (檔案:頁面)
• root_page：索引根頁 (堆為 0)
• first_iam_page：首個 IAM 頁面

這三個指標是 DBCC PAGE 深度分析的**精確起點**。

關聯邏輯圖解

sys.objects (object_id)
    │
    ├─ sys.indexes (object_id, index_id)  -- 索引定義
    │
    └─ sys.partitions (object_id, index_id, partition_number)
         │
         ├─ hobt_id  ──────────────────┐
         │                              │
         ▼                              ▼
sys.allocation_units (type=1,3)   sys.allocation_units (type=2)
   container_id = hobt_id           container_id = partition_id
   IN_ROW_DATA /                    LOB_DATA
   ROW_OVERFLOW_DATA
         │
         ▼
sys.system_internals_allocation_units (同 container_id 關聯)
   + first_page, root_page, first_iam_page

實測案例一：含叢集索引、非叢集索引、LOB 欄位的表

CREATE TABLE test (
    a INT, b VARCHAR(20), c TEXT
);
CREATE UNIQUE CLUSTERED INDEX idx_test ON test(a);
CREATE INDEX ix_test ON test(b);
INSERT INTO test VALUES (1,'a','aaa'), (2,'b','bbb'), (3,'c','ccc');

查詢腳本

-- 1. 物件與索引
SELECT name, object_id, index_id, type_desc FROM sys.indexes WHERE object_id = OBJECT_ID('test');

-- 2. 分區
SELECT partition_id, object_id, index_id, partition_number, hobt_id, rows
FROM sys.partitions WHERE object_id = OBJECT_ID('test');

-- 3. 配置單元 (標準檢視)
SELECT au.allocation_unit_id, au.type_desc, au.container_id,
       au.data_space_id, au.total_pages, au.used_pages, au.data_pages
FROM sys.allocation_units au
JOIN sys.partitions p
    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.object_id = OBJECT_ID('test');

-- 4. 配置單元 (內部檢視，含頁面指標)
SELECT au.allocation_unit_id, au.type_desc, au.container_id,
       au.filegroup_id, au.total_pages, au.used_pages, au.data_pages,
       au.first_page, au.root_page, au.first_iam_page
FROM sys.system_internals_allocation_units au
JOIN sys.partitions p
    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.object_id = OBJECT_ID('test');

結果解讀

• sys.indexes：兩列 → 叢集索引 (index_id=1) + 非叢集索引 (index_id=2)。
• sys.partitions：兩列 → 叢集索引分區 (index_id=1, hobt_id=...) + 非叢集索引分區 (index_id=2, hobt_id=...)。叢集索引即表本身，其 hobt_id 對應資料列儲存。
• 配置單元 (3 列)：
  1. IN_ROW_DATA (type=1, container=叢集 hobt_id) → 叢集索引資料列 (含 a, b, c 的行內部分)
  2. IN_ROW_DATA (type=1, container=非叢集 hobt_id) → 非叢集索引鍵值 (b) + 叢集鍵查找值 (a)
  3. LOB_DATA (type=2, container=叢集 partition_id) → TEXT 欄位資料 (行外 LOB 樹)

關鍵洞見： LOB_DATA 配置單元的 container_id 指向 叢集索引的 partition_id (而非 hobt_id)，因為 LOB 資料歸屬於「表」這個邏輯實體，不隸屬於某個特定索引的 B 樹。

實測案例二：堆表 + LOB 欄位

CREATE TABLE heaptest (a INT, b VARCHAR(20), c TEXT);
INSERT INTO heaptest VALUES (1,'a','aaa'), (2,'b','bbb'), (3,'c','ccc');

結果解讀

• sys.indexes：單列 → 堆表 (index_id=0, type=HEAP)。
• sys.partitions：單列 → 堆分區 (index_id=0, hobt_id=...)。
• 配置單元 (3 列)：
  1. IN_ROW_DATA (container=堆 hobt_id) → 堆表資料列
  2. ROW_OVERFLOW_DATA (container=堆 hobt_id) → **無資料** (本案例 VARCHAR(20) 未溢位)，但配置單元存在 (預分配機制)
  3. LOB_DATA (container=堆 partition_id) → TEXT 欄位 LOB 樹

差異點： 堆表的 sys.partitions.index_id=0，hobt_id 即堆的 B 樹 ID；LOB_DATA 同樣綁定 partition_id。

DBA 實務查詢模板

1. 取得任意表的完整儲存拓撲

DECLARE @TableName SYSNAME = 'YourTable';
DECLARE @ObjID INT = OBJECT_ID(@TableName);

SELECT
    o.name AS ObjectName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    p.partition_number,
    p.hobt_id,
    p.partition_id,
    p.rows,
    au.type_desc AS AllocUnitType,
    au.allocation_unit_id,
    au.total_pages,
    au.used_pages,
    au.data_pages,
    iua.first_page,
    iua.root_page,
    iua.first_iam_page
FROM sys.objects o
JOIN sys.indexes i ON o.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
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)
LEFT JOIN sys.system_internals_allocation_units iua
    ON au.allocation_unit_id = iua.allocation_unit_id
WHERE o.object_id = @ObjID
ORDER BY i.index_id, p.partition_number, au.type;

2. 尋找最大的 LOB / 行溢位配置單元 (容量規劃)

SELECT TOP 20
    OBJECT_NAME(p.object_id) AS TableName,
    i.name AS IndexName,
    au.type_desc,
    au.total_pages * 8.0 / 1024 AS Size_MB,
    au.used_pages * 8.0 / 1024 AS Used_MB,
    iua.first_iam_page
FROM sys.allocation_units au
JOIN sys.partitions p
    ON (au.type IN (1,3) AND au.container_id = p.hobt_id)
    OR (au.type = 2 AND au.container_id = p.partition_id)
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
LEFT JOIN sys.system_internals_allocation_units iua ON au.allocation_unit_id = iua.allocation_unit_id
WHERE au.type_desc IN ('LOB_DATA', 'ROW_OVERFLOW_DATA')
  AND au.used_pages > 0
ORDER BY au.used_pages DESC;

3. 精準定位 DBCC PAGE 分析起始頁面

-- 給定表名與索引名，取得首資料頁、根頁、首 IAM 頁
DECLARE @TableName SYSNAME = 'YourTable', @IndexName SYSNAME = 'YourIndex';
SELECT
    iua.first_page, iua.root_page, iua.first_iam_page
FROM sys.indexes i
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
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)
JOIN sys.system_internals_allocation_units iua ON au.allocation_unit_id = iua.allocation_unit_id
WHERE i.object_id = OBJECT_ID(@TableName) AND i.name = @IndexName
  AND au.type = 1; -- IN_ROW_DATA

總結

1. sys.partitions.hobt_id 是 IN_ROW_DATA / ROW_OVERFLOW_DATA 的關聯鍵；partition_id 是 LOB_DATA 的關聯鍵。
2. 一個索引/堆的每個分區 → 1 個 hobt_id → 1 個 IN_ROW_DATA 配置單元 (+ 可選 ROW_OVERFLOW_DATA)；LOB_DATA 則按 partition_id 歸屬於「表」。
3. sys.system_internals_allocation_units 雖屬內部檢視，但提供 first_page/root_page/first_iam_page 三大頁面指標，是**頁面級深度分析的唯一權威來源**。
4. 掌握「物件 → 索引 → 分區 → 配置單元 → 頁面」這條鏈路，你就能從元數據層面精確重構任何表的物理儲存拓撲，為 DBCC 診斷、效能調優、損壞恢復提供精確導航。