June 28, 2009 at 12:08 am
Hi,
We have windows 2003 Std server with 4GB RAM. SQL server 2005 was using about 1.8GB RAM. Do we need to add /GB switch in .boot.ini file?. Do we need AWE enabled in SQL server?
In my opinion, we need /GB and no need to enable AWE? what is your opinion?
Thanks in advance
June 29, 2009 at 2:45 am
The switch is called /3GB, not /GB.
Yes, you will need the /3GB switch to allow SQL Serve rto use more memory. You will only get 3GB useable by SQL Server if you have a minimal Windows configuration and just about nothing else apart from SQL Server database engine running on your box. Most sites with a similar setup will be lucky to get SQL using 2.5GB.
You can also add the /PAE switch to boot.ini. This turns on a number of features in Windows. One of them is extended memory support so that applications that are PAE-aware can use memory above the 4GB line. Other features include data execution prevention, which is a good enough reason on its own to turn this on. It also enables hypervisor support in Windows.
There are different views on if AWE gives you any benefits on a box of this size. No-one has said it could be harmful, and IMHO we are now hearing from a number of people that using AWE on all 32-bit SQL instances may be beneficial. So it seems that at worst you may waste some time turning on a feature that can do nothing because of the memory you have available, and at best it may give some performance benefit. However, AWE will do nothing unless you also have /PAE enabled in Windows.
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 29, 2009 at 6:06 am
Thank you ed
June 30, 2009 at 4:18 am
Thanks again Ed.
Is it worth trying when W2K3 has only got 3 GB RAM ?????
June 30, 2009 at 4:44 am
Dear friend(s),
/3GB switch is related to Virtual memory .
When any new proccess borns it gets 4 GB of virtual memory .
This 4Gb is divided into 2GB (used by OS for Kernal Mode)+2GB (used by the process for its work and we also call it PAS (process address space)) .In total its called as Virtual address space .Remember this is actually VIRTUAL .
/PAE is related to Physical memory and is helpful only on 32 bit servers.
On a 32 bit servers if you want OS to see more than 4GB of address space you need to use /PAE .It creates a smalll window in VAS to point to the extra RAM (> 4GB).
Now , if you have RAM less than or equal to 4GB , there is no sense using /PAE .
If you are using /3GB you will have more virtual memory and SQL will get bigger buffer pool (3GB-384MB)...this will surely boost performance .
But i will not recommend you becasue I have seen more issues when we use /3GB on high activity servers .these issues are Systems not boting up and showing you blues screens , SQL server 2000 giving you false/real Schedular hang issues like 17883s and 17884s .
My recommendation would be :
-> Not using /3GB
-> add more RAM and use /PAE
-> try to sue x64 bit servers as far as possible as you will never have virtual memory pressure .
Hope this helps ..
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 30, 2009 at 4:57 am
If you look at some of the more recent blogs from some of the Windows, my view is there is a general agreement that the functionality tied up with /PAE is worth using whatever memory you have.
Many people think that /PAE only enables Windows to use memory above the 4GB line. The original PAE functionality in NT4 only included this, but in W2003 and above it also includes data execution prevention and hypervisor enablement. The /NOEXECUTE switch also controls the PAE functionality, and provides the parameters for data execution prevention.
Unless you have /NOEXECUTE set to allow data execution prevention, then having /NOEXECUTE in boot.ini means that you do not also need to use /PAE, but there is no harm in having both switches.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply