Home Forums SQL Server 2008 SQL Server 2008 Performance Tuning Stored Proc hanging in SSIS, runs 20 secs in SSMS (2008 R2 SP1) RE: Stored Proc hanging in SSIS, runs 20 secs in SSMS (2008 R2 SP1)

  • March 9, 2015 at 6:41 pm

    #1782752

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)