SQLAgent - TSQL jobs hangs

Question

SQLAgent - TSQL jobs hangs

Jerid

Grasshopper

Points: 23
More actions
August 15, 2007 at 7:45 am

#197346

SQL Server 2005
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)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Eisbrener Mr or Mrs. 500 Points: 509 More actions · Answer 1

I would suggest you start off by looking at the execution plan of the select statement to ensure that it's not doing a table scan or some other horribly inefficient execution path.

John

Jerid Grasshopper Points: 23 More actions · Answer 2

I ran the Display Est. Execution plan, not sure how I can post it as it's a graphical image.

It is doing a Table scan on t_DTM_DATA_STAGING. I don't have any indexes setup on this table, if I create a new field and set it as the index will that make a difference?

Jerid Grasshopper Points: 23 More actions · Answer 3

I figured out how to get the plan in text format.

|--Table Insert(OBJECT[DTM].[dbo].[t_DTM_DATA_STAGING2]), SET[DTM].[dbo].[t_DTM_DATA_STAGING2].[CP] = [DTM].[dbo].[t_DTM_DATA_STAGING].[CP],[DTM].[dbo].[t_DTM_DATA_STAGING2].[CO] = [DTM].[dbo].[t_DTM_DATA_STAGING].[CO],[DTM].[dbo].[t_DTM_DATA_STAGING

|--Top(ROWCOUNT est 0)

|--Filter(WHERE[DTM].[dbo].[t_DTM_DATA].[CP] as [t_DTM_DATA_1].[CP] IS NULL))

|--Nested Loops(Left Outer Join, OUTER REFERENCES[DTM].[dbo].[t_DTM_DATA_STAGING].[CP], [DTM].[dbo].[t_DTM_DATA_STAGING].[CO], [DTM].[dbo].[t_DTM_DATA_STAGING].[MAJ], [DTM].[dbo].[t_DTM_DATA_STAGING].[MINR], [DTM].[dbo].[t_DTM_DATA_STAGI

|--Table Scan(OBJECT[DTM].[dbo].[t_DTM_DATA_STAGING]))

|--Clustered Index Seek(OBJECT[DTM].[dbo].[t_DTM_DATA].[Unique] AS [t_DTM_DATA_1]), SEEK[t_DTM_DATA_1].[CP]=[DTM].[dbo].[t_DTM_DATA_STAGING].[CP] AND [t_DTM_DATA_1].[CO]=[DTM].[dbo].[t_DTM_DATA_STAGING].[CO] AND [t_DTM_DATA_1].[MA

John Eisbrener Mr or Mrs. 500 Points: 509 More actions · Answer 4

You don't necessarily have to make a new field to make an index. Put the select statement into a .sql file then run it through Database Engine Tuning Advisor. It will give you better tips than I can on what indexes to create. Hopefully that will speed up your execution times.

John

Jerid Grasshopper Points: 23 More actions · Answer 5

Thanks for your help, that's what I did. I'm going to make the recommended changes and see what happens.

Jerid Grasshopper Points: 23 More actions · Answer 6

I added a clustered index to the tables and it seems to have fixed the issue.

Thanks for your help.