[SQL Server]資料壓縮對CPU影響

我在超過20幾個的OLTP系統,啟用資料壓縮功能都不曾遇到吃掉系統大部分CPU資源,

不管是我在當DBA、consultant或developer角色時,只要使用企業版SQL SERVER,

我都會建議啟用資料壓縮功能。

老實說,我從不覺得啟用資料壓縮功能的索引,在頻繁的查詢該索引會吃掉大部分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