We have some fairly substantial packages running as part of an over night warehouse load.
Two nights ago they failed due to a connectivity issue the package couldn't find the source server.
As a result we had to run the packages the following day during business hours. Now to some extent we expect the packages to run slowly during hours where the servers are being hit by users accessing them, however the slowdown was emence.
Normally the packages combined take 1-2 hours over night, but yesterday having re-started them at 8am they where still only 1/2 though at 6pm. Packages with a normal run time of 2-3minutes had taken in some cases 2 hours.
In the end it was so late i cancelled it and allowed it to run it's self as normal that evening and all appear's to be well this morning.
Im not a DBA so in no means an expert but as far as i can tell the servers wheren't being overloaded, at they seemed to be running at about 30% in terms of Memory and CPU which is fairly typical for those servers at that time of day.
Infact the only thing i could see that seemed out of the ordinary was the number of lock was incredibly high, spiking into the billion's on several occasions.
Again im not a DBA but i assume this is because the users /reports etc are taking lock's out on rows/tables so the packages had to wait for them to free up?? And it doens't happen on over night loads because the users are tucked up in bed (hopefully
)
We have never had this problem before but this is perhaps because we now have more systems/users than we used to, but as our systems are still growing im guessing the problem will get worse?
Essentially this whole mess caused an entire days down-time for our report server which is unacceptable. We are looking at having packages "re-try" after failure but there are enevitably times when the packages are going to have to be run during the day because something needs 'fixing'.
Are the lock's what caused it all to load so slowly during the day?? and is there any way to fix it??
Two nights ago they failed due to a connectivity issue the package couldn't find the source server.
As a result we had to run the packages the following day during business hours. Now to some extent we expect the packages to run slowly during hours where the servers are being hit by users accessing them, however the slowdown was emence.
Normally the packages combined take 1-2 hours over night, but yesterday having re-started them at 8am they where still only 1/2 though at 6pm. Packages with a normal run time of 2-3minutes had taken in some cases 2 hours.
In the end it was so late i cancelled it and allowed it to run it's self as normal that evening and all appear's to be well this morning.
Im not a DBA so in no means an expert but as far as i can tell the servers wheren't being overloaded, at they seemed to be running at about 30% in terms of Memory and CPU which is fairly typical for those servers at that time of day.
Infact the only thing i could see that seemed out of the ordinary was the number of lock was incredibly high, spiking into the billion's on several occasions.
Again im not a DBA but i assume this is because the users /reports etc are taking lock's out on rows/tables so the packages had to wait for them to free up?? And it doens't happen on over night loads because the users are tucked up in bed (hopefully
) We have never had this problem before but this is perhaps because we now have more systems/users than we used to, but as our systems are still growing im guessing the problem will get worse?
Essentially this whole mess caused an entire days down-time for our report server which is unacceptable. We are looking at having packages "re-try" after failure but there are enevitably times when the packages are going to have to be run during the day because something needs 'fixing'.
Are the lock's what caused it all to load so slowly during the day?? and is there any way to fix it??