SSIS Package runs between 3 minutes and 3 hours

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

Who is Participating?
MikeWalshConnect With a Mentor Commented:
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.
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.

JAYDUBAuthor Commented:
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    
JAYDUBAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.