June 15, 2009 at 4:45 pm
I've come across a SQL Server 2000 box with 6GB of RAM running under Windows 2000 Advanced Server.
I've been reading a bit about AWE Memory, but I'm getting a bit confused.
With the two screenshots I've attached, there is no boot.ini file found, but within the properties for the server itself I see that it is set to use the maximum of 6GB of RAM.
I guess my question is, is the SQL instance using that 6GB of RAM or is there some setting/switch I need to do?
June 16, 2009 at 6:57 am
You won't find boot.ini sometimes, you need to go to contorl pannel\system\Advanced\setup and recovery\settings\then select EDIT to change the value of what you want.
I got 16GB on my SQL Server Machine, but normally SQL can only use not more then 3 GB unless you add the following information in Boot.ini.
/3GB /PAE (according from Microsoft '/3GB' option is not required if you are using above 16GB)
** You should keep at least 1GB for Operating System. Shouldn't be used all 6GB in Max Memory in SQL. I hope it helps.
Leo
June 16, 2009 at 7:04 am
Thanks for that, will have a look at that path 🙂
Why is it /3GB ? Does that tell Windows to basically use more than 3GB RAM where available? What does the PAE stand for?
Yeah I thought that myself actually. I'll pull that back a bit.
June 16, 2009 at 7:18 am
Please check the following link...
http://www.mssqltips.com/tip.asp?tip=1268
that will tell you everything you need. I hope it helps.
By the way ..I still don't understand about '/3GB' in boot.ini coz..I got 16GB on my server but still address it 2GB, I already asked the answer from GURU in this section, you might saw it already.
Leo
June 16, 2009 at 7:35 am
Hey All,
I've got a server with 8GB ram, it is using an unusally high amount 7.2 GB, but my hardware counters are fine (New DBA I just inherited the system and had no cross training and no Documentation from the old DBA).
I think AWE is enabled but I'm not sure how to tell, I came across this script in a different forum post
exec sp_configure 'advanced options',1
reconfigure
exec sp_configure 'AWE'
exec sp_configure 'advanced options',0
reconfigure
that said it would tell me by the 0 or 1 out put on the 3rd line if AWE is enabled.
That being said, if I run the above script on a Prod system during business hours will it adversley impact the server, or is that script okay?
sorry just trying to be cautious
//EDIT
Just gave it a go, and the world didn't end..... it is amazing how the longer you do this the more cautious you get
AWE is enabled, I didn't see anything in the boot.ini but MS says Server 2003 Enterprise has PAE enabled by default.
My remaining question is should I have /1GB /PAE in the boot.ini to ensure 1GB is available for the OS?
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
June 16, 2009 at 8:47 am
Brad (6/16/2009)
AWE is enabled, I didn't see anything in the boot.ini but MS says Server 2003 Enterprise has PAE enabled by default.My remaining question is should I have /1GB /PAE in the boot.ini to ensure 1GB is available for the OS?
I added it /PAE manually in my boot.ini to make sure PAE is Enabled but when you assign it, you will need to restart the Server.
No, as you said you got 8GB so that you need to set 6GB for Max Memory in SQL Server\Properties\Memory and Leave 1GB for OS. Not in Boot.ini.
Leo
June 16, 2009 at 4:57 pm
Thanks for the help Leo 🙂
June 16, 2009 at 8:13 pm
Will try to make things clear here .After going through it in case there are any questions ,please let me know .
a 32 bit machine can only use pointers with the help of those 32 bits .
This will be 2 multiplied to itself 32 times since .this will sum up to 4 GB .
This 4 GB is virtual memory (which is not real...its virtual and does not actually exist)...each process/application is given 4 GB of VM by the OS (no process knows that there are other processes also running).Thus OS fools the processes by allocating 4GB of VM saying all the RAM is for you .Out of this 4 GB , 2GB is used by the Kernal mode and rest 2 GB is used by the user mode (we call it user address space).
These pointers in VM uses MMU (memory management unit) and PTEs (page table entries) to track the pages in RAM (physical memory)...
Now , in order for the OS to see more than 4 GB of RAM , it uses /PAE switch in the boot.ini file (start >> run >> c:boot.ini >> OK).With this the 32 bit pointer system becomes 36 bit pointer system (2 multiplied to itself 36 times ) and can point to 64 GB of RAM now (also increasing the PTEs to 64GB).OS recycle is needed .
Next , in order for an applicaton to use this extra RAM it needs to be AWE enabled .By doing this it creates a small window that in turn uses the extra RAM.in SQL server you can do it through sp_configure.make sure you execute reconfigure with override after that and recycle the SQL Server.for SQL server 2000 cap the max server memory and min server memory before enabling AWE (as AWE is not dynamic in SQL 2000 and will take entire RAM thus chocking others).for 2005 , i would still recommend to cap the max and min as i have seen some lazy writer errors (lazy writer no free buffers found isues ) due to this .
/3GB switch in boot.ini will increase the user address space from 2GB to 3GB and shrink 1GB from the kernal address space .It will also shrink the pointer system to 34 bits , thus reducing the PTEs to 16GB .this is the reason you cannot use more than 16 GB of RAM if you use both /PAE and /3GB together .Its also not recommended with SQL Server 2000 (i forgot that KB) as it causes some logic issue which causes false schedular hang issues (17883s and 17884s).Secondly just for 1 extra GB we are taking a big risk .You can also face the blue screen issue , where the OS has to do so much of zeroing activity that takes very long time to boot up .
In 64 bit there is nothing like AWE or PAE or 3GB.So there is not need to use those options.this is because the virtual memory is huge (atleast i cant count 🙂 ).
I hope this will help you to decide what you can do or should do .
if you have time and money get yourself "GURUS GUIDE TO SQL SERVER 2000" by Ken Henderson .even though its for 2000 you will read it atleast 20 times once you buy it (I still read it 🙂 ).I treat it as GITA of SQL Server.
Regards
Abhay
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply