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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » If I compress a primary key, am I compressing...

17 posts, Page 1 of 2

1 2 »»

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

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Malley

Posted Tuesday, March 05, 2013 2:56 PM

SSC 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

Vedran Kesegic

Posted Tuesday, March 05, 2013 3:11 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 8:08 AM
Points: 342, Visits: 1,072

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.

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1427052

Steve Malley

Posted Tuesday, March 05, 2013 3:16 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 08, 2013 8:10 AM
Points: 30, Visits: 135

Thanks for your observations.

Post #1427053

GilaMonster

Posted Tuesday, March 05, 2013 4:40 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 37,640, Visits: 29,895

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

arnipetursson

Posted Tuesday, March 05, 2013 6:33 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:29 PM
Points: 134, Visits: 406

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

Lynn Pettis

Posted Tuesday, March 05, 2013 6:45 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588, Visits: 27,375

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.

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)

Post #1427116

Vedran Kesegic

Posted Wednesday, March 06, 2013 3:04 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 8:08 AM
Points: 342, Visits: 1,072

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.

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1427257

SQLCharger

Posted Wednesday, March 06, 2013 5:27 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:30 AM
Points: 153, Visits: 1,278

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 Wink

Cheers,

JohnA

Post #1427324

GilaMonster

Posted Wednesday, March 06, 2013 6:15 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 37,640, Visits: 29,895

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

Steve Malley

Posted Wednesday, March 06, 2013 6:37 AM

SSC 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 »

17 posts, Page 1 of 2

1 2 »»

Permissions