December 22, 2010 at 9:34 am
Hi,
I was wondering if anyone has any resolution to this problem that I'm having. I currently have 64-bit SQL Server 2005 SP2 on my backup server and I am doing a restore database of (10) 600 GB backup files. The estimated time that sys.dm_exec_requests says that the database will complete restoring is 58 hours. Also, the server becomes extremely slow. The CPU usage is very low and there are no other users using this server.
The way I moved the backup files was by external hard drive. I just attached the external hard drive to the server. However, I had to reboot the server because the disk manager service wouldn't load (or restart). We then thought that moving the files to the local drives would help the restore database process since the files were on the external, but that did not help at all.
Does anyone happen to have encountered this problem and have a solution to this?
Any help is appreciated.
December 22, 2010 at 10:25 am
Not sure how much I can help but it sounds like the machine is I/O bound, meaning the I/O subsystem is at capacity, this is often experienced as low cpu activity but a very unresponsive server, I bet the disk lights are flashing fast or nearly solid.
You could look at perfmon and see what the Disk Queue Length was, I bet it is well over 10.
CEWII
December 22, 2010 at 10:33 am
Thank you for the response.
Yes, the Disk Queue Length is very large on the disks that have the backup files and the disks where the database files are located. I believe my problem is that one backup file is on the same physical disk (different logical disk) as the restore database files so I'm moving that one backup file to another physical disk.
Hopefully, it can speed up the restore process.
Would there be any other possible remedies for this situation?
I'm trying to accomplish database mirroring (asynchronous) as a form of HA as soon as possible. I'm just worried about the long restore process while I'm trying to implement it as I'm continuously taking differential backups and transaction log backups until the mirroring is complete.
December 22, 2010 at 10:43 am
That's not a problem I've had to solve, but the winner of this year's Red Gate Exceptional DBA Award, Tracy Hamlin, deals with databases even larger than the one you're talking about. Basically, she walked away from traditional database restores and is using the transactionally aware snapshot capabilities of modern SAN storage systems to do the same thing, much, much faster. I'd try looking in that direction, or possibly even seeing if you can get in touch with her. She might have other suggestions.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 22, 2010 at 11:00 am
Thank you. Do she have an account on here where I can attempt to contact her?
Unfortunately, we do not have a SAN. However, we are looking into them right now.
I have completed the file move and it has saved about 300 minutes.
I'm just curious if this is the fastest restore I can achieve on this server. I just curious why there's such a huge discrepancy between restoring on this server versus restoring on my local machine (which takes about 5-6 hours versus 54 hours on this particular server).
December 22, 2010 at 11:24 am
dajonx (12/22/2010)
I'm just curious if this is the fastest restore I can achieve on this server. I just curious why there's such a huge discrepancy between restoring on this server versus restoring on my local machine (which takes about 5-6 hours versus 54 hours on this particular server).
That is going to depend on the disk configuration ultimately. It certainly is a bit concerning to me that your local machine gets better performance but that too is probably due to disk configuration and potential other activity. Is the drive you are writing the restore to being used by other activity? You did state above that the backup file is not on the same drive you are restoring to correct?
You can look at the restore process spid and start looking at the wait types and that will give you a better view of what you are truly waiting on but at this point my guess is, along with all the others, that you are suffering from storage performance.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 22, 2010 at 11:40 am
Thank you.
I have queried the sys.dm_os_waiting_tasks using the SPID and the wait types are (1) BACKUPTHREAD and (10) BACKUPIO. I guess that means that there isn't enough I/O throughput to help speed the restore. Are there any other DMVs that I can query to check?
The backup files are now located on the local disks that are not being restored to. I don't believe there are any activities that are consuming I/O.
December 22, 2010 at 11:58 am
Also, I've seen spikes of Avg Disk Queue Length of 70+ on the disks where the database files is being restored on.
73 minutes has passed and it is now 2% complete.
Oh, I have tried to restore a database using the GUI and it stopped at 70%. Does anyone know why this may have happened?
December 22, 2010 at 12:03 pm
Another thought... just based on the level of pain, have you checked that your disks are aligned properly? It's not usually an issue, but if your local machine is running better... Here's a white paper on it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 22, 2010 at 12:04 pm
You could try this (pulled from HERE);
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
...but I don't know what that will reveal since the database is not online. Probably not the best view overall for troubleshooting this issue.
Best monitoring for this would be open perfmon and start looking at the queue depth as I believe you have already done and looking at the avg. disk sec/read for the drive that the backup file is on and avg disk sec/write for the drive you are restoring too. These would be under the physical disk counters. Restore operations are typically going to cause these numbers to be higher than what you would normally see for standard OLTP operations but still might be good to see what it looks like to make sure that the disk IO is the problem area.
I'm assuming you are using native SQL backup / restore for this as well. Can you confirm that?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 22, 2010 at 12:04 pm
dajonx (12/22/2010)
Thank you. Do she have an account on here where I can attempt to contact her?Unfortunately, we do not have a SAN. However, we are looking into them right now.
I have completed the file move and it has saved about 300 minutes.
I'm just curious if this is the fastest restore I can achieve on this server. I just curious why there's such a huge discrepancy between restoring on this server versus restoring on my local machine (which takes about 5-6 hours versus 54 hours on this particular server).
I'm not sure how to get in touch with her. If there's no contact info on that web page we might be out of luck. Maybe a bingle search.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 22, 2010 at 12:07 pm
Grant Fritchey (12/22/2010)Maybe a bingle search.
This is new to me. I've got to get out more....
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 22, 2010 at 12:21 pm
Grant:
No, I haven't checked that the disks are properly aligned. I haven't yet finished reading the white paper. I did try to execute a command to check, but the server is pretty bogged down.
David:
Yes, I am using the native SQL Server backup and restore. I am testing out LiteSpeed and I really like it. 😀 Now... To just get management to pay for LiteSpeed, servers, and a SAN... 😛
December 22, 2010 at 12:25 pm
dajonx (12/22/2010)
David:Yes, I am using the native SQL Server backup and restore. I am testing out LiteSpeed and I really like it. 😀 Now... To just get management to pay for LiteSpeed, servers, and a SAN... 😛
We use Red-Gate and are really happy with it as well. Might want to check out their products as well. Have used both, both are good. The Virtual database tools that are coming out from Red-Gate and Idera are pretty intriguing at least and should be considered along with this. Just a thought. I receive no royalties, not even an iPad. 🙁 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply