[SQL Server]資料壓縮功能紀錄(Row vs Page)

最近客戶要節省資料庫空間,除了資料封存(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

Data Compression: Strategy, Capacity Planning and Best Practices