Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Master and child packages performance issue Expand / Collapse
Author
Message
Posted Thursday, February 18, 2010 1:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 3:14 AM
Points: 19, Visits: 104
Hi, we have run into a performance issue with a master package calling child packages and would really appreciate some advice.

We have three SSIS projects, each consisting of a set of child packages and a master package that performs some admin tasks and then call all the child packages. All of the child packages are executed with ExecuteOutOfProcess=false.
Master1: admin task -> execute package1.1 -> execute package1.2 …-> execute package1.n-> admin task;
Master2: admin task -> execute package2.1 -> execute package2.2 …-> execute package2.m-> admin task;
Master3: admin task -> execute package3.1 -> execute package3.2 …-> execute package3.k-> admin task;

When executed separately using the Execute Package Utility, the execution times for these packages are (mm:ss)
Master1 05:27
Master2 25:42
Master3 11:50

We want to schedule these packages and add some additional conditions, however, simply creating a new Master package that calls these 3 packages sequentially (obviously using the same data sets), increases the execution times to
Master1 05:29 = an increase of 0.5%
Master2 32:33 = an increase of 27 %
Master3 36:28 = an increase of 208% !!.

We have tried to set ExecuteOutOfProcess=true on the Execute Package tasks but that only increased the execution time further.
I’m not sure what causes this and was not able to find anything similar on the forums.
Any help would be appreciated.
Thanks
Post #867790
Posted Thursday, February 18, 2010 1:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:35 AM
Points: 5,037, Visits: 11,777
It would be useful background if you would explain a bit about what the packages are doing - what is Master3 doing that the other 2 are not?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #867798
Posted Thursday, February 18, 2010 1:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 3:14 AM
Points: 19, Visits: 104
The packages all perform the same type of tasks - reading from database tables, performing transformations and loading data into tables. There are no significant differences.
Post #867801
Posted Thursday, February 18, 2010 2:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:35 AM
Points: 5,037, Visits: 11,777
Then there is a suggestion that the time increase gets worse as each package runs.

Can you swap, say, Master1 and Master3 to confirm this?

It would be useful to know whether it's a general degradation, as the numbers suggest, or a package-specific degradation.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #867814
Posted Thursday, February 18, 2010 7:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 3:14 AM
Points: 19, Visits: 104
OK, so I have set up a test package that calls master1 4 times in a row while resetting things like target database tables to their original state in between calls. These are the results:

Master1 call1: 04:40
Master1 call2: 05:42 = increase of 22%
Master1 call3: 06:44 = increase of 44%
Master1 call4: 08:29 = increase of 82%

There has to be some sort of resource problem
Post #868017
Posted Thursday, February 18, 2010 9:24 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:30 AM
Points: 346, Visits: 518

Yes ... I am using 3 level of package hierarchy and performancewise it has been absolutely fine...



--
Sabya
Post #868145
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse