June 19, 2006 at 2:01 pm
I have the following setup -
Server 1: Windows 2003 Enterprise Edition; SQL 2000 Standard Edition; Memory = 4 Gig
Server 2: Windows 2003 Enterprise Edition; SQL 2000 Enterprise Edition; Memory = 8 Gig
Awe has never been enabled ..or max server memory set..etc..
I was curious if the following was the best way to configure the memory:
Server 1: enable /3gb switch in boot.ini (This gives sql 3 gig and leave 1 gig for OS correct??); DO NOT ENABLE Awe since awe is for servers with more than 4 gig of memory.
Server 2: enable /3gb /Pae switch in the boot.ini enable awe and set the max server memory to 6 gig (would this mean sql would get 6 gig and leave 2 for the OS??)
Would the above memory configuration be correct?
Thanks!
JWA
June 20, 2006 at 3:07 am
Server 1 will never use AWE because there is not enough memory on the machine. SQL will have access to 3GB memory due to the /3GB switch. It will do no harm to have the /pae switch in boot.ini and turn AWE on in SQL, but AWE will not get used.
Server 2 will use AWE. SQL will have access to 5GB memory due to the /3GB switch.
However, do not assign all 3GB or 5GB to SQL or there will be no memory available for non-operating system tasks that need to run (e.g. backups, anti-virus, etc). In our shop where we use /3GB we need to allow 1GB for Windows, 1GB for other stuff, and the rest can go to SQL.
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
June 20, 2006 at 3:40 am
I use exactly same conf as server 2 - 8Gb RAM and Win 2003 R2
let me correct this "Server 2 will use AWE. SQL will have access to 5GB memory due to the /3GB switch.". Any process CAN HAVE access to 3GB of memory when /3GB switch enabled; SQL CAN HAVE ALL THE MEMORY in AWE mode, exept ~128MB for OS running when /PAE switch enabled.
So feel free to turn on /PAE along with /3gb option. They don't interfere.
I use 7,5GB for dedicated SQLserver process and works just fine along with hyper-threading turned on (4 physical 1MB cache Xeon processors = 8 logical)
June 20, 2006 at 3:52 am
I mis-read the original post. On a 8GB box server 2 would have access to 7GB with AWE enabled.
Note that only the database bufferpools can go into AWE memory. All other SQL functionality has to live in memory below the 4GB line.
Some people can use hyper-threading (lightweight pooling) very reliably in their shop. However, there is a long list of SQL functionality that Microsoft do not support if hyper-threading is turned on (search the KB articles). We tried hyper-threading but had reliability problems, so you really need to see how it runs with your workload.
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
June 20, 2006 at 6:49 am
There has been a misconception that awe can only be used with memory over 4Gb - this is not true. In a server with 4GB of ram you have two options, enable /3gb or enable /pae.
with /PAE and no /3gb you can allocate , say, 3.5 gb ram to sql server.
/3Gb allows increase in some pooling - please read Ken Henderson's book on internals as to how all thes eoptions work and affect things. With servers with 8Gb ram I usually use only /PAE and allocate around 7gb of ram to sql server, I then monitor memory use - if I find lots of free memory then I'll up the allocation, it makes no sense to spend money on ram and then not use it!!
With 32Gb I usually allocate 30Gb to sql server. Note that the /3Gb switch can give problems with xml / dts / and sysmaint ( anything which runs externally in memory ) Using sysmaint.exe for say log shipping is ok but each sysmaint.exe takes about 8Mb of memory.
I'm slighlty confused by the ref to hyperthreading, this isn't lightweight pooling - different beasts !!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply