SQL Server Data Compression (Row, Page, ColumnStore & ColumnStore Archive)


  1. 說明
    1. 實際應用
  2. 參考資料
  3. 相關連結

筆記 SQL Server Data Compression,關於 Row, Page, ColumnStore 以及 ColumnStore Archive 的差異。

SQL Server Logo

說明

資料壓縮 (Data Compression) 與資料庫壓縮不同 (Database shrink)。

資料庫壓縮 (Database shrink) 是將 mdf 以及 ndf 已經預先占用但未使用儲存空間釋放,需要注意的是頻繁的壓縮資料庫,反而會因資料庫資料成長而需要新的資料儲存空間,造成空間增長的 IO 問題,雖可以藉由 Database Instant File Initialization 來減緩影響,但壓縮資料庫已是推薦預設不應啟用與進行的工作。唯一適合的情境是資料庫要 offline 封存,進行前可以先壓縮資料庫釋出空間。

進行資料庫壓縮實際上是執行 DBCC 來完成作業。

DBCC SHRINKDATABASE(N'AdventureWorksLT2019' )
DBCC SHRINKFILE (N'AdventureWorksLT2012_Data' , 0, TRUNCATEONLY)

資料壓縮 (Data Compression) 則是將資料進行編碼,藉由 CPU 的預算減少所儲存的資料量,從而讓 Data Page 可以儲存更多的資料列 (Rows),減少查詢時載入 Data Page 量的 IO 問題。

資料壓縮在資料表中預設並未啟用,啟用上分為下列四種方式:Row、Page、ColumnStore 及 ColumnStore Archive。

Page 是 Row 的進階應用,ColumnStore 以及 ColumnStore Archive 適合應用在 DataWare House OLAP 或者是資料批次只進不改的業務情境。

實際應用

資料壓縮啟用的標的是 Table 或者是 Index:

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

參考史丹利好熱在 資料壓縮功能紀錄(Row vs Page) 的介紹,可以藉由 sys.sp_estimate_data_compression_savings 比較不同壓縮的預估效果:

DECLARE @SchemaName varchar(50) = 'dbo'
DECLARE @TableName varchar(50) = 'products'

exec sys.sp_estimate_data_compression_savings
    @Schema, @Table, NULL, NULL, ROW;
exec sys.sp_estimate_data_compression_savings
    @Schema, @Table, NULL, NULL, PAGE;

參考資料

https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression?view=sql-server-ver16

相關連結

SQL Server Integrated Service 初探

SQL Server 閃電般快速查詢指南⚡

SQL Server 周邊工具彙整筆記

SQL Server 學習資源筆記