史丹利好熱

 

壓縮從SQL Server 2008開始支援，到了2014共有5種資料壓縮，幸好有許多厲害的DBA都有詳細的介紹和範例，今天我們來紀錄前2種壓縮在某個金融核心帳務軟體中的效果。

1.資料列壓縮(Row Compress)

2.頁面壓縮(Page Compress)

3.Unicode Compress(SCSU 壓縮)

4.COLUMNSTORE_ARCHIVE (Microsoft XPRESS)

5.COLUMNSTORE

 

資料列壓縮、頁面壓縮

MSDN上也有詳盡的說明，節錄幾個功能重點:

 

https://msdn.microsoft.com/zh-tw/library/cc280576.aspx

主要是在儲存空間的使用上，例如，如果值可以儲存在 1 個位元組內，則儲存只會使用 1 個位元組。

但如果資料行型別本身就是選擇NVARCHAR、VARCHAR、DATE、TIME、VARBinary等，就沒辦法再壓縮了。

通常OLTP會選用這個壓縮方案!

 

包含上述的資料列壓縮、前置詞(Prefix) 及字典壓縮(Dictionary)。

 https://msdn.microsoft.com/zh-tw/library/cc280464.aspx

通常OLAP會選用這個壓縮方案!

 

預估壓縮效果:

我們可以在執行前使用sys.sp_estimate_data_compression_savings來預估可以節省的空間。

DECLARE @Schema varchar(10) = 'dbo'
DECLARE @Table varchar(20) = 'TX'
exec sys.sp_estimate_data_compression_savings  
    @Schema,@Table,NULL,NULL,ROW;
exec sys.sp_estimate_data_compression_savings 
    @Schema,@Table,NULL,NULL,PAGE;

 

觀察目前Update及Scan比例:

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]
    ,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_Update] ASC

 

執行完畢後，可以從msdn這篇文章決定壓縮的方式(Row or Page)

https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx

 

壓縮語法:

語法也很簡單，可以針對資料表或索引分別啟用壓縮:

資料表

ALTER TABLE TableName REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE)

索引

ALTER INDEX IndexName ON TableName REBUILD PARTITION = ALL 
   WITH (DATA_COMPRESSION = PAGE)

 

好!我們從Lab內選了一個3.2G的帳務交易資料表，建立壓縮需要一些時間!大約3分鐘。

 

測試數據

500萬筆交易資料壓縮比較:

#	容量	壓縮比
未壓縮	3.22 GB	100%
ROW壓縮	1.62 GB	49.6%
PAGE壓縮	0.91 GB	71.7%

可能帳務交易資料表使用了許多Varchar和Date Type，只能在頁面壓縮節省約70%空間(先前的經驗平均是85%)。

 

執行時間: OLAP Batch DML

#	INSERT(ms)	UPDATE(ms)	QUERY(ms)	DELETE(ms)
NONE	6558	1777	683	1204
ROW	7141	1765	79	1059
PAGE	17728	7085	40	3058

Batch查詢上有大幅度的效能提升! 但Page壓縮會影響到Batch Insert及Update。

 

執行時間: OLTP DML

#	INSERT(ms)	UPDATE(ms)	QUERY(ms)	DELETE(ms)
NONE	12	12	15	36
ROW	16	11	8	10
PAGE	11	11	15	13

 OLTP DML在帳務交易資料表執行時間上看不出明顯的差距，但在SQL Server 2008 Data Compression Best Practice文章中提到啟用後有著不小差距 ，可能Table或環境不太相同。    

 

測試小結:

• 頁面壓縮節省約70%空間，資料列壓縮則是節省50%空間，但頁面壓縮的副作用是批次型的DML (Update/Insert/Delete)變慢，不過兩者的大型Query都快很多。
• 啟用或停用壓縮功能可以離線(鎖定資料表)或線上執行(不鎖定資料表)。
• Rebuild Index也有時間會拉長的副作用。(語法增加CPU核心數)
• 每一個資料庫和每個Table的背景不一定相同，效果也可能未盡相同，實際測試看看最準確。
• 就帳務交易資料表分析，資料列壓縮少了Page壓縮的副作用又可以提升查詢效能，C/P值最高。

 

是否啟用壓縮?選擇Row壓縮還是Page壓縮? 我們AP還是找DBA諮商和實際測試最妥當喔。

 

 

參考:

Data Compression

Data Compression: Strategy, Capacity Planning and Best Practices