Parallel restore

Question

Parallel restore

sananthakumar

Old Hand

Points: 389
More actions
May 27, 2009 at 1:16 am

#205402

I tried to Parallel Restore of multiple .bak files in Same System.
SQL Server Object Browser Shows Parallel restoring the DBs.
But it will take lot of time (Ex. 00:5:00 )to restore compare to sequence time(00:4:30). Why It(Parallel) did n't reduce the time.
If anybody know about this Relpy me,
Thanks,
Ananth

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 1

Parallelism will help you for sure but not always ...it depends upon the balance between the resource consumption and time to execute .. you need to test and find how many database backups in parallel will complete faster rather than executing them serially ..

If you are consuming a lot of resources through parallelism it will actually hurt as it will consume a lot of resources on the server .

Let me give you a situation i faced very reciently .

We had to implement log shipping for 800 databases .So it will create 800 backup jobs and will be kicked off at the same time . It not only took a lot of time but also took lot of server resources slowing down almost everything .

So , I disabled all the jobs and created a new job to execute those backups serially and gusee what its so much fast now ..

HTH

Abhay

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

sananthakumar Old Hand Points: 389 More actions · Answer 2

hi Abhay,

iam just doing a sample task , of restoring 4 databases Parallely as well as sequentially, through C# code. (all together size of DB's are 2GB)

Parallel Time: 1 minute 45 Seconds (approx)

1st DB: 50 th second

2nd DB: 1 minute 20 th second

3rd DB: 1 minute 45th second

4th DB: 1 minute 45th second

Sequetial Time: 1 minute 20 Seconds (approx)

1st DB: 5 second

2nd DB: 30 Second

3rd DB: 25 Second

4th DB: 20 Second ( Total 1 munute 20 Second)

after several iteration the parallel time always takes more time. but the same parallel code other than restoring databases in SQL SERVER like (Copying files in code, Deploying IIS through code or any other processes) saves time. When it comes to sql server it takes more time. i need to give more specific reason to my team.

i could understand answer like stress will more on sqlserver ( server resources used more) which is not good.

can u tell me like specific reason does sqlserver can have only one active connection at a time ( Is this true or anything like this).

Thanks

Raja

looking for an urgent reply

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 3

Yes you are correct .SQL Server is non-preemptive as opposite to OS which is preemptive .

this is a short answer .let me know if you want to know how SQL fools OS and becomes non-preemptive when OS is the King and sitting on the head of all the applications .

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)