RiCo技術農場

上篇我有提到資料壓縮技術可大量降低 IO讀取、memory(data buffer)、Storage space和Network傳輸量的大小，

這裡我可能沒有太多時間一一呈現相關測試資料，不過還是可以看到資料壓縮所帶來的效果。

建立測試資料表

create table mytbl
(id int identity (1,1),
data varchar(1000))

新增測試資料

declare @i int
set @i = 1
while @i <= 1000000
begin
insert into dbo.mytbl values('ricoisme')
set @i = @i + 1
end
go

確認筆數

總共一百萬筆資料。

類型一:使用頁面壓縮

查看現有資料表預估使用空間

dbcc updateusage ('demotest', 'mytbl')
exec sp_spaceused mytbl

reserved=26000KB。

查看預估壓縮所節省空間  

size_with_requested_compression_setting(KB)=11448。

執行delete operation

delete top(1000) from dbo.mytbl where id between 3000 and 13000
go

刪除1000筆資料。

再度查看使用空間

雖然成功刪除資料後，但空間並未釋放。

再度查看預估壓縮所節省空間

由於空間並未釋放，所以預估壓縮所節省空間數值不變是可以理解的。

執行查詢

select * from dbo.mytbl tt where tt.id between 100 and 754872
go 

邏輯讀取3247表示資料以從記憶體中撈出(data buffer)。 

該查詢執行計畫。

現在我們啟用頁面壓縮來看看相關變化

--順便使用parellel縮短壓縮作業時間
ALTER TABLE dbo.mytbl 
REBUILD WITH (DATA_COMPRESSION=PAGE, MAXDOP=2);  

查看預估使用空間

reserved=10968KB，果然減少很多。

查看預估壓縮所節省空間

EXEC sp_estimate_data_compression_savings 'dbo', 'mytbl', NULL, NULL, 'Page' ;

size_with_requested_compression_setting(KB)=10920，看來頁面壓縮已到極限。

執行查詢

先清除Cache

select * from dbo.mytbl tt where tt.id between 100 and 754872
go

相同的資料列大小，但啟用壓縮後，邏輯讀取果然大幅度降低。

啟用頁面壓縮技術後，果然也影響了執行計畫的變更(IO和運算子成本)。

總表:

資料壓縮類型=PAGE，壓縮比率≒2.38。

類型二:使用資料列壓縮

取消資料壓縮功能

ALTER TABLE dbo.mytbl 
REBUILD WITH (DATA_COMPRESSION=NONE, MAXDOP=2);

新增測試資料

truncate table dbo.mytbl
declare @i int
set @i = 1
while @i <= 1000000
begin
insert into dbo.mytbl values('ricoisme')
set @i = @i + 1
end
go
 

查看預估使用空間

dbcc updateusage ('demotest', 'mytbl')
exec sp_spaceused mytbl

reserved:26000KB。

查看預估壓縮所節省空間

EXEC sp_estimate_data_compression_savings 'dbo', 'mytbl', NULL, NULL, 'ROW' ;
Go 

size_with_requested_compression_setting(KB)=16568。

執行delete operation

delete top(1000) from dbo.mytbl where id between 3000 and 13000
go

刪除1000筆資料。

再度查看預估使用空間

雖然成功刪除資料後，但空間並未釋放。

再度查看預估壓縮所節省空間

size_with_requested_compression_setting(KB)數值依然沒有改變。

執行查詢

select * from dbo.mytbl tt where tt.id between 100 and 754872
go

邏輯讀取3247。

該查詢執行計畫。

現在我們啟用資料列壓縮來看看相關變化 

--順便使用parellel縮短壓縮作業時間
ALTER TABLE dbo.mytbl 
REBUILD WITH (DATA_COMPRESSION=ROW, MAXDOP=2);

查看預估使用空間

reserved=15832KB，該數值沒有頁面壓縮來的小，是因為頁面壓縮也包含了資料列壓縮。

查看預估壓縮所節省空間

size_with_requested_compression_setting(KB)=15792。

執行查詢

select * from dbo.mytbl tt where tt.id between 100 and 754872
go

相同資料列，啟用資料列壓縮後，邏輯讀取果然降低，

雖然降低幅度沒有頁面壓縮來的大，但在CPU和整體時間也相對沒有頁面壓縮來的長。

啟用資料列壓縮技術後，果然也影響了IO和運算子成本。

總表:

資料壓縮類型=ROW，壓縮比率≒1.645。

結論:

一般來說一句SQL statement的執行計畫，我們比較在意io和運算子成本這兩項數值應該越低越好，

因為減少io讀取也是減少記憶體使用量，

而運算子成本表示查詢最佳化工具執行此作業的查詢成本，

你不希望一句SQL statement的執行計畫太過複雜，

因為查詢引擎會選擇最有效率的作業來執行查詢或執行陳述式，

所以我們要讓盡量降低計算統計值所花費的時間，而在SQL2008使用資料壓縮技術便可以輕鬆達到這需求。

啟用資料壓縮技術後，雖然整體時間拉長一點點(大部分都花費在cpu時間)，

但所減少IO讀取、memory(data buffer)、Storage space和Network傳輸量幅度卻是相當大，個人認為這項技術真的很實用。