June 8, 2011 at 2:01 am
Do a compressed table save work / resources in the log and/or backup processes?
I'm thinking of adding another positive bullet to the slide I use to help application decision makers decide wether a compression is good, or not (which it's almost always to some point).
Is it the compressed version of a page that gets to the log file or a decompressed version?
Does an already compressed index need to be uncompressed and recompressed at backup time (due to different algorithms)?
Before taking on a empiric study I thought I'd ask you guys 😉
June 8, 2011 at 6:27 am
You might want to refine your question to include whether you're referring to SQL Server's native compression, third party backup compression, or both. The answer changes if you're talking about third party products.
For example, if you're using SQL Server's native data compression to compress an index, and then you run a backup with a third party compression tool, then you will indeed have extra overhead at backup time. The third party tool will burn CPU time trying to compress data, and it'll end up with a less effective compression ratio. For already-compressed data (or difficult-to-compress data like SharePoint databases with lots of binary files in 'em) you might want to avoid backup compression.
June 8, 2011 at 7:02 am
Thank you Brent. I actually read your blog post about rockstars yesterday and feel a bit like, I don't now, not really a rock star, but at least a fan, when I'm communicating with and having you answer my question the next day. 🙂
I was thinking about native compression, but you broadened my horizon immediately.
But then I guess I was on to something regarding compression of data and then compressing backups as well. It has to decompress the native data compression and then compress the native backup compression algorithms at backup time? Still - if you have CPU for it - I'd go ahead to save disk space if that is my concern.
How about transaction log then? Does the pages logged in the transaction log preserve the compression if that part is compressed?
June 8, 2011 at 7:09 am
Jonas Bergström (6/8/2011)
Thank you Brent. I actually read your blog post about rockstars yesterday and feel a bit like, I don't now, not really a rock star, but at least a fan, when I'm communicating with and having you answer my question the next day. 🙂
HAHAHA, funny! Well, We gotta credit another rock star - Grant Fritchey tweeted your question to #SQLhelp, and that's what brought me in here to answer it.
It has to decompress the native data compression and then compress the native backup compression algorithms at backup time? How about transaction log then? Does the pages logged in the transaction log preserve the compression if that part is compressed?
Nah, when you're using the entire native stack (native data compression and native backup compression), the backup doesn't unpack and then repack it.
Still - if you have CPU for it - I'd go ahead to save disk space if that is my concern.
Well, it depends on just how big of a concern we're talking about. If you're really concerned about space, then get a third party compression tool like Quest LiteSpeed, Red Gate SQL Backup, Idera SQL Safe, HyperBac, etc. Those have additional compression levels that can save even more drive space, but at the cost of much higher CPU consumption. They can also use less CPU power and do less compression if you're into that. Native backup compression is just either on or off - there's no tuning compression levels.
Hope that helps!
June 8, 2011 at 8:16 am
That helps a lot on the backup side of my two questions. Thank you Brent.
Any one up for trying to answer the tx log question as well? Do I save tx log space after a compress? Or does it not affect the size of the log?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply