Jason Shadonix (4/22/2010)
dma333 (4/22/2010)
Great article. I'd like to see more about how query performance was affected. Also, when backing up a compressed table to a compressed backup, is that faster than an uncompressed table to a compressed backup (since it's already compressed)?I didn't do any in-depth analysis of query performance, but I did run the query for the same amount of time for the three tables, and observed the compressed tables ended up with more records in them at the end of the three min run. Not sure what (if anything) you can read out of that.
Good question on the compressed backups of compressed databases. I hadn't thought of that. Since my test database contained a combination of compressed and uncompressed tables, not sure of the answer to your question. Should be fairly easy to test though.
During a presentation by Microsoft, which of course might not be completely unbiased, the presenter did some backups and restores during the presentation. Backups AND restores were both significantly faster. I don't recall the times, but since backups and restores are both generally I/O bound, when you write less data, the whole process takes less time.
I think that the backup took about one-third of the time to back up with compresison on, and the restore took one-fourth of the time when the backup had been compressed... but that was just one presentation, and from my memory.
So, less space AND less time.