數據壓縮 : 簡要_ZenDei技術網路在線

1. 決定壓縮哪些對象通過sp_estimate_data_compression_savings 評估在ROW和PAGE壓縮時分別節省的空間量。表包含如下數據模式時，會有較好的壓縮效果：數字類型的列和固定長度的字元類型數據，但兩者的大多數值都不會用到此類型的所有位元組。如INT列的值大多數少於 ...

1. 決定壓縮哪些對象

通過sp_estimate_data_compression_savings 評估在ROW和PAGE壓縮時分別節省的空間量。

表包含如下數據模式時，會有較好的壓縮效果：

數字類型的列和固定長度的字元類型數據，但兩者的大多數值都不會用到此類型的所有位元組。如INT列的值大多數少於1000.
允許為NULL的列有很多NULL值
列值中有很多一樣的值或者相同的首碼。

表包含如下數據模式時，壓縮效果較差：

數字類型的列和固定長度的字元類型數據，但是兩者的大多數值都會用盡此類型的所有位元組。
非常少量的重覆值
重覆值不具有相同的首碼
數據存儲在行外
FILESTREAM數據

2. 評估應用負載模式

被壓縮的頁在磁碟和記憶體都是壓縮的。下麵兩種情況下會被解壓縮(不是整頁解壓縮，只解壓縮相關的數據)：

因為查詢中的filtering, sorting, joining操作而被讀取
被應用程式更新

解壓縮會消耗CPU，但是數據壓縮會減少物理IO和邏輯IO，同時會提高緩存效率。對於數據掃描操作，減少的IO量非常可觀。對於單個的查找操作，減少的IO量較少。

行壓縮導致的CPU開銷通常不會超過10%。如果當前的系統資源充足，增加10%CPU毫無壓力的話，建議所有的表都啟用行壓縮。

頁壓縮比行壓縮的CPU開銷高一些，所以確定是否使用頁壓縮會困難一些。可以通過一些簡單的準則來幫助我們判斷：

從那些不常用的表和索引開始
如果系統沒有足夠的CPU餘量，不要使用頁壓縮
因為 filtering, joins, aggregates和sorting操作使用解壓縮後的數據，所以數據壓縮對這類查詢沒有太多幫助。如果工作負載主要由非常複雜的查詢(多表JOIN，複雜聚合)組成，頁壓縮不會提高性能，最主要是節省存儲空間。
大型數據倉庫系統中，掃描性能是其重點，同時存儲設備的成本較高，在CPU性能允許下，建議對所有表使用頁壓縮。

可以通過兩個更細的度量值來幫我們評估使用何種數據壓縮方式：

U:特定對象(表、索引或者分區)的更新操作占所有操作的百分比。越低越適合頁壓縮。
S:特定對象(表、索引或者分區)的掃描操作占所有操作的百分比。越高越適合頁壓縮。

通過如下腳本查詢資料庫所有對象的U:

SELECT o.name AS [Table_Name], x.name AS [Index_Name],

       i.partition_number AS [Partition],

       i.index_id AS [Index_ID], x.type_desc AS [Index_Type],

       i.leaf_update_count * 100.0 /

           (i.range_scan_count + i.leaf_insert_count

            + i.leaf_delete_count + i.leaf_update_count

            + i.leaf_page_merge_count + i.singleton_lookup_count

           ) AS [Percent_Update]

FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i

JOIN sys.objects o ON o.object_id = i.object_id

JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id

WHERE (i.range_scan_count + i.leaf_insert_count

       + i.leaf_delete_count + leaf_update_count

       + i.leaf_page_merge_count + i.singleton_lookup_count) != 0

AND objectproperty(i.object_id,'IsUserTable') = 1

ORDER BY [Percent_Update] ASC

通過如下腳本查詢資料庫所有對象的S:

SELECT o.name AS [Table_Name], x.name AS [Index_Name],

       i.partition_number AS [Partition],

       i.index_id AS [Index_ID], x.type_desc AS [Index_Type],

       i.range_scan_count * 100.0 /

           (i.range_scan_count + i.leaf_insert_count

            + i.leaf_delete_count + i.leaf_update_count

            + i.leaf_page_merge_count + i.singleton_lookup_count

           ) AS [Percent_Scan]

FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i

JOIN sys.objects o ON o.object_id = i.object_id

JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id

WHERE (i.range_scan_count + i.leaf_insert_count

       + i.leaf_delete_count + leaf_update_count

       + i.leaf_page_merge_count + i.singleton_lookup_count) != 0

AND objectproperty(i.object_id,'IsUserTable') = 1

ORDER BY [Percent_Scan] DESC

這兩個查詢用到了DMV sys.dm_db_index_operational_stats。DMV只是記錄上次SQL Server實例啟動以來的積累值，所以在實際應用中要選擇一個合適的時間來查詢。

通常U<20%和S>75%會是比較合理的考慮啟用壓縮的出發點，但是對於只插入有序數據的流水錶，頁壓縮會比較合適(即使S值很低)。

3. 評估資源需求

使用ALTER TABLE… REBUILD和ALTER INDEX … REBUILD對錶和索引啟用壓縮，其它原理和重建索引是一樣的。通常需要的資源包括空間、CPU、IO、空間需求

在壓縮過程中，已壓縮的表和未壓縮表是並存的，只有完成壓縮後，未壓縮的表才會被刪除並釋放空間。如果Rebuild是ONLINE的話，則還有Mapping Index需要額外的空間。

事務的空間需求由壓縮方式是否是ONLINE(ON or OFF)和資料庫的恢復模式決定。

當SORT_IN_TEMPDB=ON時(推薦為ON)，為了實現併發DML操作，會在tempdb中Mapping index的內部結構來映射舊書簽和新書簽的關係。對於版本化存儲的，tempdb的使用量由併發DML操作所涉及的數據量和事務時間長度決定。

通常行壓縮操作的CPU開銷是重建一個索引的1.5倍左右，頁壓縮是它的2到5倍。ONLINE模式還需要額外的CPU資源。Rebuild和Compress可以被並行化的，所以還要結合MAXDOP一起考慮。

並行化的註意事項：

SQL Server在Create/Rebuild/Compress一個索引時，使用索引首列(最左列)的統計信息確定並行操作在多個CPU間的分佈。所以當索引首列的篩選度不高，或者數據傾斜嚴重使得首列的值很少時，並行化對性能提升的幫助就很少。
使用ONLINE=ON方式Compress/Rebuild堆表是單線程操作。但是壓縮和重建前的表掃描操作是並行多線程的。

下表總結對比了壓縮和重建一個聚集索的資源開銷：

X = 壓縮或者重建前的頁數量
P = 壓縮後的頁數量(P < X)
Y = 新增和被更新的頁數據 (只適用於ONLINE=ON時併發應用所做修改)
M = Mapping index的大小 (基於<TEMPDB Capacity Planning>白皮書的預估值)
C = 重建聚集索引所需CPU時間

在判斷何時和怎麼壓縮數據時，下麵是一些參考點：

Online vs. Offline:

Offline更快，需要的資源也更少，但是壓縮操作過程中會鎖表。Online自身也會有一些限制。

一次壓縮一個table/index/partition vs. 多個操作併發:

這個由當前資源的餘量決定，如果資源很充足，多個壓縮操作並行也可以接受的，否則最好一次一個。

表壓縮操作的順序:

從小表開始，小表壓縮需要的資源少，完成快。完成後釋放的資源也利於後續表的壓縮操作。

SORT_IN_TEMPDB= ON or OFF:

推薦ON。這樣可以利用tempdb來存放和完成Mapping index操作，從而也減少用戶數據的空間需求。

壓縮操作副作用:

壓縮操作包括重建操作，所以會移除表或索引上的碎片。
壓縮堆表時，如果有非聚集索引存在，則：當ONLINE=OFF，索引重建是串列操作，ONLINE=ON，索引重建是並操作。

4. 維護壓縮數據

新插入數據的壓縮方式

*通過以頁壓縮方式重建堆表來將行級壓縮頁轉換為頁級壓縮。

**頁壓縮中，並不是所有的頁都是頁壓縮的，只有當頁壓縮節省的空間量超過一個記憶體閾值時才是。

更新和刪除已壓縮的行

所有對行壓縮表/分區數據行的更新會保持行壓縮格式。並不是每次對頁壓縮表/分區的數據行的更新都會導致列首碼和頁字典被重新計算，只有當在上的更新數量超過某個內部閾值時，才會重新計算。

輔助數據結構的行為

Table compression	Transaction log	Mapping index for rebuilding the clustered index	Sort pages for queries	Version store (with SI or RCSI isolation level)
ROW	ROW	NONE	NONE	ROW
PAGE	ROW	NONE	NONE	ROW

頁壓縮索引的非葉級頁是行壓縮的

索引的非葉級頁相對較小，就算應用頁級壓縮，節省的空間也不會很顯著。而對非葉級頁的訪問會很頻繁，使用行級壓縮可減少每次訪問時解壓縮成本。

5. 回收數據壓縮釋放的空閑空間

不回收，留著給將在的數據增長使用。這個不適合分區表(每個分區對應一人不同的文件級)的只讀分區，壓縮舊的只讀分區不會增長，壓縮可以節省大量空間。
DBCC SHRINKFILE (或者DBCC SHRINKDATABASE) 。這個操作會帶來大量碎片，同時它是一個單線程操作，可能會耗時較長。
如果壓縮了一個文件組上的所有表，則新建一個文件組，然後在壓縮時將表和索引移動到新的文件組。數據移動可以通過Create/Recreate聚集索引的方式實現(如，WITH (DATA_COMPRESSION=PAGE, DROP_EXISTING=ON, SORT_IN_TEMPDB=ON) ON [FG_NEW] )。移動完數據之後，刪除原來的文件組即可。但是這種方式不能移動LOB_DATA數據到新文件組。
在新文件組上創建壓縮的表，然後將數據導入到這些表。

6. BULK INSERT 和數據壓縮

BULK INSERT WITH (TABLOCK)導入數據到已壓縮的表，速度最快。很明顯，這會鎖表。

壓縮數據時，BULK INSERT和創建聚集索引的順序考慮：

序號	方式	比較
1	BULK INSERT導入數據到未壓縮的堆表，然後再 CREATE CLUSTERED INDEX WITH (DATA_COMPRESSION = PAGE).	所需時間：1<2<3
2	BULK INSERT導入數據到頁壓縮的堆表，然後再 CREATE CLUSTERED INDEX	所需空間：1>2>3
3	BULK INSERT導入數據到頁壓縮的聚集索引

7. 數據壓縮和分區表維護

1. Switch操作要求目標分區(或目標表)與源分區的壓縮方式相同。

2. Split後的分區繼承原分區的壓縮方式。

3. Merger操作，被刪除的分區稱為源分區,接收數據的分區稱為目標分區：

目標分區的壓縮方式	數據合併到目標分區的方式
NONE	在Merger期間，數據會被解壓縮到目標分區
ROW	在Merger期間，數據會被轉換成行壓縮格式
PAGE	-堆表: 在Merger期間，數據會被轉換成行壓縮格式 - 聚集索引: 在Merger期間，數據會被轉換成頁壓縮格式

分區表Merger操作規則

1. LEFT RANGE時，刪除邊界值所在的分區，保留"左"側的分區，並向其移動數據

2. RIGHT RANGE時，刪除邊界值所在的分區，保留"右"分區，並向其移動數據

8. 數據壓縮和透明數據加密(TDE)

TDE是當數據頁寫入磁碟時加密，從磁碟中讀出頁放入到記憶體時解密。而數據壓縮/解壓縮操作是對記憶體中的頁執行的，所以數據壓縮/解壓縮總是用到解密後的頁。因此兩者之前的相互影響很小。

總結

1. 本文來基於白皮書<Data Compression: Strategy, Capacity Planning and Best Practices>的簡譯和總結。此白皮書是基於SQL Server 2008的。

2. 數據壓縮是一個被低估SQL Server技術，個人認為很有必要將之做為標準化最佳實踐之一。