最近客戶要節省資料庫空間,除了資料封存(archive),想到了SQL Server Enterprise版本限定的功能”資料壓縮”。
壓縮從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: Strategy, Capacity Planning and Best Practices