Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


If I compress a primary key, am I compressing the whole table --> P-key with DATA_COMPRESSION


If I compress a primary key, am I compressing the whole table --> P-key with DATA_COMPRESSION

Author
Message
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24171 Visits: 37936
Steve Malley (3/6/2013)
I should have mentioned in my initial post that I was speaking of a Primary Key *Clustered* constraint. This creates a corresponding index.


And that index is the data at the leaf level, it isn't a separate index like nonclustered indexes.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
SQLCharger
SQLCharger
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 1400
GilaMonster (3/6/2013)
SQLCharger (3/6/2013)
If it is normally in memory anyway, you may decide to leave it uncompressed.


Why?


Gail,

My tests have shown a performance degradation when the same queries run on compressed tables compared to uncompressed.

I know, there are fewer pages to trawl across, but it is more expensive to crack them open as well - I've found the net effect to be negative (10-20% give or take).

Especially with page compression, even if you have 1/3 of the pages (typical for many data tables) I've found it to still be quicker when having compression off. Looks like the access speed of each page goes down by a bit more than the size reduction.

If there are exceptions to this I'd love to hear them, so I can fine-tune the algorithms.Cool

Cheers,

JohnA

MCM: SQL2008
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47191 Visits: 44358
SQLCharger (3/6/2013)
My tests have shown a performance degradation when the same queries run on compressed tables compared to uncompressed.

I know, there are fewer pages to trawl across, but it is more expensive to crack them open as well - I've found the net effect to be 10 - 20% worse.

Especially with page compression, even if you have 1/3 of the pages (typical for many data tables) I've found it to still be quicker when having compression off. Looks like the access speed of each page goes down by a bit more than the size reduction.

If there are exceptions to this I'd love to hear them, so I can fine-tune the algorithms.Cool


That depends entirely on the workload and on the type of compression. Also you have the effect of reducing memory footprint thereby reducing IO load (more can fit into memory), reducing the CPU impact of moving data around, etc.

Page compression's overhead can be unacceptable, row compression often isn't, when there's an overhead at all.
Take a look at the whitepaper on compression, the performance results are at the bottom.
http://msdn.microsoft.com/en-us/library/dd894051.aspx

I've seen other benchmarks where even OLTP apps got performance improvements from compressing at row or page level.

Pretty much it's a case of 'test it and see', unless you're running a vendor app where that testing's been done.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24171 Visits: 37936
Here is one link: http://msdn.microsoft.com/en-us/library/dd894051(v=SQL.100).aspx.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24171 Visits: 37936
Gail beat me to that one. Oh well.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
SQLCharger
SQLCharger
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 1400
Yep, 'test it and see' indeed.

If you have lots of half-empty Char(100) columns, I guess row-compression may speed things up even comparing memory access speeds.

My tests were using an efficient design where 'every byte counts', so compression would have a harder time to prove its worth.

Once we move the transactional system to 2008, I'll test that one for compression gains (it will still fit in memory uncompressed, but the schema is inefficient so maybe compression will be worth it).

SQL Server matters are rarely cut'n'dry eh...

Cheers,

JohnA

MCM: SQL2008
SQLCharger
SQLCharger
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 1400
GilaMonster (3/6/2013)
SQLCharger (3/6/2013)
My tests have shown a performance degradation when the same queries run on compressed tables compared to uncompressed.

I know, there are fewer pages to trawl across, but it is more expensive to crack them open as well - I've found the net effect to be 10 - 20% worse.

Especially with page compression, even if you have 1/3 of the pages (typical for many data tables) I've found it to still be quicker when having compression off. Looks like the access speed of each page goes down by a bit more than the size reduction.

If there are exceptions to this I'd love to hear them, so I can fine-tune the algorithms.Cool


That depends entirely on the workload and on the type of compression. Also you have the effect of reducing memory footprint thereby reducing IO load (more can fit into memory), reducing the CPU impact of moving data around, etc.

Page compression's overhead can be unacceptable, row compression often isn't, when there's an overhead at all.
Take a look at the whitepaper on compression, the performance results are at the bottom.
http://msdn.microsoft.com/en-us/library/dd894051.aspx

I've seen other benchmarks where even OLTP apps got performance improvements from compressing at row or page level.

Pretty much it's a case of 'test it and see', unless you're running a vendor app where that testing's been done.


Gail,

Thomas Kejser has just posted some relevant metrics on his excellent blog:
http://blog.kejser.org/2013/03/11/quantifying-the-cost-of-compression/

Cheers,

JohnA

MCM: SQL2008
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search