筆記 SQL Server Data Compression,關於 Row, Page, ColumnStore 以及 ColumnStore Archive 的差異。
說明
資料壓縮 (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;