Stored Proceedure runs away with CPU untill its resaved

Question

Post reply

Stored Proceedure runs away with CPU untill its resaved

uknow

Valued Member

Points: 55
More actions
April 1, 2004 at 11:30 am

#163321

I have an interesting issue with a store procedure [pasted at the bottom of this entry]
On a regular basis this stored procedure will run but never end and tax the server more and more as this sproc is fired off.
We have been able to get the sproc to run correctly for a period of time simply by droping the sproc and recreating it with exactly the same code.
When it does run correctly, it runs for approx. 12 seconds. When it doesnt run correctly it never ends. No blocking, very little disk activity, very high cpu activity, even when its only running once.

---------------------------------------------------------
CREATE PROCEDURE sp_rptGetManagerEnrollments
(
@manager int, -- Manager Id
@Status varchar(3) -- Enrollment Status
)
as
SET NOCOUNT ON
IF ((@Status = 'All') or (@Status = '')) SET @status = '%'
SELECT DU.firstName as 'First Name',
DU.lastName as 'Last Name',
DC.city as 'City',
DLA.title as 'Course Title',
DLA.code as 'Course Code',
DF.description as 'Status',
DLAI.firstMeetingStart as 'Start',
DLT.dateTimeEnrolled as 'Enrolled',
DLT.dateTimeCompleted as 'Completed',
CASE
WHEN DLT.score < 100 THEN DLT.score
WHEN DLT.score > 100 THEN (DLT.score / 10)
ELSE DLT.score
END as 'Score',
DLT.passed as 'Passed'

FROM DRUser DU (NOLOCK)
INNER JOIN DRManagerMap DMM (NOLOCK) on DMM.ChildID = DU.userid
INNER JOIN DRCompany DC (NOLOCK) ON DU.company = DC.id
INNER JOIN DRLearningActivityTranscript DLT (NOLOCK) ON DU.userID = DLT.userID
INNER JOIN DRLearningActivity DLA (NOLOCK) ON DLT.learningActivityID = DLA.ID
LEFT OUTER JOIN DRFieldDecode DF (NOLOCK) ON DLT.enrollmentStatus = DF.abbr
LEFT OUTER JOIN DRLearningActivityInstance DLAI (NOLOCK) ON DLAI.id = DLT.learningActivityInstanceID

WHERE (DMM.ParentId = @manager)
AND ISNULL(DLT.enrollmentStatus,'') LIKE @status
AND (DF.field = 'DRLearningActivityTranscript.enrollmentStatus')
ORDER BY DU.lastname, DU.firstname
SET NOCOUNT OFF

GO

Viewing 5 posts - 1 through 4 (of 4 total)

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

wz700 SSCertifiable Points: 7133 More actions · Answer 1

Compare execution plans between correct and not correct may help.

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 2

Is this being run against static data ? or is this being run against data that is constantly changing?

Also,

You are using LIKE against the @status variable but the variable won't always be a wildcard. This may/may not help with the runaway.

You are using an alias for the DRLearningActivityTranscript table of DLT yet down below in your WHERE statement you are looking for the EXPLICIT value of 'DRLearningActivityTranscript.enrollmentStatus' instead of:

DRLearningActivityTranscript.enrollmentStatus OR DLT.enrollmentStatus

which may also be contributing to the runaway.

What does the estimated execution look like <CTRL> L from QA?? Are there any table scans/loops, etc... that may be cranking the query?

I would start by breaking the query down into smaller queries of 2 table joins and test them all to see if the runaway happens there and slowly build back-up.

This is one of the joys of SQL The "why is this happening"???

Also, is the database on local drives or a SAN/eternal drive array? You might be experiencing network troubles as well.

Good Luck,

AJ

Good Hunting!

AJ Ahrens

webmaster@kritter.net

uknow Valued Member Points: 55 More actions · Answer 3

Thanks guys......your insight is greatly appreciated!!

uknow Valued Member Points: 55 More actions · Answer 4

Just a a final note about this sproc.

I decided to take the logic of this sproc to populate a new table with the all results (all rows).

The sproc used to take well over 90 seconds to run each time. Now this code runs once in the middle of the night for about 5 minutes to populate the new table with all rows....Now to get the required results for a query against the table, it only takes about 2 seconds.

S