February 13, 2015 at 3:34 am
Hi,
We have a problem which we have hit a brick wall with, hopefully someone can offer some support or point us in the right direction...
We have an SSIS parent package that executes various stored procedures and child packages which runs as part of a daily job at 2am.
For the past 3 days the job has still been running at 7am when it should only take 7 minutes to run.
It seems to be hanging at a particular stored procedure.
The confusing part is the usp runs in about 20 seconds EVERY time without fail in SSMS but is getting stuck when running in SSIS (BIDS or via Agent)
The even more confusing part is at about 8am it ran fine in BIDS and via the job - making it almost impossible to debug.
There are no other jobs running at that time, therefore no clashes.
The stored proc does the following:
>Declares variables
>Sets variables locally from parameter tables
>Created temp table
>Inserts around 30k records from source (source same server)
>Adds clustered index on temp table
>Updated dimension tables
>Loads Staging Table
All the work is done i the proc, all ssis does is execute SQL task, exec uspxxxxxxxxxxx
We've read a few things around SSIS execuation plans, but not sure if its relevant or not.
Anybody got any suggestions?
Thanks
Steve
February 25, 2015 at 5:02 am
I would set up a query of the session and request DMVs to run at the same time as your troubled agent job. Capture data every 5 to 15 seconds and spill it to a table or a file. Then examine that data the next morning after an issue. This will help you see if something is happening in the environment that is causing the issue. You could use something like sp_whoisactive if you don't want to write your own query
March 9, 2015 at 4:04 pm
March 9, 2015 at 6:41 pm
steven.oates (2/13/2015)
Hi,We have a problem which we have hit a brick wall with, hopefully someone can offer some support or point us in the right direction...
We have an SSIS parent package that executes various stored procedures and child packages which runs as part of a daily job at 2am.
For the past 3 days the job has still been running at 7am when it should only take 7 minutes to run.
It seems to be hanging at a particular stored procedure.
The confusing part is the usp runs in about 20 seconds EVERY time without fail in SSMS but is getting stuck when running in SSIS (BIDS or via Agent)
The even more confusing part is at about 8am it ran fine in BIDS and via the job - making it almost impossible to debug.
There are no other jobs running at that time, therefore no clashes.
The stored proc does the following:
>Declares variables
>Sets variables locally from parameter tables
>Created temp table
>Inserts around 30k records from source (source same server)
>Adds clustered index on temp table
>Updated dimension tables
>Loads Staging Table
All the work is done i the proc, all ssis does is execute SQL task, exec uspxxxxxxxxxxx
We've read a few things around SSIS execuation plans, but not sure if its relevant or not.
Anybody got any suggestions?
Thanks
Steve
Along with making sure that arithabort is enable as the MadAdmin suggested, you might want to force a recompile after the clustered index has been added to the temp table. I'm thinking that when you run it the next morning, it already has a good plan in cache but not when it runs at 2AM.
You also say that no other jobs are running when this one does. So you're saying that there's absolutely no jobs running between 2AM and 6AM??? Do you have a tape backup system such as BERemote running at that time or maybe a virus update or scan running, none of which will look like a job according to SQL Server?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply