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