Find Your SQL Server Backup Bottleneck
LiteSpeed’s Backup Analyzer can do more than just find the right backup compression and encryption settings like I discussed in an earlier LiteSpeed video tutorial. It can also help pinpoint your SQL Server’s backup bottleneck, showing why you can’t back up your databases faster.
Here’s a screenshot of the Backup Analyzer’s report:
LiteSpeed Backup Analyzer
When you’re looking for the backup bottleneck, check out these columns:
- Read Speed – indicates how fast your data files can pull the database off disk and give it to the CPU. This is the first step in the backup process – getting the uncompressed data in. This number is dictated by the speed of the storage subsystem where your SQL Server data files live, and by the connection between your server and the data files.
- Throughput – indicates how fast your CPUs can process the data. The more compression and encryption performed, the less data can move through here. To improve this performance, use less compression or a weaker encryption method. (Generally, though, this isn’t a bottleneck except on very old servers with slow CPUs.)
- Write Speed – indicates how fast your storage subsystem can write out the compressed data. This is the last step in the backup process. To improve this number, you can use faster drive arrays or stripe the data between multiple drives, and the Backup Analyzer can be used to test those scenarios too.
In my screenshot above, my lab server’s data drives are connected via iSCSI. Notice that my read speeds are consistent across all of my tests. That’s because I just flat out can’t read the data any faster than I’m reading it. The only solution to make this server faster is to upgrade the connection between the data file drives and the server itself.
However, since I know I just can’t read that fast, that means I can use as much encryption and compression as I want without affecting performance. In this situation, I might retest with stronger encryption methods and higher compression levels. Even though I’m read-constrained, that doesn’t mean I can’t get benefits from using LiteSpeed’s compression.
If you’re confused about the output from Backup Analyzer, feel free to contact Quest support or leave us a comment below with the output from your tests.
