September 3, 2010 at 9:48 pm
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.
September 3, 2010 at 9:53 pm
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.
September 7, 2010 at 11:08 pm
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
September 8, 2010 at 12:45 pm
Excellent question and the reference cited spells out the answers in detail.
May 27, 2011 at 2:19 am
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.
March 19, 2012 at 10:36 am
I learnt more on Page/Row compressions and thank you for your question.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply