Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cjohansen40
Starting Member
3 Posts |
 Posted - 2012-12-10 : 16:28:25
|
| Situation: Our DBAs perform nightly full backups of large databases (1TB+) that live on SAN-attached disk and the compressed backups are written to network disk over 1Gb links. Pretty standard scenario. The problem: Read traffic from SAN disk during these backups can exceed 1GB/sec, which places a pretty big strain on our SAN arrays' ports, CPU, and cache. I've been searching for ideas to throttle the read I/O for these backups to a more reasonable level without impacting normal SQL IO, but other than setting the max transfer size option during backups, I can't seem to find any other options for throttling SAN utilization. I'm sure I'm not the first person dealing with this. Any ideas? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-10 : 19:25:17
|
| First question that comes to my mind is why full backup nightly for large databases. Can't we do incremental? |
 |
|
|
cjohansen40
Starting Member
3 Posts |
Posted - 2012-12-10 : 22:44:28
|
| Sorry that was a typo - we are doing weekly full, nightly differential. But sometimes there are so many changes to the database (large inserts, usually) that the differential is nearly as large as the full. Either way we still would like to be able to reduce the impact of large backups.I played with the BufferCount option, with a minor amount of success, but even setting it down all the way to 1 only resulted in a decrease of about 20% of the IO load during the backup operation. |
|
|
|
johnson_ef
Starting Member
16 Posts |
Posted - 2012-12-11 : 04:14:08
|
| From my understanding, you don't face any issue of performance in SQL box due to Full\differential backups. Only concern is strain on your SAN, which means, don't want to pump data into SAN and don't want to chock SAN bandwidth. to control network pack flow, you can experiment NIC Throttling option. (I don't have experience to comment on this more, just some reading through articles)Second option is, control the backup time when SAN is busy. Yes, you might think, how this is possible when you need backup during off-peak time. You can do with backup in physical drive during off-peak time and then file backup to SAN when SAN traffic is less. In our environment, we also do seperate NICs for backup, that reduces Production NICs load, but this can be done through some third Party tools where we can mention the backup path.-Johnson |
|
|
|
cjohansen40
Starting Member
3 Posts |
Posted - 2012-12-11 : 14:32:43
|
| Thanks for the input Johnson. Interestingly, *writing* the backup isn't the problem - that's working great and causes no bottleneck. It's reading from the source volume that creates the problem, and that is what I'm trying to throttle here.Scheduling the backups during off-peak periods isn't an option - there is no off-peak period. We have 24/7 latency monitors that must remain below a certain threshold (for business reasons, of course) at all times. I've had decent results by throttling the NIC/HBA settings (queue depth and port speed, primarily) but unfortunately that hobbles all traffic from the host and can result in more latency than is caused by the backup read process.I guess I need to go to Microsoft and ask them to reinstate the MaxAsyncIO option.... |
|
|
|
johnson_ef
Starting Member
16 Posts |
Posted - 2012-12-12 : 03:22:32
|
| Sure.. once you get any update from MS, please share with forum team mates. -Johnson |
|
|
|
|
|
|
|
|
Need Ideas to throttle SQL backup IO