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 Tuesday, March 05, 2013 2:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 08, 2013 8:10 AM
Points: 30, Visits: 135
Can someone explain how data compression works at the index level? If I compress a primary key, am I compressing the whole table [since the data pages are leaf pages]?


Post #1427049
Posted Tuesday, March 05, 2013 3:11 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, February 21, 2014 4:34 PM
Points: 369, Visits: 1,197
There is row-level compression and page-level compression (stronger and slower). If you compress clustered index, in fact you compress the table. PK does not have to be the same as clustered key. You should estimate compression gain first to see what you will get in terms of space. Also, be aware that compressing active objects/indexes/partitions is not recommended unless you really have a lot of unused cpu resources. Usually, one compresses old partitions that are queried almost never.


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1427052
Posted Tuesday, March 05, 2013 3:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 08, 2013 8:10 AM
Points: 30, Visits: 135
Thanks for your observations.


Post #1427053
Posted Tuesday, March 05, 2013 4:40 PM


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 @ 7:59 AM
Points: 41,530, Visits: 34,447
Steve Malley (3/5/2013)
Can someone explain how data compression works at the index level? If I compress a primary key, am I compressing the whole table [since the data pages are leaf pages]?


Depends whether the primary key is clustered or not. There's no requirement that it is.
If you compress the clustered index, you are compressing the table, if you compress a nonclustered index, you're compressing just the index.

Have a look for the performance benchmarks microsoft did (cat team I think). They got performance gains in all but one case, even when compressing active, in-use portions of the database. Less data to be moved around, less memory required resulted in improved performance.
I'll see if I can find the link in the morning.



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 #1427079
Posted Tuesday, March 05, 2013 6:33 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 5:20 PM
Points: 237, Visits: 763
Thanks Vedran and Gail for pointing out that a primary key is not the equivalent of a clustered index.
Where does this confusion come from?
Post #1427112
Posted Tuesday, March 05, 2013 6:45 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 22,507, Visits: 30,223
Microsoft. When you create a primary key constraint, if a clustered index does not exist on the table SQL Server defaults the primary key to a clustered index.

Not always what one may want for the clustered index on a table.



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 #1427116
Posted Wednesday, March 06, 2013 3:04 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, February 21, 2014 4:34 PM
Points: 369, Visits: 1,197
Estimate space gain with sp_estimate_data_compression_savings procedure:

exec sp_estimate_data_compression_savings 'myschema', 'mytable', 1, NULL, 'none'
exec sp_estimate_data_compression_savings 'myschema', 'mytable', 1, NULL, 'row'
exec sp_estimate_data_compression_savings 'myschema', 'mytable', 1, NULL, 'page'

With compression = none you check savings because of defragmentation.
Replay your average load and see how much CPU usage will increase.
Each time compresses row or page is accessed (e.g. for reading) it must be decomressed using CPU.


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1427257
Posted Wednesday, March 06, 2013 5:27 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
It is also worth understanding how this table is used and if it normally resides in memory or not.
If it is routinely scanned, then you may choose accept lower savings figures from page compression (say 50% instead of 80%)
If it is routinely used for key lookups, you may choose to err on the side of row compression

If it is normally in memory anyway, you may decide to leave it uncompressed.
If the only way to fit it in memory is through compression, you may evaluate otherwise.
Same with volatility - if 10% of the table is written every day, compression is less attractive compared to it being read-only.

Test with representative load and then you'll know what works best for you


Cheers,

JohnA

MCM: SQL2008
Post #1427324
Posted Wednesday, March 06, 2013 6:15 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 @ 7:59 AM
Points: 41,530, Visits: 34,447
SQLCharger (3/6/2013)
If it is normally in memory anyway, you may decide to leave it uncompressed.


Why?



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 #1427339
Posted Wednesday, March 06, 2013 6:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 08, 2013 8:10 AM
Points: 30, Visits: 135
I should have mentioned in my initial post that I was speaking of a Primary Key *Clustered* constraint. This creates a corresponding index.


Post #1427351
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse