Forum Discussion

klunkySQL's avatar
klunkySQL
Level 3
10 years ago

SQL COMPRESSION with slow transfer

Hello, I'm with a problem about slow backup's using SQLCOMPRESSION. My database runs on a SQL Server 2012 Enterprise edition x64 with 40 CPU's and 256GB RAM. If i start a backup without compres...
  • sdo's avatar
    10 years ago
    Do both types of backups take roughly the same amount of time? if so, then your backup client may be source disk IO bound. And so using compression just makes the backup smaller, which in turn uses less network bandwidth, but a little more CPU - but because the rest of the system is so capable, this exposes the source disk as the bottleneck no matter which backup job configurations you choose. If this is true then NetBackup Client, your network, and your backup target are capable of moving more data and more quickly but are unable to do so because the source disk is not fast enough to put pressure on orher resources. If this is true then you might consider yourself lucky... because... you appear to have only one bottleneck... instead imagine the life of the unfortunate backup admin having to care for a configuration situation where you know you have a bottleneck, but you're not sure where it is... but really one has two different bottlenecks both of which cause a similar IO throughput limit yet one doesn't know it... then what can happen is that... one resolves one bottleneck yet is left with the same IO throughput bottleneck. What I'm trying to get across is that sometimes there are hidden bottlenecks. Remember, there will always be at least one bottleneck somewhere - but sometimes there can be two or more bottlenecks all of which result in the same IO throughput limitations. Also remember that bottlenecks are not a problem worthy of investigation or investment (in effort, or kit/software) unless you are failing to meet your SLAs. You can prove whether your backup job is source disk IO bound by using Resource Monitor and the "Disk" tab. If you see disk volumes at 100% busy with an average sustained IO queue above 1.0 then those volumes are likely to be IO bound. if you see thisnthen you should use PerfMon.msc to investigate further. Before then perhaps investigating infrastructure elements outside of the backup client.