| Author |
Topic |
|
rb1373
Yak Posting Veteran
93 Posts |
 Posted - 2005-12-14 : 02:07:34
|
| I recently moved my backups from the wizard generated maintenance plans to a simple backup script. My 180GB database is now taking 7 hours to backup with instead of the normal 4 hours. The step details show 7.359 MB/sec when the backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4 hours.Below is my script. This is actually step 2. Step 1 deletes the previous backup.BACKUP DATABASE MyDBTO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'WITH INITDuring the backup, pinging the NAS device from the SQL Server takes 0ms.I have used Perfmon and the counters seem usual from what I have researched (e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).Average Device Throughput bytes/sec: 5570346Average % Disk Time: 3.854Average Disk Queue Length: .0193Average IO Write Bytes/sec: 5897638Average Split IO/sec: 0As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent - Alert Engine.Does anyone have any suggestions as what is causing the backup time to almost double?Thanks,raySS2K |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-14 : 02:22:42
|
| Were you backing up to the NAS device before, or a local drive?Is the folder on the NAS box compressed now (whereas it was UNcompressed before)?Kristen |
 |
|
|
rb1373
Yak Posting Veteran
93 Posts |
Posted - 2005-12-14 : 09:25:09
|
| Yes I was backing up to a NAS before. No compression is occurring on the NAS device. Nothing really has changed except going from the maintenance plan to a script. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-14 : 10:08:18
|
| "Nothing really has changed except going from the maintenance plan to a script"I'm stumped then!Kristen |
|
|
|
rb1373
Yak Posting Veteran
93 Posts |
Posted - 2005-12-14 : 12:11:00
|
| Anyone else have any ideas??? |
|
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2005-12-15 : 09:32:06
|
| Have you tried varying the time the backup runs? Maybe somebody else is running something on the NAS at the same time. Have you tried switching back to the Maintenance plan and running at the same time? Is that the entire script, or were there other options like backup verification?Just trying to ask more questions to spur thought. Like Kristen, I'm a little stumped.Jon-Like a kidney stone, this too shall pass.http://www.sqljunkies.com/weblog/outerjoin |
|
|
|
rb1373
Yak Posting Veteran
93 Posts |
Posted - 2005-12-15 : 11:52:26
|
| Thanks Jon! Normally the backup runs from 7 PM to 11 PM. I have moved it earlier to 5 PM but it still runs about 7 hours. I haven't started the backup any later because the backup file is used to restore a standyby server. I will try varying the backup times this weekend. I was also going to try switching back to the maintenance plan to see the results. There is no verification happening (I've made that mistake before with the maintenance plan and saw the backup time double). I am also going to look at more network counters with Perfmon. I have read that ping is not a very good measurement. As for other resources using this maintenance window, I have moved all other SQL Server processes out of that time slot. Plus this particular NAS is dedicated to the 180GB database. One other thing to note, the drive on which the mdf resides is 97% fragmented (this was the case prior to the 7 hr backups). I'm not sure if disk fragmentation plays a role. We are planning on defragging this drive soon. That's a lot of writing. I hope its all clear. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-15 : 13:10:42
|
"the drive on which the mdf resides is 97% fragmented"Whilst that would be good to fix, I doubt that happened at the instant that you changed from Maint Plan to Script Backups though  so doesn't sound like a factor.Kristen |
|
|
|
rb1373
Yak Posting Veteran
93 Posts |
Posted - 2005-12-15 : 15:17:59
|
| Thanks Kristen. Some additional research shows that all of my SQL Servers backing up to the NAS devices have throughputs of 1-12 MB/sec. That doesn't seem right. What should I expect to see in terms of throughput? I have another SQL Server that backs up locally and it has a throughput of 15-24 MB/sec. All of the SQL Servers and NAS devices have 1 GB cards. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-15 : 15:20:54
|
| "All of the SQL Servers and NAS devices have 1 GB cards"Worth checking that the Network Settings are correct - not manually set to half duplex, 10MBit, "crawl" or some-such.Try copying a (big-ish) file from each machine with a 1GB card to the NAS box, how long does it take?We discovered the settings were wrong on a link from a Web Box to the SQL Box the other day - it only came to light when I copied a 10GB file and it took 3 hours. Fixed the network settings and then it went through in a couple of minutes. Much better!Again, not sure why that would have changed at the time you went from Maint.Plan to Script Method, but it may be happen-stance of course ...Kristen |
|
|
|
|
long backup duration