數(shù)據(jù)壓縮 : 簡要

1. 決定壓縮哪些對象

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

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

• 數(shù)字類型的列和固定長度的字符類型數(shù)據(jù)，但兩者的大多數(shù)值都不會用到此類型的所有字節(jié)。如INT列的值大多數(shù)少于1000.

• 允許為NULL的列有很多NULL值

• 列值中有很多一樣的值或者相同的前綴。

表包含如下數(shù)據(jù)模式時，壓縮效果較差：

• 數(shù)字類型的列和固定長度的字符類型數(shù)據(jù)，但是兩者的大多數(shù)值都會用盡此類型的所有字節(jié)。

• 非常少量的重復(fù)值

• 重復(fù)值不具有相同的前綴

• 數(shù)據(jù)存儲在行外

• FILESTREAM數(shù)據(jù)

2. 評估應(yīng)用負(fù)載模式

被壓縮的頁在磁盤和內(nèi)存都是壓縮的。下面兩種情況下會被解壓縮(不是整頁解壓縮，只解壓縮相關(guān)的數(shù)據(jù))：

• 因?yàn)椴樵冎械膄iltering, sorting, joining操作而被讀取

• 被應(yīng)用程序更新

解壓縮會消耗CPU，但是數(shù)據(jù)壓縮會減少物理IO和邏輯IO，同時會提高緩存效率。對于數(shù)據(jù)掃描操作，減少的IO量非?？捎^。對于單個的查找操作，減少的IO量較少。

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

頁壓縮比行壓縮的CPU開銷高一些，所以確定是否使用頁壓縮會困難一些?？梢酝ㄟ^一些簡單的準(zhǔn)則來幫助我們判斷：

• 從那些不常用的表和索引開始

• 如果系統(tǒng)沒有足夠的CPU余量，不要使用頁壓縮

• 因?yàn)?filtering, joins, aggregates和sorting操作使用解壓縮后的數(shù)據(jù)，所以數(shù)據(jù)壓縮對這類查詢沒有太多幫助。如果工作負(fù)載主要由非常復(fù)雜的查詢(多表JOIN，復(fù)雜聚合)組成，頁壓縮不會提高性能，最主要是節(jié)省存儲空間。

• 大型數(shù)據(jù)倉庫系統(tǒng)中，掃描性能是其重點(diǎn)，同時存儲設(shè)備的成本較高，在CPU性能允許下，建議對所有表使用頁壓縮。

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

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

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

通過如下腳本查詢數(shù)據(jù)庫所有對象的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

通過如下腳本查詢數(shù)據(jù)庫所有對象的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實(shí)例啟動以來的積累值，所以在實(shí)際應(yīng)用中要選擇一個合適的時間來查詢。

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

3. 評估資源需求

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

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

事務(wù)的空間需求由壓縮方式是否是ONLINE(ON or OFF)和數(shù)據(jù)庫的恢復(fù)模式?jīng)Q定。

當(dāng)SORT_IN_TEMPDB=ON時(推薦為ON)，為了實(shí)現(xiàn)并發(fā)DML操作，會在tempdb中Mapping index的內(nèi)部結(jié)構(gòu)來映射舊書簽和新書簽的關(guān)系。對于版本化存儲的，tempdb的使用量由并發(fā)DML操作所涉及的數(shù)據(jù)量和事務(wù)時間長度決定。

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

并行化的注意事項(xiàng)：

• SQL Server在Create/Rebuild/Compress一個索引時，使用索引首列(最左列)的統(tǒng)計(jì)信息確定并行操作在多個CPU間的分布。所以當(dāng)索引首列的篩選度不高，或者數(shù)據(jù)傾斜嚴(yán)重使得首列的值很少時，并行化對性能提升的幫助就很少。

• 使用ONLINE=ON方式Compress/Rebuild堆表是單線程操作。但是壓縮和重建前的表掃描操作是并行多線程的。

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

• X = 壓縮或者重建前的頁數(shù)量

• P = 壓縮后的頁數(shù)量(P < X)

• Y = 新增和被更新的頁數(shù)據(jù) (只適用于ONLINE=ON時并發(fā)應(yīng)用所做修改)

• M = Mapping index的大小 (基于<EMPDB Capacity Planning>白皮書的預(yù)估值)

• C = 重建聚集索引所需CPU時間

在判斷何時和怎么壓縮數(shù)據(jù)時，下面是一些參考點(diǎn)：

• Online vs. Offline:

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

• 一次壓縮一個table/index/partition vs. 多個操作并發(fā):

這個由當(dāng)前資源的余量決定，如果資源很充足，多個壓縮操作并行也可以接受的，否則最好一次一個。

• 表壓縮操作的順序:

從小表開始，小表壓縮需要的資源少，完成快。完成后釋放的資源也利于后續(xù)表的壓縮操作。

• SORT_IN_TEMPDB= ON or OFF:

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

壓縮操作副作用:

• 壓縮操作包括重建操作，所以會移除表或索引上的碎片。

• 壓縮堆表時，如果有非聚集索引存在，則：當(dāng)ONLINE=OFF，索引重建是串行操作，ONLINE=ON，索引重建是并操作。

4. 維護(hù)壓縮數(shù)據(jù)

新插入數(shù)據(jù)的壓縮方式

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

**頁壓縮中，并不是所有的頁都是頁壓縮的，只有當(dāng)頁壓縮節(jié)省的空間量超過一個內(nèi)存閾值時才是。

更新和刪除已壓縮的行

所有對行壓縮表/分區(qū)數(shù)據(jù)行的更新會保持行壓縮格式。并不是每次對頁壓縮表/分區(qū)的數(shù)據(jù)行的更新都會導(dǎo)致列前綴和頁字典被重新計(jì)算，只有當(dāng)在上的更新數(shù)量超過某個內(nèi)部閾值時，才會重新計(jì)算。

輔助數(shù)據(jù)結(jié)構(gòu)的行為    

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

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

5. 回收數(shù)據(jù)壓縮釋放的空閑空間

1. 不回收，留著給將在的數(shù)據(jù)增長使用。這個不適合分區(qū)表(每個分區(qū)對應(yīng)一人不同的文件級)的只讀分區(qū)，壓縮舊的只讀分區(qū)不會增長，壓縮可以節(jié)省大量空間。

2. DBCC SHRINKFILE (或者DBCC SHRINKDATABASE) 。這個操作會帶來大量碎片，同時它是一個單線程操作，可能會耗時較長。

3. 如果壓縮了一個文件組上的所有表，則新建一個文件組，然后在壓縮時將表和索引移動到新的文件組。數(shù)據(jù)移動可以通過Create/Recreate聚集索引的方式實(shí)現(xiàn)(如，WITH (DATA_COMPRESSION=PAGE, DROP_EXISTING=ON, SORT_IN_TEMPDB=ON) ON [FG_NEW] )。移動完數(shù)據(jù)之后，刪除原來的文件組即可。但是這種方式不能移動LOB_DATA數(shù)據(jù)到新文件組。

4. 在新文件組上創(chuàng)建壓縮的表，然后將數(shù)據(jù)導(dǎo)入到這些表。

6. BULK INSERT 和數(shù)據(jù)壓縮

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

壓縮數(shù)據(jù)時，BULK INSERT和創(chuàng)建聚集索引的順序考慮：

7. 數(shù)據(jù)壓縮和分區(qū)表維護(hù)

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

2. Split后的分區(qū)繼承原分區(qū)的壓縮方式。

3. Merger操作，被刪除的分區(qū)稱為源分區(qū),接收數(shù)據(jù)的分區(qū)稱為目標(biāo)分區(qū)：

PS：分區(qū)表Merger操作規(guī)則

1. LEFT RANGE時，刪除邊界值所在的分區(qū)，保留"左"側(cè)的分區(qū)，并向其移動數(shù)據(jù)

2. RIGHT RANGE時，刪除邊界值所在的分區(qū)，保留"右"分區(qū)，并向其移動數(shù)據(jù)

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

TDE是當(dāng)數(shù)據(jù)頁寫入磁盤時加密，從磁盤中讀出頁放入到內(nèi)存時解密。而數(shù)據(jù)壓縮/解壓縮操作是對內(nèi)存中的頁執(zhí)行的，所以數(shù)據(jù)壓縮/解壓縮總是用到解密后的頁。因此兩者之前的相互影響很小。

總結(jié)

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

2. 數(shù)據(jù)壓縮是一個被低估SQL Server技術(shù)，個人認(rèn)為很有必要將之做為標(biāo)準(zhǔn)化最佳實(shí)踐之一。