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 ««123»»

Page/Row Compression Expand / Collapse
Author
Message
Posted Friday, September 3, 2010 9:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:33 AM
Points: 1,676, Visits: 1,760
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
Post #980300
Posted Friday, September 3, 2010 10:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:21 PM
Points: 2,807, Visits: 2,043
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
Post #980330
Posted Friday, September 3, 2010 11:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:43 PM
Points: 945, Visits: 1,234
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
Post #980406
Posted Friday, September 3, 2010 11:45 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, October 10, 2014 9:20 PM
Points: 117, Visits: 465
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.
Post #980415
Posted Friday, September 3, 2010 11:57 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, October 10, 2014 9:20 PM
Points: 117, Visits: 465
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.

Post #980425
Posted Friday, September 3, 2010 9:48 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, October 10, 2014 9:20 PM
Points: 117, Visits: 465
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.

Post #980578
Posted Friday, September 3, 2010 9:53 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, October 10, 2014 9:20 PM
Points: 117, Visits: 465
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.

Post #980579
Posted Tuesday, September 7, 2010 11:08 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Post #982045
Posted Wednesday, September 8, 2010 12:45 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, December 11, 2014 12:53 PM
Points: 790, Visits: 1,192
Excellent question and the reference cited spells out the answers in detail.
Post #982580
Posted Friday, May 27, 2011 2:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, September 21, 2014 12:18 AM
Points: 375, Visits: 116
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.

Post #1116052
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse