Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aex
Yak Posting Veteran
60 Posts |
 Posted - 2009-02-02 : 00:52:35
|
| From the two years of working with sql server, one thing haunted me for long is memory reclaimation. The database that I handle now processes million of records daily (update, delete, insert, compare, join operations). Of course, the operation logic is wrapped in stored procedures. Usually the process works fine until a very large set of raw data is coming in, say few million of operations are executed.I switch to task manager to check the memory that is occupied by sql server for each batch of processing, and I find that the memory held by process: sqlservr.exe is high (going 2GB) and the memory is not reclaimed even after the batch processing is completed. This often cause the next batch processing very slow (took more than a day which usually took only few hours) in processing due to low memory as what I can possibly guess.The problem is solved when I restart sql server which obviously I see that the memory is reclaimed. However, what I am in doubt is whether is this the only solution or are there any other better ways to solve this problem?My sql server works in following environments:OS: Windows Server 2008HDD: 200GB+RAM: 8GBServer Edi: Standardaex |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-02 : 13:48:37
|
Looks like your Procedure Cache is eating up your memory with unnecessary cache plans causing you to slow down? Post the result ofDBCC MemoryStatus |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-02-02 : 13:54:27
|
| How is the processing that pegs your system being done. Is it coming in via BCP or through SSIS? Row by row or set based? Also is it wrapped in a transaction?Mike"oh, that monkey is going to pay" |
|
|
|
aex
Yak Posting Veteran
60 Posts |
Posted - 2009-02-11 : 02:03:06
|
Hi guys, sorry for the late reply as I am really stuck due to the project deadline.quote:
The high memory utilization is completely normal and does not cause the next batch processing to be very slow.Restarting SQL Server should not be done to solve this. You should be looking into why it slow down such as by running SQL Profiler, comparing execution plans, updating statistics, defragmenting indexes, and running Performance Monitor.
I am aware of SQL profiler, execution plans etc. It is just that in order to consider about the tuning tools/methods, I have to grasp some understanding on how to use them, to which the performance issue is critical causing no way for me at the moment to tune by looking at the indexes defragmentation, execution plan etc. This is why I choose to restart the SQL Server as last resort of solution temporarily. However, definitely I will look into the tuning tools seriously.quote:
Looks like your Procedure Cache is eating up your memory with unnecessary cache plans causing you to slow down? Post the result ofDBCC MemoryStatusGo to Top of Page
After I run DBCC MemoryStatus, I've got the number of results and I will paste part of the result as below:Memory Manager KB ------------------------------ --------------------VM Reserved 1741620VM Committed 1657656AWE Allocated 0Reserved Memory 1024Reserved Memory In Use 0(5 row(s) affected)Memory node Id = 0 KB ------------------------------ --------------------VM Reserved 1737524VM Committed 1653712AWE Allocated 0MultiPage Allocator 17640SinglePage Allocator 269616(5 row(s) affected)MEMORYCLERK_SQLGENERAL (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 1312 MultiPage Allocator 1496(7 row(s) affected)MEMORYCLERK_SQLBUFFERPOOL (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 1610636 VM Committed 1610636 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 0 MultiPage Allocator 392(7 row(s) affected)quote:
How is the processing that pegs your system being done. Is it coming in via BCP or through SSIS? Row by row or set based? Also is it wrapped in a transaction?
The logic flow is BCP, bulk insert/delete/update operations, and no, the transaction isn't wrapped. The whole chunk of processing is wrapped in one and only transaction.aex |
|
|
|
|
|
|
|
|
SQL Server Failed to Reclaim Memory?