Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

If I compress a primary key, am I compressing the whole table --> P-key with DATA_COMPRESSION Expand / Collapse
Author
Message
Posted Wednesday, March 6, 2013 6:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:54 PM
Points: 23,227, Visits: 31,921
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.



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)
Post #1427361
Posted Wednesday, March 6, 2013 7:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:41 AM
Points: 170, Visits: 1,400
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.


Cheers,

JohnA

MCM: SQL2008
Post #1427369
Posted Wednesday, March 6, 2013 7:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
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.


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 2008, MVP
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

Post #1427374
Posted Wednesday, March 6, 2013 7:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:54 PM
Points: 23,227, Visits: 31,921
Here is one link: http://msdn.microsoft.com/en-us/library/dd894051(v=SQL.100).aspx.




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)
Post #1427380
Posted Wednesday, March 6, 2013 7:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:54 PM
Points: 23,227, Visits: 31,921
Gail beat me to that one. Oh well.



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)
Post #1427384
Posted Wednesday, March 6, 2013 7:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:41 AM
Points: 170, Visits: 1,400
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
Post #1427403
Posted Monday, March 11, 2013 5:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:41 AM
Points: 170, Visits: 1,400
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.


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
Post #1429205
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse