April 4, 2012 at 2:20 am
Hello,
We restore a development database each week using a backup of our live database in order for testing to be performed with data that is as up-to-date as possible. This process used to be performed manually and due to the size of the database, took around 3 hours. The process involved backing up the live database, robocopying the backup to the development server and then restoring the database.
We have now automated this process, each step running as a separate step under a Agent job. The process now takes under 1 hour to complete. What I am trying to find out is why there has been such an improvement in performance. Would it be because the backup and restore is running under the SQL Server Agent account and if so, why?
Any advice would be most appreciated,
Andrew
April 4, 2012 at 5:06 am
Highly unlikely.
If it's running at a different time of the day it could be that the disk and network are under less load.
Backup and restore time are principally governed by IO throughput.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2012 at 7:56 am
GilaMonster (4/4/2012)
Highly unlikely.If it's running at a different time of the day it could be that the disk and network are under less load.
Backup and restore time are principally governed by IO throughput.
i had to run these same sort of time based metrics on our production server and found as gale suggested when done in the middle of the day the IO and network activity on our production box slowed things way down. while the backup was being taken and transfered i also had IO and network monitoring going on so i could directly compare time taken vs resource usage by our production app.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 4, 2012 at 8:04 am
The process is running at the same time of day that we used to perform the backup and restore manually.
It's not a problem that it process completes faster but I'd like to know why.
April 4, 2012 at 8:11 am
When you were doing it manually, were always available to do the next step when the current step completed or were there delays do yo other work going on at the same time?
April 4, 2012 at 8:22 am
Andrew
Do you still have the job history going back to when it was taking a long time? If you do, which job steps are showing the biggest improvement?
John
April 4, 2012 at 9:11 am
Hi John,
The biggest improvement was with the restore. The restore usually took about 40 minutes when performed manually (using the GUI) but is now taking about 15 mins when executed as part of a step in the SQL Server Agent job.
Andrew
April 5, 2012 at 1:21 am
That makes sense. I usually find it takes about 25 minutes just to set up a restore through the GUI! That's why I always use a script.
John
April 5, 2012 at 4:13 am
Lynn, There were small delays between each stage as a DBA was not always on hand to kick off the next stage immediately, one of the main reasons we automated the process.
John, why does using the GUI increase the amount of time a restore would take?
Thank you both for your help with this,
Andrew
April 5, 2012 at 4:20 am
Andrew
I was being facetious - pointing out that it's always better to do your backups and restores through the command line than go through the fiddly GUI wizard. That said, it might be worth using the GUI once more and seeing whether it still takes longer. If it does, try scripting the GUI restore - I think there's a Script button somewhere towards the end of the wizard. Compare the results with what you have in your job step.
John
April 5, 2012 at 4:45 am
Hi John,
Sorry didn't get that. We've compared the restore times, 40 mins via the GUI and approx 10-15 mins when executed as code in the Agent job,
Andrew
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply