SQLSERV.EXE eating up all memory
-
Our application is a 3 tier application, application and all .dlls are deployed on web server where as SQL server database is deployed on separate DB server with 1GB ram. After recent update, there is a major performance issue with the application and apparently, SQLSERV.EXE starts eating up all physical memory till application freezes and we have to restart the server, normaly 2-3 times a day.
Can any one help up out pointing out the possible reason of this problem.??
-
I would setup SQL to have a maximum amount allowed so it cannot cram out the OS at all. However, run Profiler to see what major queries happen and you might consider a blackbox trace. It seems you may have a query trying to put enough data in memory to be the issue. Also be sure there are no other processes running on the box using a high amount of RAM, SQL is designed to get as much memory as possible but it may not be the culprit. You might even add Non-Page Pool items to the Task Manager for viewing and if gets higher than 30MB on any particular it might be data locked in the buffers by that app that is causing this.
-
While investidating SQL behaviour on consuming all available RAM, DBCC memorystatus show following results:
Stolen 45606
OS Reserved 1680
OS Committed 1647
OS In Use 1627
General 3194
QueryPlan 43912
Optimizer 0
Utilities 11
Connection 50
What is Stolen pages, SQL books does not explain well on this topic. Why this much ram is being consumed by Stolen pages??
Secondly, in syscacheobjects, there is an object ( a statement) exits 20 times:
20Compiled Plan862984397 select pmvixtr.ipracid as PracID,pmvixtr.ipatid as IPatID,pmvixtr.iapptid as Iapptid, pmptxft.vfname as PatFName,pmptxft.vlname as PatLName,pmptxft.vMI as PatMI,pmptxft.ddob as PatDDOB, pmptxft.vAddress1 as PatAddress,pmptxft.vCity as PatCity,pmptxft.vS
Is there any sepacial reason for this??
-
I am not 100% sure and have not found the details anywhere but I believ stolen referres to there not being enough buffer space for all processes and that another may have taken pages for it's data that later was replaced for use by another. My values on all my servers are extremely low in this area.
This is a SQL 7 server
Stolen 8190
OS 353
General 4297
QueryPlan 4061
Optimizer 0
Utilities 48
Connection 137
and SQL 2000 here (note this has 7 tables pinned in memory)
Stolen 553
OS Reserved 104
OS Committed 91
OS In Use 81
General 186
QueryPlan 339
Optimizer 0
Utilities 5
Connection 45
And as SQL 2K server without anything Pinned
Stolen 187
OS Reserved 216
OS Committed 202
OS In Use 193
General 279
QueryPlan 12
Optimizer 0
Utilities 11
Connection 46
I would suggest seeing how many page faults you are getting in Taks Manager for SQL Server. You may not have enough memory to support all processes.
-
These no's are far below than ours :O.
At 446112K of memory consumption by SQLSERVR.EXE, page faults are 114665K.
We have upgraded our DB server to 2 xion processors and 2GIG of RAM.
Now with 2GM ram SQL server takes 2-3 days to eatup all RAM..
-
quote:
These no's are far below than ours :O.At 446112K of memory consumption by SQLSERVR.EXE, page faults are 114665K.
We have upgraded our DB server to 2 xion processors and 2GIG of RAM.
Now with 2GM ram SQL server takes 2-3 days to eatup all RAM..
WHen it gets to the point of eating up the memory take a look again at the page faults. I don't have that many on most boxes but the server with the heaviest load is hitting about that high.
-
I am still nowhere!!
If Page Faults is one of the reason of eating memory, how it can be rectified??
-
Page Faults demonstrate that a lot of page swaping is going on and points to potential insufficent memory. If all other figures are low but the Page Faults are high it shows excessive swapping is going on.
The Stolen should mean data was removed from memory and replaced by other data, not to the swap file. This too can be because of insufficient memory to handle individual queries.
Edited by - antares686 on 04/25/2003 03:32:47 AM
-
I Agree with Antares686.
I would setup Profiler and watch for big queries.
What is the size of database? May be you need to plan for archiving if possible, just an idea.
-
Database is around 450MB in size.
I watched long queries using profiler in peak hours, max query time noted is 31 secs.
-
I have been having a similar problem and also getting 'insufficient system memory' errors.
I have found this on the MS website about memory leaks. It only applies to SQL 2K.
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b810052
Hope this helps.
Jeremy
-
I have been having a similar problem and also getting 'insufficient system memory' errors.
I have found this on the MS website about memory leaks. It only applies to SQL 2K.
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b810052
Hope this helps.
Jeremy
-
I have been having a similar problem and also getting 'insufficient system memory' errors.
I have found this on the MS website about memory leaks. It only applies to SQL 2K.
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b810052
Hope this helps.
Jeremy
-
Would suggest checking the Size of your system Page File. From My Computer - Properties - Advanced Tab - Performance Options - Change. Suggest you set this to the recommended value shown which should reduce your page faults.
Also consider setting a maximum limit for SQL Server through Enterprize Manager.
-
I have made not so good experience setting the size of Page file to what Windows suggests. The system is slowing down. I have set it manually to 2.5x of suggested size.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply