Rock的SQL筆記本

SQL2008後MS SQL企業版支援了資料表資料壓縮的功能，壓縮的類型有None跟Row及Page三種模式。None就是不壓縮，而Page壓縮的效果會是最好，下圖是壓縮資料的UI。

本LAB就不說明如何建立資料壓縮，但有一個預存程序一定要介紹一下。sp_estimate_data_compression_savings 這個預存程序能可以針對整個資料表或部分資料表評估壓縮，您可以使用此程序來估計已重新壓縮之資料表、索引或資料分割的大小。

我建立了三張資料表tbNoCompression(不壓縮)，tbRowCompression(Row壓縮)及tbPageCompression(Page壓縮)，如下圖所示這三張資料表都有1千萬筆左右的資料。

由下圖可以看見沒有壓縮的tbNoCompression資料表大小為940MB左右，Row壓縮的為880MB左右，而採Page壓縮的tbPageCompression只有545MB。因此可以知道Page壓縮的效果會比較好。

 

本次實驗的方式就是針對三張資料表做小量及中量跟大量的Select，然後觀察在不同的資料量下，三種壓縮類型對CPU及IO的資源耗用差異。

 

【一、少量資料】資料筆數：32筆 。 搜尋方式：Clustered Index Seek

下圖是沒有壓縮的資料表，IO 4個Page而CPU耗用0 ms。

下圖是Row壓縮的資料表，IO 4個Page而CPU耗用0 ms。

下圖是Page壓縮的資料表，IO 6個Page而CPU耗用0 ms。

 

 

【二、中量資料】資料筆數：32754筆 。 搜尋方式：Clustered Index Seek

 下圖是沒有壓縮的資料表，IO 382個Page而CPU耗用62 ms，總耗時879ms。

下圖是Row壓縮的資料表，IO 359個Page而CPU耗用63 ms，總耗時887ms。

下圖是Row壓縮的資料表，IO 252個Page而CPU耗用125 ms，總耗時921ms。

 

【三、大量資料】資料筆數：236059筆 。 搜尋方式：Clustered Index Scan

下圖是沒有壓縮的資料表，IO 117981+ 2064個Page而CPU耗用3186 ms，總耗時7311ms。

下圖是Row壓縮的資料表，IO 110341+ 289個Page而CPU耗用4124 ms，總耗時7154ms。

下圖是Page壓縮的資料表，IO 68413個Page而CPU耗用5452 ms，總耗時6216ms。

 

完成上述測試後表列如下

	不壓縮	Row壓縮	Page壓縮
小量資料(32筆)	IO數 : 4 CPU耗時: 0 ms 總耗時: 0 ms	IO數 : 4 CPU耗時: 0 ms 總耗時: 0 ms	IO數 : 6 CPU耗時: 0 ms 總耗時: 0 ms
中量資料(32754筆)	IO數 : 382 CPU耗時: 62 ms 總耗時: 879 ms	IO數 : 359 CPU耗時: 63 ms 總耗時: 887 ms	IO數 : 252 CPU耗時: 125 ms 總耗時: 921 ms
大量資料(236059筆)	IO數 : 120045 CPU耗時: 3186 ms 總耗時: 7311 ms	IO數 : 110630 CPU耗時: 4124 ms 總耗時: 7154 ms	IO數 : 68413 CPU耗時: 5452 ms 總耗時: 6216 ms

由實驗結果可以得知壓縮越高的資料表會耗用的CPU資源就越多(少量資料基本上無感)，但相對的壓縮越高可以降低越多的IO。因此我們可以看見在大資料量的情境下，Page壓縮雖然耗費最多的CPU資源(5452 ms)，但是整個總耗時卻是最低(6216 ms)，其原因就是Page壓縮花費較少的IO (68413 Page)所以相對速度也會較快。

所以如果您是企業版的SQL Server就有支援這樣的功能，因此您可以針對不同需求來選擇是否要做資料壓縮。例如SQL瓶頸就是CPU了，那就別做資料壓縮。但如果瓶頸是Disk IO或記憶體空間，那壓縮就是一個不錯選擇了。我曾經將一張約3GB的資料表壓到不到1GB，這樣一來Disk跟Memory就空出了2GB的空間，而且在使用上也沒感覺比較耗時。

 

 

我是ROCK

rockchang@mails.fju.edu.tw