SSIS Package runs between 3 minutes and 3 hours

Posted on 2009-12-24
Last Modified: 2013-11-30
SSIS use to run on 3 out of 4 databases within 3 minutes.  Only one of my databases took well over an hour to complete.  NOW, the 3 good SSIS programs are taking between 3 minutes and 3 hours to complete.  I am not a SQL Server guru, but something appears to have changed and is creating the same problem on all databases now.  I do NOT believe that I changed anything on the SSIS or the database, but I work with a colleague, so one of us could have jacked things up.  The databases are less than 200,000 rows and the SSIS seem to STALL on the "arupdate" process.  Below is the code....any help would be appreciated. Thanks.
use [7709528881]


'this insert names seems to fly (see below for update names)

Insert into Names ([Name], ARDue, ARContact, ARCreditlimit)

Select namesar.[name], namesar.ARDue, namesar.ARContact, convert(money,namesar.ARCreditlimit)  from namesar left outer join names on namesar.[name] = names.[name]

where  names.[name] IS NULL

'this update names is what seems to hang

Update names

set [name] = namesar.[name], Ardue = convert(money,namesar.ardue), arcontact = namesar.arcontact, arcreditlimit = convert(money,namesar.arcreditlimit)

from names inner join namesar on names.[name] = namesar.[name]	

where (namesar.[name] is not null)

Open in new window

Question by:JAYDUB
    LVL 13

    Expert Comment

    A few thoughts pop into my mind while reading your question -

    1.)  Can you see what kind of waits your are experiencing? A simple query to run would be SELECT * FROM sys.dm_os_waiting_tasks where session_id > 50. Look for the session_id that corresponds to the SSIS package (don't tell anyone but I still sometimes use sp_who2 to quickly see what session_id belongs to who). Look at the wait duration and wait type/last wait type columns. What are you seeing? If you are being blocked you should see something in the blocking_session information.

    2.) When something suddenly stops running as well as it used to I tend to look at statistics. Sometimes this matters, sometimes it doesn't but take a look at updating your statistics (Search sp_updatestats or statistics in books online).

    3.) Run that query in SQL Server Management studio and look at the execution plan. Look for problem areas, are your estimated and actual rows drastically different? That points back to statistics updating if so.

    4.) Do you have an index on the name columns in the two tables? Looking at that query that really seems to be the primary index to help here. Covering indexes could potentially help with the other columns in the update portion of the statement being listed as included columns but I don't know if the additional overhead of maintaining the wider index is worthwhile. Experiment and see. So definitely an index on each tables name columns. MAYBE the one on the namesar including the arcontact,debtlimit,etc. columns.

    5.) I am a bit confused by that query.You are joining on the names column and one of your updates is updating the name column. It seems to me like if the join happened on the name, the update of the name to the second tables name is superfluous. Removing that won't save you lots of time but not doing updates that don't need to be done does help some and it seems more logical.


    Author Comment

    waiting_task_address session_id exec_context_id wait_duration_ms wait_type resource_address blocking_task_address blocking_session_id blocking_exec_context_id resource_description
    0x04CE5390 56 0 38 PAGEIOLATCH_SH 0x03D7BCAC NULL NULL NULL 2:1:153159
    0x04CE58E8 54 0 0 PAGEIOLATCH_SH 0x03DA3ED0 NULL NULL NULL 2:1:357822
    0x04CE4AA8 57 0 0 PAGEIOLATCH_SH 0x03DA4850 NULL NULL NULL 2:1:770866
    Above is result of the wait select.  I was running 3 SIS and they were stuck on NAMES SIS again....see below for sp_who2 results.  does this shed any more light on the huge delay? i am in the process of looking into your 2-5 suggestions and will get back to you.  I do know that nothing is indexed right now, and we will be indexing name id (but nothing was indexed before and it only took 3 minutes so I am not hopeful).  I did notice that the "wait time on server replies" was equal to the "total execution time" and both numbers were very high... again, don't know what this means but I'm looking into it.  thanks for your help so far.  any other thoughts based on this info?
    SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID REQUESTID
    56    SUSPENDED                      EVAG\sqlagent EVAG-DEV-SQL   . 7709528881 UPDATE           11937 284090 12/25 07:35:47 SSIS-7704291100-names-{25136849-6D03-4865-9186-68E93D773FAC}Connection 2 56    0    
    LVL 13

    Accepted Solution

    Alright. So the good news is that there is no blocking in that one snapshot of you running the query on waiting_tasks. I would run that repeatedly during the wait and see if you ever see a non 0 value for blocking_session_id to see if there is blocking (there are more elegant ways but this is a quick way)

    The waiting is on IO Latches which -could- point towards some issues in the I/O subystem. Splitting data and log files onto their own physical spindles helps there. Looking at your RAID options helps also (RAID 10 or even just RAID 1 for logs.. RAID 5 may be fine for data, etc.).

    In this case just looking at the above waits for that one quick fraction in time and the fact that it wasn't always this bad, I am thinking that updating statistics, and at least adding an index on those name columns and thinking about a clustered index for those tables will be the answer most likely.

    Author Closing Comment

    thanks for you help....i'm going to index nameid and look into i/o configuration.  will get back to you if need be, you've been a great help.  thanks.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now