Why No speed benefit when using multiple backup devices to back up a database?
I am running SQL Server 2005 on a Windows 2003 Advanced Server machine.
It is a test set up with one running instance of SQL Server 2005 standard and Microsoft Test databases only. I am running SQL Server directly on the machine and the server\discs are not subject to any external load or network traffic.
I am running tests to compare different back up scenarios to find the optimum methodology (with speed in mind)
I am surprised that when I back up Adventureworks using 2 backup devices, each on a seperate local disk, the backup time is double that of a backup to a single device.
The microsoft documentation suggests that a parallel backup into two devices will halve the time required to complete the back up. This seems logical.
Can anyone explain to me why it is taking longer. Thanks. The code I used is attached.
It is a test set up with one running instance of SQL Server 2005 standard and Microsoft Test databases only. I am running SQL Server directly on the machine and the server\discs are not subject to any external load or network traffic.
I am running tests to compare different back up scenarios to find the optimum methodology (with speed in mind)
I am surprised that when I back up Adventureworks using 2 backup devices, each on a seperate local disk, the backup time is double that of a backup to a single device.
The microsoft documentation suggests that a parallel backup into two devices will halve the time required to complete the back up. This seems logical.
Can anyone explain to me why it is taking longer. Thanks. The code I used is attached.
BACKUP DATABASE AdventureWorks
TO AdventureWorks_F_6, AdventureWorks_F_7
WITH DESCRIPTION = 'Daily split disk full Backup of Adventureworks',
NAME = 'Adventureworks_F_200904171400'
chapmandew
When you take the backups, are they both the same size?
ASKER
The .bak =files that are produced on each drive are different sizes, but only by 500kb. For detail, the output from running the backup TSQL is produced below:
Processed 20992 pages for database 'AdventureWorks', file 'AdventureWorks_Data' on file 1.
Processed 3 pages for database 'AdventureWorks', file 'AdventureWorks_Log' on file 1.
BACKUP DATABASE successfully processed 20995 pages in 54.877 seconds (3.133 MB/sec).
Processed 20992 pages for database 'AdventureWorks', file 'AdventureWorks_Data' on file 1.
Processed 3 pages for database 'AdventureWorks', file 'AdventureWorks_Log' on file 1.
BACKUP DATABASE successfully processed 20995 pages in 54.877 seconds (3.133 MB/sec).
Are they significantly smaller than they would be had you created one large .bak file?
ASKER
The size of a single backup file is sum of the two files when split accross two devices.
Splitting the load helps avoid disk throughput limitations but your post says 3 MB/sec which is way too low for that to be an issue anyway. I'd speculate you have something wrong outside of the backups. What kind of box is this on?
do your disks have seperate write heads?
ASKER
This is on an old standalone server - a DELL Poweredge 500SC, commissioned about 2003.
It has a 1GB processor, 384mb of memory and 2 fixed disks.
The two fixed disks:
1 basic disc of 40GB with 4 8GB NTFS partitions on it
1 NTF Dynamic disk of 232 GB
both disc are reporting healthy.
Whilst the power of this box is derisory and it is getting on in age, please remember that this is a 'toy' server. It only runs SQL server and is used for self teaching and trying out coding ideas etc. It is not used for any sort of physical or realtime benchmarking or development\testing.
With that in mind, and accepting the known limitations of the kit, surely a parallel backup to 2 devices on two seperate disks should be faster than an identical backup to one? Regardless as to the fact that all backups will be very slow relative to running the same test on proper kit.
That is where my logic was coming from. All I really want to prove or disprove is the Microsoft claim that parallel backups to two devices on seperate disks is faster than a back up to a single file on one device. I have found that using two devices doubles both the backup and the restore times.
It has two seperate fixed disks one of
It has a 1GB processor, 384mb of memory and 2 fixed disks.
The two fixed disks:
1 basic disc of 40GB with 4 8GB NTFS partitions on it
1 NTF Dynamic disk of 232 GB
both disc are reporting healthy.
Whilst the power of this box is derisory and it is getting on in age, please remember that this is a 'toy' server. It only runs SQL server and is used for self teaching and trying out coding ideas etc. It is not used for any sort of physical or realtime benchmarking or development\testing.
With that in mind, and accepting the known limitations of the kit, surely a parallel backup to 2 devices on two seperate disks should be faster than an identical backup to one? Regardless as to the fact that all backups will be very slow relative to running the same test on proper kit.
That is where my logic was coming from. All I really want to prove or disprove is the Microsoft claim that parallel backups to two devices on seperate disks is faster than a back up to a single file on one device. I have found that using two devices doubles both the backup and the restore times.
It has two seperate fixed disks one of
I wasn't meaning to insult your server :-)
I just wouldn't expect to see any benefit unless you're getting higher throughput. You might, for example be going from a backup on a single fast scsi drive to splitting the backup between that drive and a slower ide which also is busy with a windows swap file.
Having said that, I'll admit I have no experience splitting backups as you describe so anything or everything I say might be meaningless gibberish.
I just wouldn't expect to see any benefit unless you're getting higher throughput. You might, for example be going from a backup on a single fast scsi drive to splitting the backup between that drive and a slower ide which also is busy with a windows swap file.
Having said that, I'll admit I have no experience splitting backups as you describe so anything or everything I say might be meaningless gibberish.
ASKER
No insult taken :-) It really is a peice of old tutt - but fine for the purpose that has kept it out of a skip!!
Whilst there is no other activity on the server, the swap file is a good point as that is hosted on the system partition of the of the drive that is being used for backups. My OS knowledge is a little thin - would I be right in saying that running SQL and Windows Server with such scant resources will result in raised swap disk activity? If this is the case, as you say, there is a conflict there.
As a matter of good experimentation - will see if I get a better throughput creating a single file backup on the other disk, not the 'backup' disk.
Just as a point of interest, my goal with these experiments is to try and find a way of running full backups of large databases quicker, using only native tools (i.e. not litespeed) and withouit resorting to differentails etc. Any other ideas?
Thanks
Whilst there is no other activity on the server, the swap file is a good point as that is hosted on the system partition of the of the drive that is being used for backups. My OS knowledge is a little thin - would I be right in saying that running SQL and Windows Server with such scant resources will result in raised swap disk activity? If this is the case, as you say, there is a conflict there.
As a matter of good experimentation - will see if I get a better throughput creating a single file backup on the other disk, not the 'backup' disk.
Just as a point of interest, my goal with these experiments is to try and find a way of running full backups of large databases quicker, using only native tools (i.e. not litespeed) and withouit resorting to differentails etc. Any other ideas?
Thanks
ASKER
Well, Having run the back up using a single device on each disc, it is clear that the culprit is the large 232GB dynamic disc which takes twice the time as the other one.
I assume that the speed recorded for the backup on dual devices is limited to the slowest device. So I guess that answers that one - except, any idea what is likely to make this disk (added brand new quite recently) 100% slower? Any idea what tools I can use to diagnose - Always keen to learn something new ! :-)
Thanks for your help on this
I assume that the speed recorded for the backup on dual devices is limited to the slowest device. So I guess that answers that one - except, any idea what is likely to make this disk (added brand new quite recently) 100% slower? Any idea what tools I can use to diagnose - Always keen to learn something new ! :-)
Thanks for your help on this
Yes, that would be a worthwhile test. What size db are we talking about? We had some 10 - 30 GB databases that tended to kill our old servers for extended periods when we would run a full backup. So the answer was - don't do those very often and eventually upgrade the hardware. But some random thoughts would be:
1. We have some db's backed up once / week because we can live with a lengthy recovery time while we cycle through the week's worth of log backups (no differentials). The full backup runs in the wee hours during the weekend when it doesn't much matter how long it takes. A little tricky to recover using these files on a different server but not insurmountable.
2. Backups are effective in throwing data at hard drives and the nicest answer is hardware RAID 0 or 10 and fast drives. Striping 2 (or 4) drives you'll likely cut backup time in half. 3 (or 6) would cut the time to 1/3 of the original. With progressively less payback as you add more of them. You might fire up perfmon to watch disk bytes/sec while doing a backup to a single drive to estimate requirements and times.
3. Big bucks but I notice Sql 2008 Enterprise can compress backups which I might guess would speed them up given enough cpu power.
4. I haven't used these but I think you can split databases up into file groups and back up individual file groups probably to kind of split up the work, though I like the simplicity of just staying with a single full followed by a bunch of log backups.
1. We have some db's backed up once / week because we can live with a lengthy recovery time while we cycle through the week's worth of log backups (no differentials). The full backup runs in the wee hours during the weekend when it doesn't much matter how long it takes. A little tricky to recover using these files on a different server but not insurmountable.
2. Backups are effective in throwing data at hard drives and the nicest answer is hardware RAID 0 or 10 and fast drives. Striping 2 (or 4) drives you'll likely cut backup time in half. 3 (or 6) would cut the time to 1/3 of the original. With progressively less payback as you add more of them. You might fire up perfmon to watch disk bytes/sec while doing a backup to a single drive to estimate requirements and times.
3. Big bucks but I notice Sql 2008 Enterprise can compress backups which I might guess would speed them up given enough cpu power.
4. I haven't used these but I think you can split databases up into file groups and back up individual file groups probably to kind of split up the work, though I like the simplicity of just staying with a single full followed by a bunch of log backups.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all your comments on this. I am still not totally clear as to what is going on but you have helped me immensely in thinking through the issues involved - and as you say, it could be so many thinks! Cheers