Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Could my stored procedure be the cause of performance problems?

Avatar of Meps
MepsFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
11 Comments1 Solution1667 ViewsLast Modified:
I have 3 major stored procedures, meaning they are the backbone of my application. I built them to be as dynamic as possible, but every 1st of the month the database crashes and our database people blame these 3 stored procedures.  The only reason I think they blame them is because they are ran so often they are running at the time the datbase crashes.  These stored procedures run about 10,000 times a day.

Here is an example of 1 of the stored procedures.  

The stored procedures work flawlessly the whole month running 10,000 times a day, but each 1st of the month the database crashes.

The fix for the database crash is to run:          sp_updateStats

Is my stored procedure to blame, if so then why doesn't it crash on other days instead of the 1st of the month.  Like clockwork the 1st of the month the database crashes.

Or is something else going on?  
DECLARE @TEIR int
SET @TEIR = 0

--temp table
CREATE TABLE #TempA(
..........
 )

Select @TEIR = ROLEID FROM UserInfo WHERE UserID = @WHO_WHERE;

Insert into #TempA(...............)
SELECT   ................
FROM(
	Select ..........
	From Work tt Left Join
		Error tf on tf.UserID=tt.UserID and tf.SubmitDate=tt.TimeDate
	Union All
	select .............
	from Error tf Left Join
		Work tt on tf.UserID=tt.UserID and tf.SubmitDate=tt.TimeDate
	where tt.UserID is null)  BF
Inner Join EmployeeManagement_History A on A.UserID=BF.UserID and BF.TimeDate between A.StartDate and isnull(A.EndDate,GetDate())
Where CASE @TEIR
	WHEN 1 THEN [Employee]
	WHEN 2 THEN [Supervisor]
	WHEN 3 THEN [Manager]
	END = @WHO_WHERE
AND BF.TimeDate between @StartDate and @EndDate
OPTION(MAXDOP 1);


-- Moved all the calculations down to here, so speed up the process
Select Distinct .............
from #TempA A LEFT JOIN  (
		SELECT 
		B.TimeDate,B.WorkDescription,
		UnitWorkedCummulative = ( SELECT sum(UnitWorked) 
									FROM #TempA A 
									WHERE A.TimeDate <= B.TimeDate
									 AND A.WorkDescription = B.WorkDescription),
		ErrorCummulative = ( SELECT sum(TotalErrors) 
								FROM #TempA C 
								WHERE C.TimeDate <= B.TimeDate
								AND C.WorkDescription = B.WorkDescription)
		FROM #TempA B	) B ON (A.TimeDate =B.TimeDate AND A.WorkDescription = B.WorkDescription  )
WHERE A.Work is not null
Order by A.WorkDescription, A.TimeDate
OPTION(MAXDOP 1);

Drop Table #TempA;
ASKER CERTIFIED SOLUTION
Avatar of Shannon_Lowder
Shannon_LowderFlag of United States of America image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 11 Comments.
See Answers