February 12, 2007 at 7:36 am
Just to double check myself here. I am under the impression that if I am running W2k3 sp1 standard and SQL 2005 SP1 standard, I should not have to enable AWE to use more that 2 gigs of memory. Am I wrong?
February 12, 2007 at 7:49 am
memory available is limited by the underlying o/s, there's too many posts about this .. this link shows capcities for windows 2003
http://www.microsoft.com/windowsserver2003/evaluation/features/comparefeatures.mspx
To use the extra memory you'll need the /pae awitch in the o/s with awe and fixed memory OR /3gb switch in o/s and leave the memory settings on sql alone.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 12, 2007 at 8:03 am
I am only trying to use 4gb of memory. I was under the impression you only needed to use /PAE and AWE if you wanted to use more than the standard 4 gb.
February 12, 2007 at 8:33 am
Yes.. on a 32 bit system you need the /PAE to address more than 4GB.. You would use the /3GB switch in the boot.ini to allow the applications to use 3GB of mem...
And there are lots and lots of posts on this site and many others which will answer most of your questions..
February 12, 2007 at 9:42 am
You can use under 4gb of ram with awe OR the 3Gb switch. AND you can use the 3gb switch and awe with more than 4gb of ram.
Up to 16gb of ram you can also use the /3gb switch in boot.ini.
With 4gb of ram you take your choice, as I previously said ;-
/3gb switch - sql server uses just under 3gb ram total with normal dynamic memory settings
/PAE - with awe = 1 and set max memory = 3000 sql server will use 3Gb ram, I've actually taken this to 3,500 Mb without any issues, but as in all cases it just depends.
( There are also lots of incorrect posts about using extra memory with sql server. )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 13, 2007 at 6:48 am
As Colin says, there are a lot of incorrect posts about tuning SQL memory. For what my 5+ years of SQL experience is worth, Colin's post gives the right answer.
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
February 13, 2007 at 7:39 am
Colin.. can you clear something up for me?
if you have only 4GB of Ram (which can be addresses natively with 32bit OS), why would you use the /PAE switch? I would imagine the only benefit is a larger pagefile (>4GB but disk is slow) and an added layer of translation between the App and memory.. Wouldn't there be a performance hit?
Thanks..
February 13, 2007 at 9:18 am
If you've only got 4GB RAM I would only enable the /3GB boot config switch, and that if you don't mind loosing 1GB from the OS (increased paging if using many other apps).
February 13, 2007 at 3:13 pm
sorry but you're still missing the point, the /PAE switch has nothing to do with a page file .. don't understand this question. As I keep trying to point out, probably for a number of years, the /3gb switch is different to /PAE and awe. For those who won't take my word for it get hold of Ken Henderson's book on internals. Using the /3gb switch can have adverse effects in lower memory when you have a lot of out of process activity. /PAE + awe increases data cache only but still reduces the amount of lower memory.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 13, 2007 at 3:57 pm
Colin, I get they are different, which is why they are used in different situations.. I was looking for the reason you would recommend using /PAE+AWE with only 4GB of memory.. I guess if you want to lock the memory to the application but that seems to be a bigger risk of starving the OS/out of process apps.. Anyway.. your's was just the first I had seen recommending that with 4Gb on a 32 bit system...and I was hoping for a little clarification thats all.. Didn't mean to get you annoyed.
The paging file reference was from Intel.. maybe I misread it.. But I'll be sure and pick up Henderson's book.. Thanks
February 14, 2007 at 1:03 am
Rich - no not directed at you - there's an almost constant number of posts over memory on sql server, I don't think there's a definitive description posted anywhere. Giving more memory to sql on a 4gb machine may cause some issues, which ever way. However, I see little point in manitaining large amounts of free memory , when viewed through task manager or perfmon. Each successive o/s seems to require more, NT4 ent I could get to run in under 30mb of ram on a dedicated sql server, so in that instance allocating 2gb to the o/s was a bit absurd.
The 3gb switch increases overall memory to sql server, so the various buffer and cache pools all get increases, whereas with awe it's a larger data cache you get. Generally increasing the data cache gets you better performance as it reduces physical reads and speeds up data access, thus tending to speed up/increase writes ( as less reads ) , but in all things it just depends. As I say on a dedicated sql box then it's possible to increase the sql server memory, disabling unwanted services in the o/s is a good move too - it's not very usual to want to use the print spooler or task scheduler for instance ( to pick 2 )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply