November 1, 2010 at 8:35 am
I am running some test on compression for one of our SQL Server based systems. When estimating ROW level compression for some objects, I actually get a negative savings. I have sen an explanation for this but cannot seem to find it now.
Can someone enlighten me as to under what scenarios I would see negative savings?
Thanks!
November 2, 2010 at 6:48 am
That usualy happen when you use fixed length character columns and the actual data fit tighly in it, ROW compression is adding some overhead that cannot be saved if none of the data can be compressed.
Try with this example:Create table RowCompressionTest (
ID tinyint not null,
SQLText1 char(50) not null)
GO
With tbl10(n) as (
Select 1 Union All Select 1 Union All
Select 1 Union All Select 1 Union All
Select 1 Union All Select 1 Union All
Select 1 Union All Select 1 Union All
Select 1 Union All Select 1),
tbl100(n) as (Select a.n from tbl10 a cross join tbl10 b),
tbl10k(n) as (Select a.n from tbl100 a cross join tbl100 b),
tbl100m(n) as (Select a.n from tbl10k a cross join tbl10k b)
Insert RowCompressionTest with (tablock) (ID, SQLText1)
Select top 10000000
row_number() over (order by n) % 255 ID
, '&SRE1W83!q9j?!EkPCIImziHfr3dr9K?F112JslKwo4#mJ#nU5' SQLText1
From tbl100m
Exec sp_estimate_data_compression_savings 'dbo', 'RowCompressionTest', 0, 1, 'ROW'
/*
size_with_current_compression_setting(KB)size_with_requested_compression_setting(KB)
597040606464
*/
In this case no space can be saved from tinyint (takes only 1 byte) and the string is always 50 character long, so no space saved there.
Full detail on row compression here: http://technet.microsoft.com/en-us/library/cc280576.aspx
Some details about negative compression in the Remarks section of this article: http://technet.microsoft.com/en-us/library/cc280574.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply