?
Solved

SQL 2005 x64 memory usage (and leakage)

Posted on 2008-10-10
5
Medium Priority
?
255 Views
Last Modified: 2012-05-05
I just set up a new SQL2005 server standard edition x64 on win 2003 server x64.  The box is a quar core xeon with 4 gigs of ram and a 6 gig swapfile on a partition other then the sql install or the db partitions.  Our developers moved applications from a SQL2000 (Standard, x32) server on to the 05 box.  What has been happening is that over the course of the day, page file useage rises.  It gets up to about 3.9gigs, even though the CPU's activity is nill (1-2% or so).  Once it gets that high, I start panicing and restart the SQL service (late at night, as not to drop active sessions) and it drops to about one gig.  Then it begins it's rise again.  

Now, the server hasn't crashed, or become unresponsive yet, but historically, when our SQL2000 boxes started a rise like this, it ended badly...  

So my initial assessment was a memory leak in one of the applications using the server, but none of the developers can identify any problem with their code (all asp.net web apps).  So my thoughts went to x64 SQL 2005, that maybe this is a normal condition?

Any comments?

Thanks
0
Comment
Question by:mchad65
  • 3
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22686425
go to the sql server parameters, and set the MAX MEMORY setting to 3500 (MB)
0
 

Author Comment

by:mchad65
ID: 22686791
Ok, did that.  So I assume that will prevent SQL from crashing the server?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22686821
it will make that SQL Server will stop taking memory for it's usage at 3.5GB, avoiding that sql server and any other apps on the server start swapping, and resulting in all the apps slowing down to a crawl ...
0
 

Author Comment

by:mchad65
ID: 22686845
As I suspected.  The box is dedicated to SQL only.  

Regarding my original question, looking at the memory utilization I described, would you call that a memory leak, or normal sql 2005 operation?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22686961
it's normal operation. sql server will try to put all the data, as tables are queried, into memory.
means also that if all the db tables together only have 1GB, sql server will not use much more than 1GB of RAM neither.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question