SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Need Ideas to throttle SQL backup IO
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cjohansen40
Starting Member

USA
3 Posts

Posted - 12/10/2012 :  16:28:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/10/2012 :  19:25:17  Show Profile  Reply with Quote
First question that comes to my mind is why full backup nightly for large databases. Can't we do incremental?
Go to Top of Page

cjohansen40
Starting Member

USA
3 Posts

Posted - 12/10/2012 :  22:44:28  Show Profile  Reply with Quote
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.
Go to Top of Page

johnson_ef
Starting Member

India
16 Posts

Posted - 12/11/2012 :  04:14:08  Show Profile  Reply with Quote
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
Go to Top of Page

cjohansen40
Starting Member

USA
3 Posts

Posted - 12/11/2012 :  14:32:43  Show Profile  Reply with Quote

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....
Go to Top of Page

johnson_ef
Starting Member

India
16 Posts

Posted - 12/12/2012 :  03:22:32  Show Profile  Reply with Quote
Sure.. once you get any update from MS, please share with forum team mates.

-Johnson
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000