June 10, 2003 at 7:05 am
I’m hoping that someone can help me improve the speed of a SQL database restore.
The following SQL statement uses a 40/80 GB SuperDLT tape drive to restore a 45 GB database with a 150 MB log file, and it takes 6 hours 45 minutes to execute when the underlying NTFS files do *not* exist:
quote:
restore database ProdCPWQuote from tapedrive with file = 40, unload, stats,norecovery, replace,
move 'prodcpwquotelog' TO 'D:\atlantic\prod\cpwquote\db\prodcpwquotelog.ldf'
The same SQL statement takes 3 hours 26 minutes when the underlying NTFS *do* exist, meaning that SQL Server is "twiddling" with the disk drives for ~3 hours 20 minutes when it creates the NTFS files from scratch. (By the way, the "move" parameter on the SQL statement does *not* appear to affect the overall elapsed time--the restore "strolls along" at the same pace with it or without it, and the slow restore does not seem to be related to this specific server since we have observed similar elapsed times on several of our servers.) In a true disaster, the underlying NTFS files will not exist, and this testing leads me to believe that there will be significant down-time related to the creation of the NTFS files during the SQL restores.
Questions:
What is SQL Server doing when it creates the NTFS files from scratch? More importantly, why is SQL Server doing *anything* with the NTFS files since the SQL restore will overlay the contents of the newly created NTFS files anyway?
How can we improve the database restore time, especially when the NTFS files do not exist?
Thanks,
Jon
June 10, 2003 at 7:24 am
Jon,
6 hours is an extraordinarily long time for a 45gb database. Normally I'd say your tape drive needs looking at, except for the fact that it's about half the time for a pre-existing database. So, when restoring to a new database, given that SQL is taking about 3 hours just to prepare the ground for your database, I would look hard at the write performance of your disk system. Maybe it's RAID 5? Maybe there's some over-zealous virus software operating? Or other factors?
Cheers,
- Mark
June 10, 2003 at 7:25 am
quote:
restore database ProdCPWQuote from tapedrive with file = 40, unload, stats,norecovery, replace,
move 'prodcpwquotelog' TO 'D:\atlantic\prod\cpwquote\db\prodcpwquotelog.ldf'
Have you tested the restoration without 'replace' option when the database does not exist and underlying NTFS files do *not* exist?
June 10, 2003 at 7:45 am
I should have mentioned that in the period of time that SQL Server is twiddling with the NTFS files, the activity light goes off on the tape drive (in the Test and Production environments). The tape drive appears to be absolutely idle for the entirety of the extra 3 hours 20 minutes required for creating the NTFS files from scratch.
>>Maybe it's RAID 5
Yes, we use RAID5 in Test and Production. However, in Production, those disks are EMC-based (i.e., very fast), and the restore still takes roughly the same amount of time as on the Test machine.
>>Maybe there's some over-zealous virus software operating?
No virus software is installed on the server I used for testing. (By the way, the elapsed times I mentioned above came from the Test server.)
>>Have you tested the restoration without 'replace' option when the database does not exist and underlying NTFS files do *not* exist?
No, I haven’t, but it’s worth a try.
June 10, 2003 at 10:15 pm
To improve throughput use LTO 2 Tape Drives directly to the server. Scsii will then become bottleneck.
June 11, 2003 at 12:40 am
Hi there...
Thats is an unacceptible about of time taken to restore such a relatively *small* db. We also use an SDLT tape drive (its the 110/220 GB) with a db of about 50GB and the restore time directly from tape to SQL takes approx 45mins even to RAID5.
This problem sounds more like either a tape drive problem or the drivers for it. Check or upgrade your drivers for both the SCSI card and the tape drive itself.
This does not sound like a SQL problem to me.
-Just my 2 cents worth
June 11, 2003 at 9:02 am
I also have a 45GB database that is backed up with a IBM 40/80 DLT using RAID 5 on 10 Drives. It takes 1Hr 40min to complete a full backup and the restore takes around 4hrs if the files are there and 6hrs if there not. I'm not sure you can speed this up anymore when the files aren't there, SQL needs to create the database structure in the files.
I have 4 servers using the same tape drive and have the same results on all of them. The only way I found to improve performance was to add a second tape drive in a pool or add 15000 RPM SCSI drives instead of 10,000RPM, upgrade Processors...etc.
Cheers
Greg
June 11, 2003 at 11:40 am
Allen_Cui,
quote:
Have you tested the restoration without 'replace' option when the database does not exist and underlying NTFS files do *not* exist?
I tested this and was very surprised to see the restore take even longer. I’m not sure why, though. This is a Test machine with nothing else running on it.
Jon
June 12, 2003 at 5:13 am
To help isolate why the restore is taking over 6 hours, copy the database dump file to disk and then re-try the restore. Note the time this restore takes. The time difference will be down to the DLT tape device.
As gregtm states when the database does not exist, SQL Server seems to be formatting the disk before starting the restore. Run a restore using 'stats=1'. There is always a delay before '1 percent restored' appears whilst SQL Server is setting up the database files.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply