September 20, 2011 at 2:03 am
Has anyone seen performance behaviour where a backup of a large database to a fileshare slows down as it progresses eg the network utilisation starts at say 25% then gradually slows down to say 5%. When one database is finished and the next one starts in a script, it jumps back up again to 25%
Plotting out the network utilisation thru the NIC you can almost rule a straight line from the start to the finish.
The perfmon counter object Device Throughput Bytes/Sec shows the same thing. The difficult thing is trying to work out where the bottleneck is and why this type of behaviour.
I would like to see the throughput remain more-or-less constant.
SQL Server 2005. Windows 2003 R2. Windows failover cluster ie we are backing up databases on one node to a disk on another node via a fileshare using a UNC filename.
Thanks.
September 20, 2011 at 4:37 am
suggestions :-
1) create 2 step in your job. First, take backup on local server. Second, Copy the backup file to destination via robocopy.
2) If compression already in use and still problem with big backup size, then try to break the backup in equal size files (1 gb or so)
----------
Ashish
September 20, 2011 at 5:09 am
September 20, 2011 at 9:12 am
Network backups are notorious for poor performance, although this precise behavior is not one I've seen. Generally, you're better off backing up to attached storage and then copying to a network location.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 20, 2011 at 6:31 pm
Jayanth_Kurup (9/20/2011)
Is the utilization drop gradual over time or more like a step ? Are you verifying the backup after finished ?
The utilisation drop is gradual eg starts at 25% and slowly drops to 4 over 10 hours in this case%. No backup verification. If it stayed constant at say 20%, then it would take less than 10 hours.
September 20, 2011 at 6:36 pm
Grant Fritchey (9/20/2011)
Network backups are notorious for poor performance, although this precise behavior is not one I've seen. Generally, you're better off backing up to attached storage and then copying to a network location.
I agree. At this point I am looking for a 'quick fix' to a problem of excessive database backup times eg a network setting or SQL Server setting that can be tweaked. Next step would be to re-engineer the backup process.
September 20, 2011 at 7:08 pm
This is just a bandaid rather than a full solution... but I remember reading from a trusted source that he dramatically increase the backup speed by backing up to 4 files at once (same network / drive).
He tested all the way up the 8 but at that point it wasn't making a real difference.
The real solution obviously is to check what's wrong with the network & or those drives.
Might be as simple as the nic card being set at 10 mbps instead of 100. Maybe something else hapenning at the same time stealing the ressources.
What bugs me is the gradually slower part. If something breaks / changes it's going to be sudden and not incremental.
Do you have an AV scanning those folders or a 3rd party backup solution?
September 23, 2011 at 8:45 am
Perhaps you could try copying a large file to the same network share, and observe if the same behaviour is exhibited. If it is, then it is probable that it is an issue with the network path, or some configuration on the remote node. You could then try copying the same file to a different network share, preferably a plain vanilla box, and if the same behaviour is exhibited, then it's more likely something to do with the NIC/NIC configuration.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
September 25, 2011 at 10:09 pm
Ray Mond (9/23/2011)
Perhaps you could try copying a large file to the same network share, and observe if the same behaviour is exhibited. If it is, then it is probable that it is an issue with the network path, or some configuration on the remote node. You could then try copying the same file to a different network share, preferably a plain vanilla box, and if the same behaviour is exhibited, then it's more likely something to do with the NIC/NIC configuration.
I had already done a speed test using basic file copying, hoping to reproduce the problem without SQL Server being involved. No luck, the NIC utilisation remained more-or-less constant during the file copying.
I am beginning to think there was something strange going on on that particular month end. Will park it for now and monitor it again the next month end.
June 20, 2012 at 5:20 am
Hi,
Perhaps it is related to information in link below ??
I'm having the same problem
Regards Andre Lucas
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply