Solved

SQL 2005 x64 memory usage (and leakage)

Posted on 2008-10-10
5
241 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 142

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 142

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 142

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

809 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