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值很低)。
使用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的大小 (基于<EMPDB 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期間，數據會被轉換成頁壓縮格式 |
PS：分區表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技術，個人認為很有必要將之做為標準化最佳實踐之一。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
