Solved

SQL 2005 x64 memory usage (and leakage)

Posted on 2008-10-10
5
246 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

688 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