August 22, 2008 at 4:13 pm
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
August 22, 2008 at 6:40 pm
What build level are you at? I believe this is an issue related to procedure cache and that SP2 fixed it.
Jack Corbett
Consultant - Straight Path Solutions
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
August 23, 2008 at 4:50 am
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
August 23, 2008 at 5:03 am
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
March 24, 2010 at 5:01 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply