Setting Max Memory in 64-bit version

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Express » Setting Max Memory in 64-bit version

Setting Max Memory in 64-bit version

Rate Topic

Display Mode

Topic Options

Author

Message

eschlange

Posted Friday, August 22, 2008 4:13 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 20, 2009 12:27 PM
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

Post #557664

Jack Corbett

Posted Friday, August 22, 2008 6:40 PM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

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

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

Post #557694

GilaMonster

Posted Saturday, August 23, 2008 4:50 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651, Visits: 29,903

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 2008, MVP
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

Post #557743

GilaMonster

Posted Saturday, August 23, 2008 5:03 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651, Visits: 29,903

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 2008, MVP
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

Post #557745

dave 92282

Posted Wednesday, March 24, 2010 5:01 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 6:09 AM
Points: 16, Visits: 54

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.

Post #888812

« Prev Topic | Next Topic »

Permissions