Could my stored procedure be the cause of performance problems?

Meps
Meps used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Most Valuable Expert 2014
Top Expert 2009
Commented:
please clarify:
> but every 1st of the month the database crashes

if it's really the 1st of every month, I would rather look up some scheduled job stuff that runs the 1st of the month (in the morning) or the last day of the month (night), including database backup/maintenance stuff.
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

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.
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.
I would suspect that there is a large insert or update of data on the first of the month.  Or, there is enough of a change that the database file is resized, and as a result, your indexes and statistics grow out of date.  I would look at your inserts, are you loading a lot of data in large batches?  If so, I would suggest reindexing / re-stat-ing right after those large inserts.

Also, you may need to consider re-indexing / re-stat-ing more often than monthly.  You may be able to look at those indexes used in your queries to note specific ones that may be growing out of date.  In my experience when re-stat-ing the database appears to be the solution, there is something going on with your data set.  Look there next.
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?
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.
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial