SQLAgent - TSQL jobs hangs (SQL Server 2005)

Last Post 23 Aug 2007 07:53 PM by SQLUSA. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
jerid
New Member
New Member

--
15 Aug 2007 04:14 AM
I have two tables
t_DTM_DATA_STAGING around 2 million records
t_DTM_DATA around 251 million records

The below SQL statement looks for records in the t_DTM_DATA_STAGING table that are not in the t_DTM_DATA table and adds them to a 3rd table. (t_DTM_DATA_STAGING2)

This statement has been running fine for weeks, but now it seems to get hung every day. I ran sp_Who2 and it says the status is runnable. I let it run for around 5 or 6 hours the other day to see if it will finish but it didn't. This SQL job is step 3 in a 6 step SQLAgent job that usually finishes in 30 to 45 minutes.

I'm not sure how to troubleshoot this problem. No other jobs are running at the time this job runs.

Could this SQL statement be written a better way?

Thanks for any help anyone can provide.

Jerid

SET QUOTED_IDENTIFIER ON

INSERT INTO
[DTM].[dbo].[t_DTM_DATA_STAGING2]
([CP],[CO],[MAJ],[MINR],[LOCN],[DPT],[YEAR],[PD],[WK],[TRDT],[SYSTEM],[AMOUNT],[DESCRIPTION],[GROUP],[VENDOR]
,[INVOICE],[IDAT],[PO_NUMBER],[DDAT],[RCV#],[RDAT],[RSP],[EXPLANATION],[UPLOAD_DATE],[UPLOAD_USER],[UPLOAD_NAME]
,[RELEASE_DATE],[RELEASE_USER],[RELEASE_NAME],[TRTM])
SELECT
t_DTM_DATA_STAGING.CP, t_DTM_DATA_STAGING.CO, t_DTM_DATA_STAGING.MAJ, t_DTM_DATA_STAGING.MINR, t_DTM_DATA_STAGING.LOCN, t_DTM_DATA_STAGING.DPT,
t_DTM_DATA_STAGING.YEAR, t_DTM_DATA_STAGING.PD, t_DTM_DATA_STAGING.WK, t_DTM_DATA_STAGING.TRDT, t_DTM_DATA_STAGING.SYSTEM, t_DTM_DATA_STAGING.AMOUNT,
t_DTM_DATA_STAGING.DESCRIPTION, t_DTM_DATA_STAGING.[GROUP], t_DTM_DATA_STAGING.VENDOR, t_DTM_DATA_STAGING.INVOICE, t_DTM_DATA_STAGING.IDAT,
t_DTM_DATA_STAGING.PO_NUMBER, t_DTM_DATA_STAGING.DDAT, t_DTM_DATA_STAGING.RCV#, t_DTM_DATA_STAGING.RDAT, t_DTM_DATA_STAGING.RSP,
t_DTM_DATA_STAGING.EXPLANATION, t_DTM_DATA_STAGING.UPLOAD_DATE, t_DTM_DATA_STAGING.UPLOAD_USER, t_DTM_DATA_STAGING.UPLOAD_NAME,
t_DTM_DATA_STAGING.RELEASE_DATE, t_DTM_DATA_STAGING.RELEASE_USER, t_DTM_DATA_STAGING.RELEASE_NAME, t_DTM_DATA_STAGING.TRTM
FROM
t_DTM_DATA_STAGING
LEFT OUTER JOIN
t_DTM_DATA AS t_DTM_DATA_1
ON
t_DTM_DATA_STAGING.TRTM = t_DTM_DATA_1.TRTM
AND
t_DTM_DATA_STAGING.TRDT = t_DTM_DATA_1.TRDT
AND
t_DTM_DATA_STAGING.PD = t_DTM_DATA_1.PD
AND
t_DTM_DATA_STAGING.YEAR = t_DTM_DATA_1.YEAR
AND
t_DTM_DATA_STAGING.DPT = t_DTM_DATA_1.DPT
AND
t_DTM_DATA_STAGING.LOCN = t_DTM_DATA_1.LOCN
AND
t_DTM_DATA_STAGING.MINR = t_DTM_DATA_1.MINR
AND
t_DTM_DATA_STAGING.MAJ = t_DTM_DATA_1.MAJ
AND
t_DTM_DATA_STAGING.CO = t_DTM_DATA_1.CO
AND
t_DTM_DATA_STAGING.CP = t_DTM_DATA_1.CP
WHERE
(t_DTM_DATA_1.CP IS NULL)
jerid
New Member
New Member

--
15 Aug 2007 06:22 AM
I assume that would be the BlkBy column? If so, then no, nothing is being blocked.
whynot
New Member
New Member

--
16 Aug 2007 09:06 AM
Try change "WHERE" to "AND" at the end of the code.
whynot
New Member
New Member

--
20 Aug 2007 03:56 AM
Yes, Peter is right. I did miss the condition "t_DTM_DATA_STAGING.CP = t_DTM_DATA_1.CP ".
jerid
New Member
New Member

--
22 Aug 2007 06:15 AM
I adding a clustered index to the Staging table and it seems to have fixed the issue. It stopped the table scan.

Thanks for your help.
SQLUSA
New Member
New Member

--
23 Aug 2007 07:53 PM
The case of the missing index solved again...

Easy way of checking is SET STATISTICS IO ON

If you see excessive amount of reads on any table, indexing maybe the issue.
You are not authorized to post a reply.

Acceptable Use Policy