April 25, 2009 at 6:29 am
Recently I carried out some testing of large database backup using (12) striped backup files. All of the backup files were backed up to the same disk drive and location e.g f:\sql\dumps\largedb_01.Bak through to :\sql\dumps\largedb_12.Bak. My initial thoughts were that this would reduce the amount of time to take the backup instead of using on large single backup file.
However, my testing indicated the backups times were virtually the same. I was under the impression that SQL Server would spawn off a different thread for each backup file and would therefore process in parallel and I had assumed this would therefore be faster.
I carryout striped backups using Sybase in a similar manner and a stirped backup set is much faster than a single backup file, hence my reasons for thinking SQL Server would operate in a similar manner.
If anyone could throw any light on this I would appreciate it.
April 25, 2009 at 10:13 am
virtually the same? was it better at all?
I guess i would have expected it to be faster, perhaps 12 stripes was overkill and you got contention. They are all going to the same drive so that won't help, can you try splitting across drives (doesn't have to be 12 different drives!)
---------------------------------------------------------------------
April 25, 2009 at 10:57 am
How many CPU's on this server?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 25, 2009 at 11:20 am
Thanks for the question, there are 4 CPU's.
April 25, 2009 at 11:23 am
Thanks for the reply I did try it with 6 stripes as well and it was a matter of maybe 5 or 10 seconds difference on a 30 Gb database.
I was wondering if it was write contention especially as it was all to the same drive. I still would have expected to have seen some performance improvements.
Haven't tried to run stripe across different drives as we have 4 configured 1 for O/S , one for Data, one for Logs and one for Backups.
April 25, 2009 at 5:10 pm
I think you would have to stripe across separate drives so see good improvement, that's the recommendation if you are using striping to get faster throughput.
---------------------------------------------------------------------
April 26, 2009 at 3:37 am
I thought that maybe the case but was hoping it wasn't. I was thinking that the uses of multiple threads would alllow it to write in parallel to the single disk but it may well just be contention. I will have to look at other alternatives so long as we have the budget.
Cheers...
April 27, 2009 at 11:43 am
Striping will only help if your I/O subsystem can handle it.
I have done extensive testing over the years and have found that 4 threads usually gives me the best performance. I For example:
if the backup job takes 4 hours with one file, then the same backup job with 4 files on the same drive will probably take about 1 hour 15 to 1 hour and 30 minutes minutes. Again, if your I/O subsystem can handle it. (these numbers are from a 500 Gb database with a backup file size of 300 Gb).
If you want further performance improvements you need to go to separate drives on separate disk controllers - then with 4 files you might get it down to an hour.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 27, 2009 at 11:52 am
Rudy, have you seen any differences if there are more CPU's available? I have not seen a large difference between using 3 threads and 7 threads with 8 CPU's available. But, I have seen that going above the number of CPU's available does have a negative impact.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 27, 2009 at 12:00 pm
For some reason 4 threads is kind of magical, whether there were 2, 4 or 8 CPUs. It did not matter if the CPUs were physical, hyperthreaded or physical (single or dual core). As for why I could only offer poor conjecture at best.
Oh and more recently (last month) I tried it on 580 G5 with 8 dual core Xeons not hyperthreaded and 4 stripes still came out the winner.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 28, 2009 at 7:21 am
Many thanks for your comments, I will test again using the 4 backup stripes as recommended and see if this improves the run duration. It maybe that I had over stretched it by having more stripes than the number of CPU's...
May 1, 2009 at 8:47 am
Sorry to interrupt you in between, i have never used striped backup but wanted to explore it. Today i have tried to take striped backup for my test database(named working) it worked fine but when try to restore i have got below error:
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "working" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I have used below commands for backup and restore:
backup database working to
disk ='C:\backup1C.bak',
disk='d:\backup2D.bak',
disk='E:\backup3E.bak'
with init
restore database working
from disk = 'C:\backup1C.bak',
disk = 'D:\backup2D.bak',
disk = 'E:\backup3E.bak'
with stats = 10
Please let me know if this is not the right way or where i am making mistake.
thanks,
May 1, 2009 at 11:47 am
Add the WITH REPLACE clause.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
May 2, 2009 at 3:13 am
Sorry earlier I missed it, striped backup is useful to split backup file size in multiple files so that it can be easily transferable OR if you are facing disk crunch on a particular drive. Is there any other benefit using striped backup.
thanks,
DKG
May 2, 2009 at 4:02 am
reduced elapsed time for backup, spread the i/o load.
that's about it
---------------------------------------------------------------------
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply