Network Backup is slow - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Administration

Network Backup is slow

Author

Topic

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2014-07-14 : 11:15:52

Hello,

The SQL server does backups with this command to a network share \\BackupServer\weekeklybackups"


BACKUP DATABASE DBNAME TO DISK = '\\BackupServer\weekeklybackups\DBNAME.BAK'

There is a 1 gig network between the SQL Databse server and the BackupServer. but it uses maximum 25% of the network, so the backup is taking for ever.
If I make the database locally and copy it over to the same network share, it is faster using 99% of the network bandwidh.

So, the weekly automated backups are taking a long time, which is an issues

When I check the monitor it says it is waiting with "ASYNC_IO_...". When I did some google search it says, this is an issue on tape backup. But this is on iSCSI hard

disks. and manual copy over works fine. So, what could be the issue? It was working fine for a year until last week. Not sure what happened. but where should I be looking in to?

Thanks for your time

D

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-14 : 12:12:50

You should be contacting your network and server engineers since the local backup is fine. And why not 10 gig for the network? I highly recommend 10 gig for network backups, otherwise I recommend local backups+copy to network share.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2014-07-14 : 12:52:30

Thanks Tara. Since the network utilization during copy over is 99%, they say nothing is wrong with the network.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-14 : 12:56:20

Try backing up to the nul backup device to see how fast SQL can back it up without any interference from things like IO and network.

TO DISK = 'NUL'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2014-07-14 : 16:41:14

Hey Tara,

When I do to Nul, the iSCSI disks where the Data and Log files go up to 20% as shown below. Nothing on network as expected

http://screencast.com/t/yRZRdMtxVjPV

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-14 : 16:53:09

But what did it show for the output in SSMS?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2014-07-15 : 09:04:59

nothing. It justs runs the backup. No messages. but I stopped in the middle.

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2014-07-15 : 09:08:06

Here are more tests I have done:

1. Ran the same backup from another/similar sql server to the same network backup server.
Same issue - 25% percent usage on the network link what it used to be 99%.

2. Did a restore from the same network server. It is using 99% of the network. So, only the backups are slow and using 25% of the network line.

3. Again, if I do a manual copy paste a database backup file, it uses 99% of the network link.

So, it is not the network.

Can the disks loose writing speed overnight?

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2014-07-15 : 09:20:05

When I ran the NUL backup statement on a small database, it completed and this is what it shows.


Processed 30952 pages for database 'DBNAME_US', file 'DBNAME_TEMPLATE' on file 1.
Processed 2 pages for database 'DBNAME_US', file 'DBNAME_TEMPLATE_log' on file 1.
BACKUP DATABASE successfully processed 30954 pages in 1.609 seconds (150.292 MB/sec).

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2014-07-15 : 12:07:35

DB backup to WanDBbackup1 E drive:
http://screencast.com/t/Xm1i2ld4

A Copy to WanDBbackup1 E drive
http://screencast.com/t/Vf8QPmRAQnH

Avg.Disk sec/write seems to be much lower when it is a direct copy. Would that mean SQL server writes take longer and direct writes take shorter. is this normal?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-15 : 12:38:09

I don't think the small database test is sufficient for this.

For your PerfMon captures, I need to see the values for Avg Disk sec/Write and Avg Disk sec/Reads, not a graph. Go to the dropdown and select the report option. When the values stay pretty much constant, do a screenshot.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2014-07-15 : 12:57:38

ok. Thanks Tara.

This is for the Backup.
http://screencast.com/t/wRD7bL0H

This is for the copy over.
http://screencast.com/t/Au0GAQTQR2nX

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2014-07-16 : 13:25:14

We sort of isolated this issue to the Disks. If we run the backup on C drive it seems fine but the usual backup drives are slow. One thing I can't understand is a direct copy on those drives are fine. Not sure what else can be an issue here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources