RiCo技術農場

老實說，我從不覺得啟用資料壓縮功能的索引，在頻繁的查詢該索引會吃掉大部分CPU資源，

我以前測試過資料壓縮對記憶體的影響，因為我個人喜歡用數據來說話，

動手建立LAB來驗證自己想法這是最有效的，

今天，我也用數據來說說該功能對CPU到底有沒有巨大影響，甚至需要你對它提心吊膽。

同事A:我從不啟用索引資料壓縮，因為頻繁Select該索引，會吃掉系統大部分CPU資源

 

Query statement without index compression

one session to query 100 times

declare @i tinyint=0;
while(@i<100)
begin
select device_name,emp_id,emp_dep
from dbo.rsa241 with(index(idx1_nocompression))
where door_name=N'0002'
set @i=@i+1;
end

 

Create Index

create index idx1_nocompression on dbo.rsa241(door_name)
include(device_name,emp_id,emp_dep)

index disk usage

SELECT i.[name] AS IndexName
    ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
    AND s.[index_id] = i.[index_id]
where i.[name]='idx1_nocompression'
GROUP BY i.[name]
ORDER BY i.[name]
GO

Processor:% Processor Time平均:31.823

System: %Total Processor Time平均:210.121

4 sessions to query 40 times

Processor:% Processor Time平均:46.646

System: %Total Processor Time平均:208.292

 

Query statement with index compression

declare @i tinyint=0;
while(@i<100)
begin
select device_name,emp_id,emp_dep
from dbo.rsa241 with(index(idx1_compression))
where door_name=N'0002'
set @i=@i+1;
end

 

create Index

create index idx1_compression on dbo.rsa241(door_name)
include(device_name,emp_id,emp_dep)
with(data_compression=row)

 

index disk usage

One session to query 100 times

Processor:% Processor Time平均:30.520

System: %Total Processor Time平均:208.064

4 sessions to query 40 times

Processor:% Processor Time平均:44.311

System: %Total Processor Time平均:209

結果

你可以看到啟用索引壓縮，Select該索引反而還使用較少CPU資源，

資料壓縮功能對Select幾乎沒有影響，且查詢頻率也非該關心的重點，

要注意的副作用是，壓縮功能會影響DML效能(尤其是批次Insert和update資料時，如一次1000筆資料..等)，

所以OLTP大部分最好啟用row壓縮(但有些資料表我也有啟用page壓縮，前提是要測試過)，因為這可以最小化DML的效能影響，

但又可獲得其他更多效益，如節省硬碟空間、記憶體、網路傳輸量和邏輯IO讀取量，

用一點點CPU資源來換這些好處，我認為真的很值得。

 

補充:

我在8/20寫封mail，詢問國外SQL Expert對於資料壓縮的使用經驗，是否有發生過頻繁查詢壓縮索引造成耗用更多CPU資源，

這些國外SQL Expert都使用資料壓縮功能很久了，也都沒有發生過該問題(前提是要有測試過)，

針對國外資料庫基本TB起跳來說，這些專家都覺得資料壓縮功能真的很神奇也很棒(用一點CPU來換sapce何樂不為)，

同時他們得到很多system owner的主要feeback: they can't believe they didn't use it before.

大家可以參考看看:)

我擷取內容如下:

Greg's replay

Gerald's replay

 

參考

[SQL SERVER][Performance]善用資料壓縮#簡介

[SQL SERVER][Performance]善用資料壓縮#實做

Monitor CPU Usage

[SQL SERVER][Memo]資料壓縮對記憶體影響

Processor Counters

[SQL SERVER][Performance]找出硬體Bottleneck

Index Disk Space Example