SQLAgent - TSQL jobs hangs (SQL Server 2005)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQLAgent - TSQL jobs hangs (SQL Server 2005)

Author

Topic

Jerid
Starting Member

10 Posts

Posted - 2007-08-15 : 11:18:49

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)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-08-15 : 11:35:07

Can you posted the Expected execution plan?
I'd suspect an index has been dropped or a data colum have changed in size and a data conversion is forcing a table scan instead of an index seek....

Jerid
Starting Member

10 Posts

Posted - 2007-08-15 : 13:36:21

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 wich has around 2 million records. I don't have any indexes setup on this table, if I create a new field and set it as a clustered index will that make a difference?

Jerid
Starting Member

10 Posts

Posted - 2007-08-15 : 14:21:54

I figured out how to show the Plan Text.

|--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

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-15 : 22:13:50

Did you rebuild index or update statistics recently?

Jerid
Starting Member

10 Posts

Posted - 2007-08-16 : 08:26:31

I ran the query through the Database Tuning Advisor and it recomended creating a clustered index. I did, and the problems seems to be fixed.

Thanks for your help.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-08-16 : 13:25:37

Interesting to see the execution plan AFTER the extra index....so that the benefit can be observed.

Jerid
Starting Member

10 Posts

Posted - 2007-08-28 : 13:11:23

|--Clustered Index Insert(OBJECT:([DTM].[dbo].[t_DTM_DATA_STAGING2].[Staging_Import2]), 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_STAGING2].[MAJ] = [DTM].[dbo].[t_DTM_DATA_STAGING].[MAJ],[DTM].[dbo].[t_DTM_DATA_STAGING2].[MINR] = [DTM].[dbo].[t_DTM_DATA_STAGING].[MINR],[DTM].[dbo].[t_DTM_DATA_STAGING2].[LOCN] = [DTM].[dbo].[t_DTM_DATA_STAGING].[LOCN],[DTM].[dbo].[t_DTM_DATA_STAGING2].[DPT] = [DTM].[dbo].[t_DTM_DATA_STAGING].[DPT],[DTM].[dbo].[t_DTM_DATA_STAGING2].[YEAR] = [DTM].[dbo].[t_DTM_DATA_STAGING].[YEAR],[DTM].[dbo].[t_DTM_DATA_STAGING2].[PD] = [DTM].[dbo].[t_DTM_DATA_STAGING].[PD],[DTM].[dbo].[t_DTM_DATA_STAGING2].[WK] = [DTM].[dbo].[t_DTM_DATA_STAGING].[WK],[DTM].[dbo].[t_DTM_DATA_STAGING2].[TRDT] = [DTM].[dbo].[t_DTM_DATA_STAGING].[TRDT],[DTM].[dbo].[t_DTM_DATA_STAGING2].[SYSTEM] = [DTM].[dbo].[t_DTM_DATA_STAGING].[SYSTEM],[DTM].[dbo].[t_DTM_DATA_STAGING2].[AMOUNT] = [DTM].[dbo].[t_DTM_DATA_STAGING].[AMOUNT],[DTM].[dbo].[t_DTM_DATA_STAGING2].[DESCRIPTION] = [DTM].[dbo].[t_DTM_DATA_STAGING].[DESCRIPTION],[DTM].[dbo].[t_DTM_DATA_STAGING2].[GROUP] = [DTM].[dbo].[t_DTM_DATA_STAGING].[GROUP],[DTM].[dbo].[t_DTM_DATA_STAGING2].[VENDOR] = [DTM].[dbo].[t_DTM_DATA_STAGING].[VENDOR],[DTM].[dbo].[t_DTM_DATA_STAGING2].[INVOICE] = [DTM].[dbo].[t_DTM_DATA_STAGING].[INVOICE],[DTM].[dbo].[t_DTM_DATA_STAGING2].[IDAT] = [DTM].[dbo].[t_DTM_DATA_STAGING].[IDAT],[DTM].[dbo].[t_DTM_DATA_STAGING2].[PO_NUMBER] = [DTM].[dbo].[t_DTM_DATA_STAGING].[PO_NUMBER],[DTM].[dbo].[t_DTM_DATA_STAGING2].[DDAT] = [DTM].[dbo].[t_DTM_DATA_STAGING].[DDAT],[DTM].[dbo].[t_DTM_DATA_STAGING2].[RCV#] = [DTM].[dbo].[t_DTM_DATA_STAGING].[RCV#],[DTM].[dbo].[t_DTM_DATA_STAGING2].[RDAT] = [DTM].[dbo].[t_DTM_DATA_STAGING].[RDAT],[DTM].[dbo].[t_DTM_DATA_STAGING2].[RSP] = [DTM].[dbo].[t_DTM_DATA_STAGING].[RSP],[DTM].[dbo].[t_DTM_DATA_STAGING2].[EXPLANATION] = [DTM].[dbo].[t_DTM_DATA_STAGING].[EXPLANATION],[DTM].[dbo].[t_DTM_DATA_STAGING2].[UPLOAD_DATE] = [DTM].[dbo].[t_DTM_DATA_STAGING].[UPLOAD_DATE],[DTM].[dbo].[t_DTM_DATA_STAGING2].[UPLOAD_USER] = [DTM].[dbo].[t_DTM_DATA_STAGING].[UPLOAD_USER],[DTM].[dbo].[t_DTM_DATA_STAGING2].[UPLOAD_NAME] = [DTM].[dbo].[t_DTM_DATA_STAGING].[UPLOAD_NAME],[DTM].[dbo].[t_DTM_DATA_STAGING2].[RELEASE_DATE] = [DTM].[dbo].[t_DTM_DATA_STAGING].[RELEASE_DATE],[DTM].[dbo].[t_DTM_DATA_STAGING2].[RELEASE_USER] = [DTM].[dbo].[t_DTM_DATA_STAGING].[RELEASE_USER],[DTM].[dbo].[t_DTM_DATA_STAGING2].[RELEASE_NAME] = [DTM].[dbo].[t_DTM_DATA_STAGING].[RELEASE_NAME],[DTM].[dbo].[t_DTM_DATA_STAGING2].[TRTM] = [DTM].[dbo].[t_DTM_DATA_STAGING].[TRTM]))
|--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_STAGING].[LOCN], [DTM].[dbo].[t_DTM_DATA_STAGING].[DPT], [DTM].[dbo].[t_DTM_DATA_STAGING].[YEAR], [DTM].[dbo].[t_DTM_DATA_STAGING].[PD], [DTM].[dbo].[t_DTM_DATA_STAGING].[TRDT], [DTM].[dbo].[t_DTM_DATA_STAGING].[TRTM]) OPTIMIZED)
|--Clustered Index Scan(OBJECT:([DTM].[dbo].[t_DTM_DATA_STAGING].[Staging_Import]))
|--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].[MAJ]=[DTM].[dbo].[t_DTM_DATA_STAGING].[MAJ] AND [t_DTM_DATA_1].[MINR]=[DTM].[dbo].[t_DTM_DATA_STAGING].[MINR] AND [t_DTM_DATA_1].[LOCN]=[DTM].[dbo].[t_DTM_DATA_STAGING].[LOCN] AND [t_DTM_DATA_1].[DPT]=[DTM].[dbo].[t_DTM_DATA_STAGING].[DPT] AND [t_DTM_DATA_1].[YEAR]=[DTM].[dbo].[t_DTM_DATA_STAGING].[YEAR] AND [t_DTM_DATA_1].[PD]=[DTM].[dbo].[t_DTM_DATA_STAGING].[PD]), WHERE:([DTM].[dbo].[t_DTM_DATA_STAGING].[TRTM]=[DTM].[dbo].[t_DTM_DATA].[TRTM] as [t_DTM_DATA_1].[TRTM] AND [DTM].[dbo].[t_DTM_DATA_STAGING].[TRDT]=[DTM].[dbo].[t_DTM_DATA].[TRDT] as [t_DTM_DATA_1].[TRDT]) ORDERED FORWARD)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 16:08:30

Also asked here
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=91&threadid=86900&enterthread=y

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources