MS SQL Server 2000 Batch Job

Last Post 10 Dec 2007 11:43 AM by SwePeso. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
lhhoi
New Member
New Member

--
09 Dec 2007 07:09 AM
I'm running a stored procedure which doing some select statement based on account number. If there is/are matching found then it will issue an update statement for those matching records (on a separate update statement). The process will be repeated for 4000 times. When job started, I can see the memory usage keep increasing (from Task Manager). The higher is the memory usage, the slower is my batch process. It takes 8 hours to process around 500+ accounts.

I think it's not caused by record locking, coz I checked the event viewer, no such message shown. I do not have a begin transaction statement so I assume all updates are commited immediately. Could it be memory leak problem? Please help.

lhhoi
New Member
New Member

--
09 Dec 2007 09:08 PM
only one cursor, which is at the outmost loop.
Is DBCC FREEPROCCACHE or DBCC FLUSHPROCINDB(<dbid> help in the middle of the batch process?
lhhoi
New Member
New Member

--
10 Dec 2007 06:46 AM
The biggest table for update is around 2700000k with 7096512 rows, 2 GB memory allocated to sql.
Already minimised the number of cursor, now left only one.
SwePeso
New Member
New Member

--
10 Dec 2007 11:43 AM
Care to post the code here?
Maybe we can get rid of the last cursor too?
SQLUSA
New Member
New Member

--
11 Dec 2007 02:32 AM
Are your indexes in top shape? Or deteriorated?

Are you reindexing the business transactional tables every night with 70% FILL FACTOR?

Is your sproc optimized?

Kalman Toth, Business Intelligence Architect
SQL Server Training - http://www.sqlusa.com/highperformance2005/
lhhoi
New Member
New Member

--
12 Dec 2007 08:17 PM
Thanks everyone, problem resolved. Caused of the problem is because of a view which returning all the account transactions.

Now question is why memory wasn't release after the stored proc is terminated, not even with FREEPROCCACHE. I'll have problem if this isn't a one time off batch job.
You are not authorized to post a reply.

Acceptable Use Policy