Stored Proceedure runs away with CPU untill its resaved
-
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
-
Compare execution plans between correct and not correct may help.
-
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 -
Thanks guys......your insight is greatly appreciated!!
-
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply