sproc runs forever - SQL Server Forums

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)

sproc runs forever

Author

Topic

peterlemonjello
Yak Posting Veteran

53 Posts

Posted - 2007-01-11 : 12:06:39

I'm having problems with a proc and sql2k5 sp1. 95% of the time the proc runs in a few milliseconds. But occasionally the proc will run forever. sp_who2 shows no io activity but the cpu time continually grows. here's the proc code. Anyone see anything of significance or have an idea of what's occuring?

CREATE PROCEDURE clairvoyant.p_rptDscLostTimeIntervalDetail
@SupervisorID int,
@RptDate datetime,
@KviID int,
@AllEmployeeFlag int,
@TimeZoneOffset int
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

IF @SupervisorID IS NULL
RAISERROR ('@SupervisorID is Required!', 16, 1 )
IF @RptDate IS NULL
RAISERROR ('@RptDate is Required!', 16, 1 )
IF @KviID IS NULL
RAISERROR ('@KviID is Required!', 16, 1 )
IF @AllEmployeeFlag IS NULL
RAISERROR ('@AllEmployeeFlag is Required!', 16, 1 )
IF @RptDate < DATEADD(mm, -6, GETDATE())
RAISERROR ('@RptDate must be within 6 months!', 16, 1 )

BEGIN

SELECT au.AppUserID, au.FullName, ltid.LostTimeIntervalDetailID, CAST(ltid.IntervalMinutes AS int) IntervalMinutes,
ltid.SupervisorDescription, lte.LostTimeExcptnName, ltid.IntervalNote,
DATEADD( mi, @TimeZoneOffset, ltid.IntervalModifiedDateTime) AS IntervalModifiedDateTime, lti.IntervalDate, ts.StartTime,
CASE lt.LostTimeName WHEN 'Man' THEN 1 ELSE 0 END AS 'Man',
CASE lt.LostTimeName WHEN 'Method' THEN 1 ELSE 0 END AS 'Method',
CASE lt.LostTimeName WHEN 'Measurement' THEN 1 ELSE 0 END AS 'Measurement',
CASE lt.LostTimeName WHEN 'Material - System Error' THEN 1 ELSE 0 END AS 'Material - System Error',
CASE lt.LostTimeName WHEN 'Machine - System Down Time' THEN 1 ELSE 0 END AS 'Machine - System Down Time',
CASE ltid.ControllableFlag WHEN 1 THEN 1 ELSE 0 END AS 'Controllable',
CASE ltid.ControllableFlag WHEN 0 THEN 1 ELSE 0 END AS 'Uncontrollable'
FROM clairvoyant.LostTimeIntervalDetail ltid
INNER JOIN clairvoyant.LostTimeInterval lti
ON lti.LostTimeIntervalID = ltid.LostTimeIntervalID
INNER JOIN clairvoyant.TimeSlot ts
ON ts.TimeSlotID = lti.TimeSlotID
INNER JOIN clairvoyant.LostTimeExcptn lte
ON lte.LostTimeExcptnID = ltid.LostTimeExcptnID
INNER JOIN clairvoyant.LostTime lt
ON lt.LostTimeID = lte.LostTimeID
INNER JOIN clairvoyant.AppUser au
ON au.AppUserID = lti.AppUserID
WHERE lti.IntervalDate = @RptDate
AND lt.KviID = @KviID
AND lti.SupervisorID =
CASE
WHEN @AllEmployeeFlag = 1 THEN lti.SupervisorID
ELSE @SupervisorID
END
ORDER BY au.FullName

END

END

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-11 : 12:54:16

Is it a problem when the supervisot flag = 1?

I think I would write 2 queries and lose the flag in the predicate

What's the SHOW PLAN look like, and did you run a profile trace whne it is running long?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

peterlemonjello
Yak Posting Veteran

53 Posts

Posted - 2007-01-11 : 13:52:59

I forgot to mention, when the proc hangs I can call the exact same proc passing the exact same parms and it returns immediately. Even while the other proc is still hung.

Is it a problem when the supervisot flag = 1?
Can't find any evidence that suggest that. The proc runs very fast whether it's 1 or 0.

What's the SHOW PLAN look like, and did you run a profile trace whne it is running long?
SHOW PLAN looks good. Mostly index seeks and joins. I haven't been able to get anything from a trace since I don't know when it's going to occur.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources