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


Page/Row Compression


Page/Row Compression

Author
Message
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1807
Hugo Kornelis (9/3/2010)
- Restore and backup:
You could argue that this is simply copying pages from one place to another. What is on the page is irrelevant. The internal code to implement this is unchanged. Hence unaffected.
You could also argue that backup files of compressed databases are a lot smaller. Hence affected.

To its defense, the BOL page does state very clearly that because

Compression occurs in the storage engine and the data is presented to most of the other components of SQL Server in an uncompressed state

•Compression does not affect backup and restore.

The options in the question clearly match the How Compression Affects Other SQL Server Components paragraph of the referenced in the answer BOL page, so this is a good question.

Oleg
J DBA
J DBA
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3546 Visits: 2745
Thanks for the question. I enjoyed educating myself about this feature and was fortunate to find the same article referenced in the explanation.

J DBA
AmolNaik
AmolNaik
SSC Eights!
SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)

Group: General Forum Members
Points: 969 Visits: 1234
Good question! But wouldn't the backup and restore get affected because of the compressed row size? The DB backup would be relatively smaller and hence the restores would be faster.

Thanks,

Amol Naik
Tushar Kanti
Tushar Kanti
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 494
I appreciate all the comments and discussion.

@cdiebolt .. The question was a bit tricky with the language. However the backup/restore operations are not affected as per the performance of the sql server. However I agree that the size of the backup/restore is altered.


@Hugo Kornelis.. Thanks fot the detailed explanation on all the options. Though I have some argument on the
log shipping part that you mentioned. It's definitely true that the log of the compressed database does not have compressed information and hence does not have any size compression. On the other hand the backup/restore has compressed size because of the compressed database. Another thing about the log compression that you mentioned is actually exploited in the Mirroring in SQL Server 2008 refer this link http://msdn.microsoft.com/en-us/library/cc645581.aspx


@CirquedeSQLeil.. Didnt get much of what you intended to say but logs are not affected by database compression.
Tushar Kanti
Tushar Kanti
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 494
I really appreciate the comments and arguments.

@cdiebolt.. The language of the question is tricky. The intention was to ask about the performance of the server affected. However the size of the backup definitely is reduced by a good margin because of Page/Row level compression.

@Hugo Kornelis.. Thanks for the elaboration on all the options. I would like to mention a few things about the comment you made about the sql log compression. The log is definitely not getting compressed and hence has no benefit. On the other hand the size of the backup is reduced cause of the database compression and not because of any compression on the backup. However your comment about the log compression saving bandwidth is actually exploited in SQL Server 2008 Mirroring as mentioned in this link http://msdn.microsoft.com/en-us/library/cc645581.aspx


@CirquedeSQLeil.. Didn't get what you intended to ask but then log's are not compressed when you set up database compression. Hope that helps.
Tushar Kanti
Tushar Kanti
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 494
I really appreciate the comments and arguments.

@cdiebolt.. The language of the question is tricky. The intention was to ask about the performance of the server affected. However the size of the backup definitely is reduced by a good margin because of Page/Row level compression.

@Hugo Kornelis.. Thanks for the elaboration on all the options. I would like to mention a few things about the comment you made about the sql log compression. The log is definitely not getting compressed and hence has no benefit. On the other hand the size of the backup is reduced cause of the database compression and not because of any compression on the backup. However your comment about the log compression saving bandwidth is actually exploited in SQL Server 2008 Mirroring as mentioned in this link http://msdn.microsoft.com/en-us/library/cc645581.aspx


@CirquedeSQLeil.. Didn't get what you intended to ask but then log's are not compressed when you set up database compression. Hope that helps.
Tushar Kanti
Tushar Kanti
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 494
I really appreciate the comments and arguments.

cdiebolt.. The language of the question is tricky. The intention was to ask about the performance of the server affected. However the size of the backup definitely is reduced by a good margin because of Page/Row level compression.

Hugo Kornelis.. Thanks for the elaboration on all the options. I would like to mention a few things about the comment you made about the sql log compression. The log is definitely not getting compressed and hence has no benefit. On the other hand the size of the backup is reduced cause of the database compression and not because of any compression on the backup. However your comment about the log compression saving bandwidth is actually exploited in SQL Server 2008 Mirroring as mentioned in this
link.


CirquedeSQLeil.. Didn't get what you intended to ask but then log's are not compressed when you set up database compression. Hope that helps.
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
Oleg Netchaev (9/3/2010)
Hugo Kornelis (9/3/2010)
- Restore and backup:
You could argue that this is simply copying pages from one place to another. What is on the page is irrelevant. The internal code to implement this is unchanged. Hence unaffected.
You could also argue that backup files of compressed databases are a lot smaller. Hence affected.

To its defense, the BOL page does state very clearly that because

Compression occurs in the storage engine and the data is presented to most of the other components of SQL Server in an uncompressed state

•Compression does not affect backup and restore.

The options in the question clearly match the How Compression Affects Other SQL Server Components paragraph of the referenced in the answer BOL page, so this is a good question.

Oleg

But would that not mean that Import & Export is not affected either?
Whats the difference between Backup and Import & Export with regards to the effect of Page/Row Compression. I don't see any - to both the (de-) compression should be transparent, and the performance impact should be the same.

Best Regards,

Chris Büttner
jlennartz
jlennartz
SSC Eights!
SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)

Group: General Forum Members
Points: 810 Visits: 1197
Excellent question and the reference cited spells out the answers in detail.
khullargirish02
khullargirish02
Old Hand
Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)

Group: General Forum Members
Points: 385 Visits: 126
Compression occurs in the storage engine and the data is presented to most of the other components of SQL Server in an uncompressed state. This limits the effects of compression on the other components to the following:

a) Bulk import and export operations

When data is exported, even in native format, the data is output in the uncompressed row format. This can cause the size of exported data file to be significantly larger than the source data.

When data is imported, if the target table has been enabled for compression, the data is converted by the storage engine into compressed row format. This can cause increased CPU usage compared to when data is imported into an uncompressed table.

When data is bulk imported into a heap with page compression, the bulk import operation will try to compress the data with page compression when the data is inserted.

b) Compression does not affect backup and restore.

c) Compression does not affect log shipping but do affect replication.

Enabling compression can cause query plans to change because the data is stored using a different number of pages and number of rows per page.
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