SQL Server Performance Forum – Threads Archive

with AWE, max server memory settings take all memo

I have a server machine with 6GB of RAM, with Win2K Advanced Server and SQL2K EE. I have to use /3GB and /PAE switches in the boot.ini file of the OS in order to take full advantage of the physical RAM. From SQL server I have to enable AWE in order to address this much RAM. Now according to Microsoft, with this much RAM, OS keeps for itself 2GB RAM, and the rest then is available for the application. With the help of /3GB switch, OS releases 1GB RAM out of its 2GB for the applications. So according to this theory, with my settings, I should have 5GB of RAM available (out of total 6GB physical RAM). But while enabling AWE, if I set the "max server memory" to 5.5GB, it is allocated to the Server leaving only 0.5GB for the OS. Can any body explain why is this contradiction???
I know that even Microsoft says that if we won’t set the max server memory while enabling the AWE, AWE then will take all the available memory leaving behind only 128MB for the OS. This is a complete contradiction to the statements which Microsfot gives while explaining /3GB and /PAE switches.

Capture the counters for MEMORY, Physical disk and others using PERFMON to asses the situation. _________
Satya SKJ

It’s not a case of the OS reserving memory for itself, it’s a case of how much an application can use. Remember that AWE involves mapping the extended memory whereas the /3GB switch allows an application (in this case SQL Server) to access upto 3 GB of "flat" address space (whilst it’s all virtual memory I use flat and mapped to refer to the linear and three level address translation used for "normal" and AWE memory). With PAE enabled, the operating system moves from a two-level linear address translation to a three-level address translation. The extra layer of translation is what provides access to physical memory beyond 4 GB. The less memory mapping the server has to manage the more efficently it can manage the memory thus the relavent points are 1) /3GB allows SQL access to 3 GB of "flat" address space (2 level)
2) /PAE allows the OS to address >4GB (3 level)
3) AWE allows SQL to use >3 GB (3 level) Thus with 6 GB physical memory available, and the /3GB and /PAE switches set and AWE enabled and max server memory set to 5.5 GB then SQL has 3 GB "flat" address space (2 level)
2.5 GB "mapped" address space (3 level) without the /3GB switch it would have to map an extra 1 GB of memory which would be less efficent. Also AWE memory cannot be used for certain types of operations such as sorts so it’s beneficial to include the /3GB switch rather than just rely on AWE. With regard to the OS then yes if you have more than 16 GB you shouldn’t use the /3GB switch because of the increased memory requirements of the kernel to manage the extended memory and you should also leave the OS at least 1 GB. The memory the OS needs to manage AWE grows as the amount of physical memory to "map" grows. This is why you can allocate 5.5 GB of memory to SQL Server.
HTH Jasper Smith

Thanks Jasper, As usual, you came for the help. This is the best explanation that I have go so far for this issue and its logical off course. Now is there any way that we really now that out of assigned 5.5 GB of memory (via AWE), how much exactly SQL server is using really?? In other words, is there a way so that I know that the assigned 5.5GB memory is either not enough or bit too very much??

One indication of whether you have enough RAM for your SQL Server is to watch the Buffer Cache Hit Ratio. It it is consistantly high,like 99% or more, then you have enough RAM. If it is 95% or less, then more RAM is indicated. Determining if you have too much RAM is a little harder to tell. But if you go to Task Manager and do to the Performance Tab, take a look of the "Available" Physical Memory. If this figure is very high, this may be indication that your server has more RAM than SQL Server can currently use effectively. These are only rough guides, but are a good place to start. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

Hello Mr. McGehee, Buffer Cache hit ratio in my case more 100% (100.4%). Strange figure…..
With Task Manager, I think we can’t realy judge about the memory usage issue, if we are using AWE. AWE assigned all the memory defined in the "max server memory" settings to the SQL server and it is not a dyanamic allocation. What I want to know, if I have assigned 7GB of RAM to SQL server, how can I know that how much out of 7GB of RAM is lying idle. I have 2 databses (quite busy, more than 700 user connections alway reaches upto 1100 user connections at peak times). My server machine is also a dedicated SQL server machine (means no other applications are running on it). regards, Hameed.

Hameed, interesting question. I’ve often wondered how to size memory if you’re using AWE.
There is a PerfMon counter called Page Life Expentancy in the Buffer Manager object. What the counter tells you is how many seconds a page resides in the buffer pool. So if the count is high data is staying in the buffer pool without having to be flushed out to make room for new data. Thus the higher the count the better off your memory situation is. As a rule of thumb (explained to me by MSFT) a 300 is a value to shoot for.
This is not as clear cut as Target Server Memory vs. Total Server Memory which is viable on non-AWE configs, but its a best guess.

The counter, SQLServer:Memory Manager: Total Server Memory (KB) tells you how much dynamic memory (RAM) that SQL Server is using. If you want to find out how much RAM SQL Server has consumed, but is not really using, then look at this counter: SQLServer:Buffer Manager: Free Pages, which is the list of free pages (8k) tht SQL Server has available in its buffer cache, but is currently not using. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

The counter "Total Server Memory" gives the figure: 7106872 KB, and is fixed as I have assigned 7GB of RAM to SQL server via AWE.
Buffer Cache Hit Ratio: 99.993%
Page life expectancey: 626000 (average) looks like a page never leaves the buffer pool…
Free Pages: 190 Average, which means if every page is 8K then around 1.5MB of memory is free in the Buffer Cache. Isn’t it strange that out of 7GB of RAM and with a 99.993 buffer cache hit ratio, and such a huge page life expectancy, such a small amount of free pages are available??? Do these counters give us the right value if we are using AWE. Like e.g., "Working Set Size" counter does not give the right value in case of AWE use.

Using AWE does present some mysteries, and I don’t know all the answers. But it appears that your SQL Server has plenty of RAM and you are not experiencing any memory bottlenecks. If fact, it looks like you have more RAM than you really need (for now.) If performance is OK for you know, I wouldn’t worry too much about it. But of course, if you come up with some difinitive answers about AWE, we all would like to hear them. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

]]>