SQL Server SP為我們帶來了vardecimal功能這項功能使得原來定長的decimal數據在數據文件中以可變長的格式存儲據稱這項功能可以為典型的數據倉庫節省%的空間而SQL Server 在這一基礎上又進一步增強了數據壓縮功能SQL Server 現在支持行壓縮和頁面壓縮兩種選項數據壓縮選項可以在以下對象上啟用
未創建聚簇索引的表
創建聚簇索引的表
非聚簇索引(對表設置壓縮選項不會影響到該表上的非聚簇索引因此聚簇索引的壓縮需要單獨設置)
索引視圖
分區表和分區索引中的單個分區
為什麼需要數據壓縮
首先可能需要討論的問題就是為什麼在存儲成本不斷降低的今天微軟還要煞費苦心地在SQL Server中實現並且不斷改進數據壓縮技術呢?
盡管存儲成本已經不再是傳統意義上的首要考慮因素但是這並不代表數據庫尺寸不是一個問題因為數據庫尺寸除了會影響到存儲成本之外還極大地關聯到管理成本和性能問題
首先我們來討論為什麼會有管理成本的問題?因為數據庫需要備份數據庫的尺寸越大那麼備份時間就會越長當然另外一點就是消耗的備份硬件成本也會隨之提高(包括需要的備份介質成本和為了滿足備份窗口而需要更高級的備份設備帶來的采購成本)還有一種管理成本就是數據庫的維護成本例如我們經常需要完成的DBCC任務數據庫尺寸越大我們就需要更多的時間來完成這些任務
接著我們再看看性能問題SQL Server在掃描磁盤讀取數據的時候都是按照數據頁為單位進行讀取的因此如果一張數據頁中包含的數據行數越多SQL Server在一次數據頁IO中獲得的數據就會越多這樣也就帶來了性能的提升
最後考慮存儲的成本按照原先SQL Server SP中vardecimal的壓縮數據為例%的空間節省也就意味著%的存儲成本而按照SQL Server 當前放出的測試數據采用新的數據壓縮技術可以達到XX的存儲率再加上如果企業要考慮容災而增加的存儲空間這樣節省的存儲硬件成本也將是想當可觀的
如何使用數據壓縮
SQL Server 中的壓縮選項可以在創建表或索引時通過Option進行設置例如
CREATE TABLE TestTable (col int col varchar()) WITH (DATA_COMPRESSION = ROW);
如果需要改變一個分區的壓縮選項則可以用以下語句
ALTER TABLE TestTable REBUILD PARTITION = WITH (DATA COMPRESSION = PAGE);
如果需要為分區表的各個分區設置不同的壓縮選項可以使用以下的語句(SQL Server 可以對不同的分區使用不同的壓縮選項這一點對於數據倉庫應用是非常重要的因為數據倉庫的事實表通常都會有一個或數個熱分區這些分區中的數據經常需要更新為了避免數據壓縮給這些分區上的數據更新帶來額外的處理載荷可以對這些分區關閉壓縮選項)
CREATE TABLE PartitionedTable (col int col varchar())
ON PS (col)
WITH (
DATA_COMPRESSION = ROW ON PARTITIONS()
DATA_COMPRESSION = PAGE ON PARTITION( TO ));
如果是為某個索引設置壓縮選項的話可以使用
CREATE INDEX IX_TestTable_Col ON TestTable (Col) WITH (DATA_COMPRESSION = ROW);
如果是修改某個索引的壓縮選項可以使用
ALTER INDEX IX_TestTable_Col ON TestTable REBUILD WITH (DATA_COMPRESSION = ROW);
SQL Server 同時還提供了一個名為sp_estimate_data_compression_savings存儲過程幫助DBA估計激活壓縮選項後對象尺寸
數據壓縮是怎樣工作的
對於行壓縮SQL Server 采用以下三種方法來節省存儲空間
減少了與記錄相關聯的元數據開銷此元數據為有關列列長度和偏移量的信息在某些情況下元數據開銷可能大於舊的存儲格式
它對於數值類型(例如integerdecimal和float)和基於數值的類型(例如datetime和money)使用可變長度存儲格式
它通過使用不存儲空字符的可變長度格式來存儲定長字符串
對於頁面壓縮SQL Server 則是在一張數據頁面上依次采用
行壓縮
前綴壓縮
字典壓縮
配置數據壓縮功能需要注意的
盡管SQL Server 的數據壓縮功能非常有價值但是仍然需要注意一些問題
數據壓縮功能僅在企業版和開發版中可用
數據壓縮可以讓一張數據頁存儲更多的數據行但是並不能改變單行數據最長字節這一限制
在一張已經設置了數據壓縮的表上創建聚簇索引時聚簇索引默認繼承原表上的壓縮選項
在未設置聚簇索引的表上設置頁面壓縮時只有以下情況才會獲得頁面壓縮的實際效果
數據使用BULK INSERT語法添加到表中
數據使用INSERT INTO WITH (TABLOCK)語法添加到表中
執行帶有頁面壓縮選項的ALTER TABLE REBUILD命令
在未設置聚簇索引的表上更改壓縮選項會導致該表上所有非聚簇索引都需要重建因為這些非聚簇索引指向的數據行地址已經都發生了改變
在改變壓縮選項時所需要的臨時空間大小與創建索引是所需要的空間是一樣的因此對於分區表我們可以逐個分區設置壓縮選項來減少臨時空間的需求壓力
由於SQL Server 中數據壓縮技術其實是SQL Server SP中vardecimal技術的一個超集因此設置了數據壓縮後就沒有必要保留vardecimal了當然SQL Server 為了保持向後兼容性在當前版本中仍然保留了vardecimal但是SQL Server 的下一個版本及可能就會棄用vardecimal選項因此做了這些設置的數據庫應該盡早改變到數據壓縮設置下
SQL Server 的壓縮選項是工作在存儲引擎層的對於SQL Server的其他部件來說這一特性是透明的因此當我們用BULK LOAD的方式將外面的數據導入SQL Server時會顯著的增加CPU的工作載荷同時將以壓縮的數據表導出到外部文件時可能會消耗比原來多很多的空間
From:http://tw.wingwit.com/Article/program/Oracle/201311/17483.html
