SSIS Catalog slower than package deployment

We are having a problem with our Data Warehouse load process taking well over an hour longer than expected.  Taking a look at our logging, every package is running just a second or two slower, which is translating into an increase of the load time from 4:30 - over 6 hours.

Background
SQL Server 2014 Enterprise
Tools are all version 12

The WH Load is made up of 61 systems that are required to be loaded, consisting of 12 different system types.  The most common system type has 31 systems that all execute the same packages.  There is a control system that manages the executions.
This equates to 2237 package executions over the course of the night.  We have systems all over the world, and have to wait for each one to pass midnight before we load, so we can only start to load systems that are at GMT-5 very late (we are at GMT+1).

All packages were upgraded to version 12, and converted to project deployment at the same time.  We also reviewed each package and removed some old code we no longer need, so there is not much we can do to make them quicker.

Taking a look through our historical logs, each package is running slightly slower.  Some of these packages only used to run for a total of 1 - 2 minutes over 31 executes, but this is now taking 2 - 3 minutes over those same executes.
I chose one package that loads a fact.  The package deployment execute took 191 seconds for 32 executes.  Project deployment took 377 seconds for 31 executes. I checked the catalog.executions, and that is showing a total of 364 seconds for the 31 executes.  I am using a custom SP to execute packages, so I do expect a small additional overhead there.

We have turned all logging off for the executes to see if that was causing a problem, but there was no change.

Has anyone seen any thing similar or can point me in the direction where I can find the cause of this issue?

Thanks

July 22nd, 2015 8:20am

I do not see the relationship between deploying packages and execution speeds of a package. Perhaps you have a large number of packages running simultaneously, on an not adequately sized machine(s).

There is always ways to speed the executions up, but you need to better profile the runs.

Over what period of time the processing got slower you do not tell as well as you do not tell about the growth in volume of data.

So in short, unable to help having 0 specifics.

Free Windows Admin Tool Kit Click here and download it now
July 22nd, 2015 3:25pm

The change is over a week, volume size is the same.
July 23rd, 2015 3:30am

Again, please try to answer all the questions above otherwise not having the discovery phase properly done is not going to yield a resolution.

And you need to profile the load to see where the package(s) slow down. DI suggest looking at the SSIS operational reports https://msdn.microsoft.com/en-us/library/hh213362.aspx?f=255&MSPPError=-2147217396 They have quite a bit of insight.

Free Windows Admin Tool Kit Click here and download it now
July 23rd, 2015 10:50pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics