SQL 2005 64 bit Consuming RAM Rapidly (Please Help)

Question

SQL 2005 64 bit Consuming RAM Rapidly (Please Help)

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Different application hitting the server, or the identical application and same traffic?

It's been mentioned at least half a dozen times that SQL using all the memory is perfectly normal, but you are skipping over that critical item.

remember how SQL is going to use most of that memory:

SQL wants to serve the data in the fastest way possible.

the fastest way possible is to keep data in memory until SQL determines that it will probably not be needed.

if the system has 20 gig of ram, and I query some data, SQL may keep the data i requested in memory for similar queries until it decides it need the memory for other purposes,and that data hasn't been accessed recently anyway.

so if the SQL Engine gets 10K different queries hitting it (based on the execution plans of those queries), if it has enough memory, it will keep all that data in memory so that it can more quickly return the data.

there is no need to recycle the server, all you do is make it do what it was doing in the first place...check if the data requested is in memory...if not, fetch it and put it in memory for subsequent queries.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jason.nodarse Ten Centuries Points: 1143 More actions · Answer 2

Thanks for the explanation. It is just a red flag to me to see a server consume 16plus gigs in one day when the previous server which was 32 bit would take a week to consume 13gigs. There are multiple applications running against this DB. I will take your advice and stop looking at task manager and start using perfmon.

I just hope this does not cause any outages letting the system consume all 32gigs ultimately. I hope it stops consuming at some point. This is my fear.

Thanks again.

Should I leave the AWE unchecked or should I check it in SSMS? Does it matter using 64 bit. What I have read is this setting does not apply if you are running a 64 bit system it is only functional using 32 bit.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

I'm going to pile on what Gila and Lowell have said.

STOP RESTARTING SQL..

Unless you have other software installed you have effectively set aside 14GB for the OS and SQL performance in MANY MANY cases is only enhanced by more memory. If this is a SQL ONLY box change the memory setting to 28-30GB. You won't run out. With Lock pages it won't be paged, it will force everything else to be paged if it were to run low but not SQL.

It is important to also understand that a max memory of 18GB does not mean an absolute limit of 18GB but it will stick close to that.

I don't care how fast it allocated memory in the past, SQL will use memory at the level it decides up to the max, it wants to cache data in memory for fast retrieval. I always allocate the maximum memory I can to SQL, leaving a little space for SSIS and the OS, but I'm not going to waste much and you are effectively wasting 12GB of RAM.

As a further point, your comments have shown that you are fairly new to SQL admin, and this is ok, but I would recommend some training.

CEWII

jason.nodarse Ten Centuries Points: 1143 More actions · Answer 4

Elliot,

I will take your advice and let you know how it works out for me.

Thanks again !!

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

We'd love to hear back..

And the AWE setting in 64 bit is effectively ignored, not sure why its even there in that edition, probably backwards compatibility.

If you set it to 28-30 it will stop growing right around those numbers.

Good luck!

CEWII