Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Setting Max Memory in 64-bit version


Setting Max Memory in 64-bit version

Author
Message
eschlange
eschlange
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 28
Hey Folks,

We're relocating our database to a 64-bit server, and try as I might I can't seem to get the maximum memory setting to have any effect on things. I know the 64-bit version is a bit different when it comes to memory management (no AWE) but does it also not let you set the maximum memory?

As you can imagine, it just eats up my ram until other applications start breaking--hence my need to limit it.

I've tried stuff like
use master
Go
exec sp_configure 'show advanced options', 1;
Go
RECONFIGURE;
GO
exec sp_configure 'max server memory (MB)', 100;--or whatever number you wish
GO
RECONFIGURE;

and while that number does change the config_value I see when I run sp_configure, the sqlservr.exe process just keeps eating up memory.

Thanks in advance for any pointers!

Regards,
Eric
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11018 Visits: 14858
What build level are you at? I believe this is an issue related to procedure cache and that SP2 fixed it.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44377
I believe that you need to restart the instance before the max memory takes effect. It certainly does work (and needs to be set) on 64 bit.

100 MB's a little low for SQL Server. How much memory do you have on that server?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44377
Jack Corbett (8/22/2008)
What build level are you at? I believe this is an issue related to procedure cache and that SP2 fixed it.


The proc cache pre SP2 could grow to about (iirc) half the size of the buffer pool, reducing the amount of memory available for the data cache and increasing the amount of time to find a cached plan. SQL still honoured the max memory setting, which limits the size of the total buffer pool.

I recall seeing my proc cache at 20GB on SP1, with SQL set to max memory of 42GB, total of 48GB on the box.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


dave 92282
dave 92282
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 85
Hi
I too am having a similar problem, I have two environments both windows 2003 64bit enterprise, both SQL enterprise 64 bit both have 128GB of RAM both have sql sp3 the difference is one is a live production environment and the other is a dev environment.
On my live environment SQL conforms to the max memory of 120GB I have set and will not go above this usage. My dev server however is ignoring this setting I have set it to 118 GB of RAM but continually goes up to just under 128. Which subsequently leaves no memory for IIS causing out of memory errors?
The only difference I can see is that my live environment used a windows R2 and my dev environment didn’t use R2 to install windows, I can’t imagine why this would be the cause but I am clutching at straws is this the possible cause???? Any thoughts please guys.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search