We help IT Professionals succeed at work.

Hoist the colors! We’ve added location flags to usernames sitewide, so it's easier to connect with the global community on EE. View My Profile

x

Could my stored procedure be the cause of performance problems?

1,462 Views
Last Modified: 2012-05-09
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;

Open in new window

Comment
Watch Question

First, check do you really need the MAXDOP??

I think it look like you have some missing index. try to identify that.

try to create an clustered index on #temp table and see the performance.

Author

Commented:
My stored procedures only take about 3-5 seconds to run each time.  Considering how much information they are gathering I don't think I could see much improvement for indexing my temp table.

And it doesn't explain why the database crashes on the 1st of the month.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Yea it really is the 1st of the month.  My rights on the server are limited so I can't see much, but we have no process or scheduled task that only run 1st of the month, or last of the month.

In fact we only have one scheduled task that runs every morning, but that is only a few days old and this problem has been going on for about a year now.

The only thing I know is:
1.  Database crashes the 1st of every month
2.  Database people blame this stored proc (IMO because it is ran so often it is running when it crashes)
3.  sp_updatestats fixes the problem until the next month.
4.  autoUpdateStats runs every Sunday.

Does anyone see anything wrong with the stored proc that could cause it?

And the MAXDOP was "suggested" by the database people to fix our problem, but hasn't done anything.
CERTIFIED EXPERT
Top Expert 2012

Commented:
Your Stored Procedure is getting blocked by some other process/transaction.  You should add indexes to your temporary tables as suggested previously and recommend that any missing indexes be added to all permanent tables.  In addition you can consider adding WITH (NOLOCK).  The downside to this is that it will read "dirty" (uncommitted) data.
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>In my experience when re-stat-ing the database appears to be the solution, there is something going on with your data set. <<
Absolutely.  If you are having to resort to doing UPDATE STATS then you need to optimize your query.

Author

Commented:
Ok, I guess I don't understand.
>>resort to doing UPDATE STATS then you need to optimize your query.<<

So how can a query cause the problems needed to run update stats?  And cause that problem on exactly the same day of the month each month?

My understanding of stats, is that it helps process the query, and having bad stats can cause a query to run slow.  So how can a query cause bad stats?
CERTIFIED EXPERT

Commented:
I don't believe the select statement is causing bad stats, I believe there are other processes going on that could be causing bad stats.  If you have any processing that deletes/updates/inserts batches of records to the same table over and over, your statistics will become stale, and actually cause the queries to run slower, since they are using the statistics to help locate data for you.  Do you have any large batch processes running against your work or error tables?

Author

Commented:
Oh, yea.  We are deleting about 500,000 rows and inserted about 2,000,000 rows each day in batch jobs.

Which could be causing the bad stats.  Since this started out as a simple question, We can just end it here.  Just wanted to know if my stored proc was the problem, trying to answer exactly what is going on will take a lot more time and effort.

Thank you guys and girls.
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>Just wanted to know if my stored proc was the problem, trying to answer exactly what is going on will take a lot more time and effort.<<
And the answer is yes.  Your Stored Procedure is very likely the cause.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
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
Empower Your Career
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

Ask ANY Question

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

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.