My company uses Quest Software’s LiteSpeed for SQL Server for both nightly and ad-hoc backups. The DBA’s here before me set everything up using a compression ratio of 1 and we get actual compression and backup times that are more than adequate for our needs. I’ve often wondered exactly what effect raising that ratio in the LiteSpeed settings would make in both how long the backup took, but in the actual compression itself. What trade-off between size and efficiency do I have to make should I decide to raise that setting up the scale?
Let me first make it VERY clear. I am not an employee of Quest. I have nothing whatsoever to gain or lose by this examination. I happen to be an extremely big fan of LiteSpeed and have found it by far to be the best backup solution I’ve had the pleasure to work with during my short professional career. I have tremendous respect for everyone from Quest I’ve had the pleasure to come in contact with; that includes Kevin Kline, Brent Ozar & others. This is simply a non-scientific, ground-level look at LiteSpeed’s compression ratios. Please by all means visit Quest’s website to get all the product details and info you need. (http://www.quest.com/) Take nothing I say here as gospel. We clear folks?? I don’t wanna wake up to a cease-and-desist order from Quest tomorrow, ok?
The results graph first, then a discussion on my conclusions…
The blue line and left x-axis represent actual compression for the ratios along the bottom. The red line and right x-axis likewise represent the time taken vs. a native non-LS backup. So for example, when I did my sample on compression level 3, it was compressed almost 81% and it took just about half the time of a full native backup.
Ok, so what observations can I make from these results? As far as compression goes, there seems to be a significant jump from level one to two; that 6% could make a tremendous difference when extending these results to an entire company’s worth of backups. From there, the compression indeed inches up, but never more than another 1/4 or 1/2 percent at a time.
Looking at the red line (time taken for the backup to complete vs a native one), we see time is even quicker than native all the way through level four. Even five and six completed in about the same time as a native backup. From there though, total time taken ramps up pretty quickly; specially at the highest compression ratio which took over five times as long as a native backup.
What compression ratio is best for your situation? Like Paul Randal is so fond of saying, “It depends.” This falls along with the dozens of other decisions we make as DBA’s on a daily basis; a trade-off between factors. Are we severely limited in backup drive space yet have plenty of time to accomplish the backup? If this is the case then I might choose level 7 or 8. Is drive space never ever an issue, yet your evening backup window is? Perhaps level 1 would be the optimal choice for you.
I think 90% of us fall somewhere between those two extremes. Yes, storage is cheap, but rarely ever unlimited. While some of us may have multi-terabyte server farms to backup to; it’s still irresponsible to waste it needlessly. I wouldn’t be awfully surprised though if many of us fall into the “not-enough-hours-in-a-night” caveat where backup time is much more a significant factor. From my testing and actual experience, I’ve found a compression factor of two or three brings the right balance into a variety of environments.
I would love some feedback and/or some opposing viewpoints from anyone; particularly if someone from Quest happens upon this.
Have a Grateful day…Troy