SQL Server 2008 資料壓縮深度解析：列級與頁級壓縮原理、實作與效能實測

前言：為什麼需要資料壓縮？

SQL Server 效能瓶頸多半在於磁碟 I/O。資料壓縮透過減少磁碟佔用空間，間接提升 I/O 吞吐量、縮短備份/還原時間、降低儲存成本。SQL Server 2008 引入原生資料壓縮功能，支援列級壓縮與頁級壓縮兩種層級，可套用於資料表、叢集索引、非叢集索引、索引檢視、分割資料表與分割索引。

一、壓縮層級架構與適用範圍

注意：當堆疊資料表的壓縮狀態變更時，所有非叢集索引會自動重建。

二、列級壓縮 (Row Compression) 核心機制

列級壓縮著眼於「儲存格式最佳化」，不改變資料邏輯：

1. 減少中繼資料開銷：精簡資料列標頭資訊。
2. 數值型別動態縮減：int、decimal、float 及衍生型別 (datetime、money) 依實際值大小動態調整位元組數。
   例：100 存入 int 原佔 4 bytes，壓縮後僅需 1 byte (0–255 範圍)，省 3 bytes。
3. 固定長度字串變動化：char/nchar 視實際內容長度儲存，尾端空白不再佔空間。
   例：CHAR(10) 存 'CSDN'，原佔 10 bytes，壓縮後僅 4 bytes。
4. NULL 與零值零位元組儲存：所有資料型別的 NULL 與 0 不再佔用任何位元組。

三、頁級壓縮 (Page Compression) 進階機制

頁級壓縮在列級基礎上，針對「單一資料頁內的重複值」進行跨列最佳化：

1. 列級壓縮：先套用上述所有列級技術。
2. 前綴壓縮 (Prefix Compression)：
   分析頁面上每個欄位的值，找出最長共同前綴，建立「錨點值」存放於頁首，各列改存「相對於錨點的差異部分」或「指向錨點的參考」。
3. 字典壓縮 (Dictionary Compression)：
   針對頁面上所有欄位的重複值（不限前綴），建立全域字典表存放於頁首，各列改存字典索引值。字典壓縮作用於「頁面內任意位置的重複值」，比前綴壓縮更激進。

四、實作範例：建立測試表並逐步套用壓縮

USE tempdb;
GO

-- 1. 建立測試表
CREATE TABLE TestCompression (
    col1 INT,
    col2 CHAR(50)
);
GO

-- 2. 灌入 5,000 筆測試資料
INSERT INTO TestCompression VALUES (10, 'compression testing');
GO 5000

-- 3. 查詢原始空間使用
EXEC sp_spaceused TestCompression;
GO

-- 4. 套用列級壓縮
ALTER TABLE TestCompression REBUILD WITH (DATA_COMPRESSION = ROW);
GO
EXEC sp_spaceused TestCompression;
GO

-- 5. 套用頁級壓縮
ALTER TABLE TestCompression REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
EXEC sp_spaceused TestCompression;
GO

-- 6. 移除壓縮還原
ALTER TABLE TestCompression REBUILD WITH (DATA_COMPRESSION = NONE);
GO
EXEC sp_spaceused TestCompression;
GO

五、效能觀測重點與權衡

六、最佳實務建議

• 先評估再套用：使用 sp_estimate_data_compression_savings 預估空間節省與 CPU 影響。
• 分階段部署：優先壓縮大型唯讀/歷史資料表、非叢集索引；核心交易表謹慎測試。
• 監控 CPU 與 Buffer Pool：壓縮頁面在記憶體中仍為壓縮狀態，讀取需解壓縮，可能增加 CPU 負載。
• 備份/還原受益：壓縮後備份檔案顯著縮小，I/O 時間縮減，災難復原 RTO 縮短。
• 版本限制：Enterprise/Developer/Evaluation 版完整支援；Standard 版僅支援備份壓縮，不支援資料壓縮。

結語

SQL Server 2008 的資料壓縮是「以 CPU 換 I/O」的經典權衡。列級壓縮幾乎零負擔、適用面廣；頁級壓縮威力強大但需評估 CPU 容量。善用 sp_estimate_data_compression_savings 與實際負載測試，找出最適合系統的壓縮策略，能在不擴充硬體前提下大幅提升儲存效率與查詢效能。