March 1, 2007 at 8:35 am
I've got a SS 2000 SP4 server with 4G RAM sitting on top of Win 2000 Adv'd Server SP4. Right now Sql Server is only using 1.7G of RAM because we forgot the /3GB switch. (Note: This is a dedicated box, i.e. only Sql Server is running on it.) Based on past experience, when I add the /3GB switch into the boot.ini file, Sql Server will gobble up about 2.7G of RAM
My questions are these:
Is there any way to get Sql Server to take a little more memory than that, say, 3G?
If so, would I want to do that? And how high should I go - what is the rule of thumb involved here?
March 1, 2007 at 11:06 am
there are two ways to configure 4GB of ram , 3gb switch with dynamic memory management
/PAE enable awe set fixed memory to say 3.25gb
I've run boxes in both configs. Note that if anyone posts the usual link to memory at sql performance.com the information there is incomplete.
You can run awe with 4gb but you MUST used fixed memory, do this through scripts not EM.
the two methods are different, awe gives you a larger data cache, whilst the 3gb switch increases other memory pools - depends on what you want. Ken Henderson's guru guide to internals is the best reference on this.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 2, 2007 at 12:22 am
Yes, definitely physical address extensions is the best option for you.
It's also worth noting that you should use either AWE or PAE but not both.
Let us know how you progress.
March 2, 2007 at 3:37 am
Chris,
I think you are wrong about AWE and PAE. PAE applies only to Windows, and enables the adressing extentions to allow Windows to access memory over the 4GB line. AWE only applies to SQL Server, and allows SQL to use the memory made available by PAE.
These flags are normally used with boxes with more than 4GB memory, but you can take advatage (just) of AWE on a 4GB box. AWE will only work if a) you specify a minimum of 3GB memory at SQL startup; b) The memory you specify is available when SQL starts.
Use of AWE on a 4GB machine will maximise the database buffer cache at the expense of other storage pools. As Colin point out, you can claim up to 3.25GB for SQL using AWE on a 4GB box, which could help performance if your databases are large but the user community is relatively small. If you have a very large user community accessing relatively small databases then using fully dynamic memory with the /3GB switch may be your best option. Better still would be more memory, and running it on a 64-bit system.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 2, 2007 at 5:48 am
chris - you can't enable awe without the /PAE switch. Perhaps you intended to say don't use the /PAE and /3GB switch on a server with 4GB of ram ? ( which you musn't do )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 2, 2007 at 8:08 am
MS has a KB may give you some details.
How to configure SQL Server to use more than 2 GB of physical memory
March 6, 2007 at 3:21 pm
Thx guys. I really appreciate all the great information...
November 16, 2007 at 6:37 am
Hi guys,
I took long to respond but I meant to say that you should not use PAE with the 3GB switches at the same time. Sorry about the mixup.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply