June 7, 2012 at 8:45 am
I have a SSIS package that calls 5 other packages in sequence. Each of these packages transfers data to an Ingres server and then processes it using a number of batch command files from within the packages.
The top level package is run via a scheduled task (because of Ingres password problems I can't run it via SQL Agent) calling a batch command.
The problem is that the job used to run in minutes but about a week ago it suddenly started taking over 4 hours. This morning the job failed and I had to run the job manually from with Visual Studio. It ran in minutes.
Unfortunately the job has to normally run at 02:00 in the morning so it has to be run via a schedule task.
Any ideas about why it now takes so long?:rolleyes:
June 7, 2012 at 8:52 am
Have you check the database and server logs for any unusual changes between the most-recent fast-run and the first slow-run?
I'm not familiar with administering Ingress, but most data systems log errors, significant events, and so on, one way or another.
Is the data source SQL Server? If so, you should also check event/error logs there, to see if that's where the problem is coming from.
Otherwise, Windows logs on the server(s) in question is where I'd start.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2012 at 8:55 am
Also, is this a one-time thing, where it was slow/failed one night, or did it start being consistently slow a few nights ago and is staying that way?
If it's running in the expected time from BIDS, and not from the scheduled job, then it's probably something in the server environment, not something in the data layers. Maybe a server involved in the process had a change that night, or maybe there's a new process running on there that's competing for resources with your job.
They key is, in every system involved, check for what changed before the process slowed down.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2012 at 9:18 am
The data source is SQL Server. All server and event logs have been checked on both systems - no hint of any problem anywhere. Ingres gives little or no information in it's logs even when it logs errors but in this case there are no errors or messages. The network speed has been checked and is not a problem.
The extended run time is happening all the time now. The intention is to run it in the limited number of ways open to us.
June 7, 2012 at 9:54 am
Have you fully verified no changes were made to either server environment? (I'm assuming it's two different servers: one for SQL, one for Ingress. Correct me if that's wrong.) A Windows patch applied, or an SQL Server patch maybe? Maybe an update to some piece of security software (antivirus, that kind of thing)? Or either a driver update applied, or one that should be applied that's now out-of-date?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 8, 2012 at 12:34 am
How does the package design look like?
Any blocking transformations?
If for some reason there isn't enough memory and SSIS has to spool data to disk, performance drastically degrades.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 8, 2012 at 3:21 am
There have been no changes to either system of any form. There have been no changes to either database on SQL Server or Ingres, nor has there been any change in the processing of the data.
The package design is
Package
calls subpackageA, calls subpackageB, calls subpackageC, calls subpackageD, calls subpackageE
Each subpackage looks like
Create transferdata table in SQL Server, populate the table with the required data, transfer the data to Ingres using Import / Export, run a number of batch command SQL files against Ingres to process the data, drop the transfer table
June 8, 2012 at 6:42 am
I will try to identify the package that is now taken most of the time narrowing the scope of the troubleshooting, then you can focus on the tasks that package is performing.
Hope this helps,
Rock from VbCity
July 5, 2012 at 8:18 am
I know it has been a while since I responded but I thought that you may like to know that I eventually found a cure for the slow running but not the cause.
I had found a few cases on the net that suggested it might have something to do with the priority level the job was running at.
On Windows 2008 R2 Standard the default priority of scheduled tasks is 7. So for this specific task I changed the priority to 6. While not bringing the runtime down to the original time of 2 minutes it brought it down to 10 minutes which was acceptable.
July 6, 2012 at 5:14 am
Sounds like you may have a clash with other scheduled tasks - backing up that database for example?
July 6, 2012 at 5:52 am
No clash. No other scheduled task running at the same time.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply