SQL Server 2008 儲存架構深度解析：系統視圖視角的物理與邏輯對映

前言：為什麼要懂儲存架構？

理解頁面、配置單元、分區、檔案組的對應關係，是效能調校 (索引設計、分區策略、檔案配置)、故障排查 (DBCC 原理、頁面損壞修復)、容量規劃的基石。SQL Server 2008 引入豐富的目錄視圖 (`sys.*`)，取代舊版 `sysobjects`/`sysindexes` 等相容性視圖，提供更精確、規範的中繼資料存取。

邏輯層級架構總覽

Instance
 └─ Databases (master, model, msdb, tempdb, user DBs)
      └─ Filegroups (PRIMARY + User-defined)
           └─ Files (.mdf, .ndf, .ldf)
                └─ Allocation Units (IN_ROW_DATA, LOB_DATA, ROW_OVERFLOW_DATA)
                     └─ Partitions (Heap/B-tree per index)
                          └─ Pages (8 KB) → Extents (8 pages = 64 KB)

核心系統視圖分類與查詢範本

1. 伺服器與資料庫層級

-- 伺服器屬性
SELECT * FROM sys.servers WHERE server_id = 0;
-- 相容: sys.sysservers

-- 所有資料庫核心屬性
SELECT name, database_id, create_date, compatibility_level,
       collation_name, state_desc, recovery_model_desc,
       is_read_only, is_auto_shrink_on, is_auto_close_on
FROM sys.databases;
-- 相容: sys.sysdatabases

-- 檔案組
SELECT * FROM sys.filegroups;
-- 相容: sys.sysfilegroups

-- 所有資料庫檔案 (跨資料庫)
SELECT * FROM sys.master_files;
-- 相容: sys.sysaltfiles

-- 當前資料庫檔案
SELECT * FROM sys.database_files;
-- 相容: sys.sysfiles

-- 資料空間 (檔案組/分區配置)
SELECT * FROM sys.data_spaces;

2. 物件與結構描述層級

-- 所有使用者物件 (核心入口)
SELECT * FROM sys.objects WHERE type_desc = 'USER_TABLE' AND name = 'TestTable';

-- 含相關物件 (索引、約束、觸發器、統計)
SELECT o.* FROM sys.objects o
WHERE o.type_desc = 'USER_TABLE' AND o.name = 'TestTable'
   OR o.parent_object_id IN (
       SELECT object_id FROM sys.objects WHERE type_desc = 'USER_TABLE' AND name = 'TestTable'
   );

-- 欄位定義
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.TestTable');
-- 相容: sys.syscolumns

-- 索引結構
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.TestTable');
-- 相容: sys.sysindexes

-- 分區資訊 (每個索引/堆至少一個分區)
SELECT * FROM sys.partitions WHERE object_id = OBJECT_ID('dbo.TestTable');
-- 關鍵欄位: partition_id, hobt_id (Heap/B-tree 內部 ID)

-- 配置單元 (頁面配置單位)
SELECT * FROM sys.allocation_units;
-- 需配合 partitions 解讀:
-- TYPE=1 (IN_ROW_DATA)  → container_id = hobt_id
-- TYPE=2 (LOB_DATA)     → container_id = partition_id
-- TYPE=3 (ROW_OVERFLOW) → container_id = hobt_id

-- 結合查詢: 物件 → 分區 → 配置單元 → 頁面統計
SELECT au.type_desc, au.total_pages, au.used_pages, au.data_pages,
       p.partition_number, p.rows, i.name AS index_name, i.type_desc
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
WHERE p.object_id = OBJECT_ID('dbo.TestTable');

-- 分區統計 (DMV)
SELECT * FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.TestTable');

-- 索引欄位組成
SELECT ic.index_column_id, c.name, ic.is_descending_key, ic.is_included_column
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = OBJECT_ID('dbo.TestTable');

-- 依索引名取得欄位清單 (動態 SQL 範例)
DECLARE @idx_fields NVARCHAR(MAX) = N'';
SELECT @idx_fields += QUOTENAME(c.name) + N','
FROM sys.index_columns ic
JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.name = N'IX_TestTable_Name'
ORDER BY ic.index_column_id;
SET @idx_fields = LEFT(@idx_fields, LEN(@idx_fields)-1);
PRINT @idx_fields;

3. 約束與相依性

-- CHECK 約束
SELECT * FROM sys.check_constraints WHERE parent_object_id = OBJECT_ID('dbo.TestTable');
-- DEFAULT 約束
SELECT * FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID('dbo.TestTable');
-- PK / UNIQUE
SELECT * FROM sys.key_constraints WHERE parent_object_id = OBJECT_ID('dbo.TestTable');
-- FK
SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('dbo.TestTable');
-- 觸發器
SELECT * FROM sys.triggers WHERE parent_id = OBJECT_ID('dbo.TestTable');
-- 程式碼本體 (取代 syscomments)
SELECT * FROM sys.sql_modules WHERE object_id = OBJECT_ID('dbo.TestTable');
-- 相容: sys.syscomments

4. 安全性與型別系統

-- 資料庫主體 (使用者/角色)
SELECT * FROM sys.database_principals;
-- 相容: sys.sysusers

-- 系統/使用者定義型別
SELECT * FROM sys.types;
-- 相容: sys.systypes

物件類型代碼完整對照 (sys.objects.type)

實戰應用場景

• 容量規劃： `sys.master_files` + `sys.dm_db_partition_stats` 計算每檔案組/資料庫成長趨勢。
• 索引維護： `sys.indexes` + `sys.dm_db_index_physical_stats` 判斷碎片與填滿因子。
• 分區管理： `sys.partitions` + `sys.partition_schemes/functions` 實現滑動窗口歸檔。
• 損壞修復： `sys.allocation_units` 定位損壞頁面所屬物件/索引，配合 `DBCC PAGE` 分析。
• 安全稽核： `sys.sql_modules` 搜尋敏感關鍵字 (xp_cmdshell、OPENROWSET、動態 SQL)。

結語

SQL Server 2008 的目錄視圖體系將儲存架構的每一層 (資料庫 → 檔案組 → 檔案 → 配置單元 → 分區 → 頁面) 都對應到可查詢、可關聯的關係表中。熟練掌握這些視圖的關聯鍵 (`object_id`, `index_id`, `partition_id`, `hobt_id`, `container_id`)，即擁有了「透視資料庫內部」的 X 光眼，是資深 DBA 與效能專家的必備內功。