August 16, 2005 at 10:54 am
Hi,
I've been reading up on this for the last hour or so, and am still a bit unsure what is correct. We have a server which is dedicated to running SQL server (and in fact a single database on the SQL server). It's running windows server 2003 (the standard edition) and has 4GB of memory. We want the maximum amount of memory to be available for SQL Server
I believe we should be using the /3GB switch in boot.ini, but should we be using AWE as well to get more than 3gig? Is doing so advisable, or will we see degradation in performance due to the OS having less memory available? I saw one article that indicated that in our configuration we were either stuck with 3gig (shared with any other non OS processes), or with SQL server taking all but 128 meg, which struck me as starving the OS a bit.
Any comments?
August 16, 2005 at 2:28 pm
What edition is your sql server?
If it is a standard edition, it will maximum use 2 gb.
With the /3gb switch, the system will have 1 gb, sql server the 2 other I presume
August 16, 2005 at 2:46 pm
If you're on SQL Server Enterprise Edition (and Windows Advanced Server) and you have 4GB of RAM and the /3GB switch, the SQL Server will grab 3GB of RAM and the OS will grab 2GB (vs. 2GB for SQL and 2GB for the OS without the switch). I don't think that you need AWE unless you have more than 4GB, since that's the maximum ammount of RAM that 32bit Windows can natively address.
From Books Online:
Standard 32-bit addresses can map a maximum of 4 GB of memory. The standard address spaces of 32-bit Microsoft Windows NT® 4.0 and Windows 2000 processes are therefore limited to 4-GB. By default, 2 GB is reserved for the operating system, and 2 GB is made available to the application. If you specify a /3GB switch in the Boot.ini file of Windows NT Enterprise Edition or Windows 2000 Advanced Server, the operating system reserves only 1 GB of the address space, and the application can access up to 3 GB. For more information about the /3GB switch, see Windows NT Enterprise Edition or Windows 2000 Advanced Server Help.
AWE is a set of extensions to the memory management functions of the Microsoft Win32® API that allow applications to address more memory than the 4 GB that is available through standard 32-bit addressing. AWE lets applications acquire physical memory as nonpaged memory, and then dynamically map views of the nonpaged memory to the 32-bit address space. Although the 32-bit address space is limited to 4 GB, the nonpaged memory can be much larger. This enables memory-intensive applications, such as large database systems, address more memory than can be supported in a 32-bit address space. For more information about AWE, see the MSDN® page at Microsoft Web site.
August 17, 2005 at 2:18 am
We've just been going through the same thing over here, we run SQL Enterprise with 4GB of RAM, we've just enabled the /3GB switch but have not enabled AWE. I've got to say I was suprised with the performance gain using the /3GB switch, our benchmark queries are taking roughly half the time they used to, but hey now it has another gig of ram to play with it shouldn't be that suprising. From everything that I've read enabling AWE will only be of benefit if you've got more than 4GB of RAM.
August 17, 2005 at 9:56 am
SQL Enterprise and better on a server with 4Gb or more RAM:
boot.ini needs /3GB switch
via sp_configure set your 'max server memory' to 3072 MB (3 Gb)
(you may want to set 'min server memory' to 3072 as well)
via sp-configure set 'awe enabled' to 1
via sp_configure set 'set working set size'
The 'set' option works well on 'dedicated' SQL Servers. If you have 4 Gb, 3Gb for SQL leaves 1 Gb for Windows - plenty for a 'dedicated' SQL Server. Also, the 'set' needs a reboot.
One final note, if you monitor memory usage you'll find out that SQL Server only appears to use 2.7 to 2.8 Gb of the 3 Gb of RAM.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 17, 2005 at 10:19 am
AWE is not needed when Windows total memory is 4GB!!!
* Noel
August 30, 2005 at 4:05 am
Thanks all,
more or less confirms the conclusion we reached.
Sorry for the delay in acknowledging - just got back from a week and a half holiday!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply