SQL Server’s memory usage often leads to confusion, particularly when AWE memory is involved.
When the amount of memory available to SQL Server is under 3-4GB, SQL Server (or any other 32-bit application) can reside entirely in the Virtual Address Space (VAS), which is a virtual 32 bit address space allowing each process to use up to 4 GB of memory – 2 GB for user-mode and 2 GB for the system (kernel mode). This virtual address space is then managed by Windows’ memory management to map it into real memory, and SQL Server can use up to 2 GB of memory.Â
A 32 bit machine with 4 GB of RAM can use the /3gb switch in boot.ini to allow user mode (and thus SQL Server) VAS to use 3 GB, limiting the kernel mode to 1 GB.
If you look at Task Manager, you can see exactly how much memory SQL Server is using when SQL Server is entirely in VAS.
However, what happens when your machine has more than 3 GB of memory? The answer is AWE, or Address Windowing Extensions. AWE allows the operating system to map in a window of the extra memory, up to the limit of the operating system, which is 64 GB for Windows Server 2003, Datacentre Edition. The SQL Server process will still require around 256 MB of memory to implement AWE, but the buffer and procedure cache can then be stored in AWE-mapped memory. When looking in Task Manager, SQL Server will only show the 256 MB of memory, and the AWE-resident memory is hidden. The best way to determine SQL Server’s AWE memory usage is via the SQLServer: Memory Manager – Total Server Memory (KB) Perfmon counter.
To turn on AWE, there are three steps:
- Add the /pae switch to boot.ini to allow Windows 2000/2003 to access more than 4 GB of memory
- Grant “Lock Pages in Memory” to the user account that runs the SQL Server process
- Set the configuration setting “AWE Enabled” to 1.
Note that AWE requires 2 GB of kernel VAS to address AWE memory above 16 GB, so using the /3gb switch in boot.ini will limit AWE to 16 GB.
Under Windows 2000, AWE will only be enabled if the available physical memory is greater than the user mode virtual address space. When SQL Server starts, it will lock either the amount of memory specified in the configuration setting “Max Server Memory”, or the amount of free physical memory, minus 256 MB.
This AWE mapped pool cannot be swapped out of physical memory. Also, the value of “Min Server Memory” is not used, as the AWE allocated by SQL Server is never decreased in Windows 2000.
Under Windows Server 2003, SQL Server will always try to use AWE if it is enabled, and SQL Server can dynamically manage its AWE memory usage. Upon startup, it will allocate the amount of memory needed for the startup workload, and from there it will grow as needed, assuming memory is available. It will continue growing up to the value specified in “Max Server Memory”. If the server comes under memory pressure, SQL Server 2005 will release memory if possible, down to the point specified in “Min Server Memory”.
The 32-bit versions of Windows Server 2003 support the following maximum memory limits: Standard Edition 4 GB, Enterprise Edition 32 GB, and Datacentre Edition 64 GB.
It is always recommended to set a value for “Max Server Memory” to ensure that other processes running on the server have some memory available. Leaving ~2 GB is a good setting, for under 32 GB total server memory, ~4 GB if 64 GB total server memory.  Additionally, when multiple instances of SQL Server are running on the one machine, “Max Server Memory” is necessary to prevent one instance from using too much memory and starving the other.
AWE is not necessary on a 64-bit machine, as the operating system’s memory limits are the limits (a 64 bit memory address can point to 16,777,216 GB of memory). However, it is recommended that the “Lock Pages in Memory” permission is granted to the user account that runs the SQL Server process, as this will prevent SQL Server’s memory from getting paged out to disk.
Great article, thanks
[...] Quote from this AWESOME article (I encourage you to check it out… Jim knows more on this subject than I do… [...]
Hi, after doing all this instruction we are only seeing 190-210 mb memory usage for SQL in Taskmgr – do you know why this could be?
Hi Tom,
This is normal – SQL Server’s AWE memory consumption is not measured by Task Manager.
You can use “DBCC MemoryStatus”, or Perfmon counters to find out exactly what SQL Server is using. I like to use the Perfmon counters from within SQL Server. Run:
SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name IN (‘Target Server Memory (KB)’,'Total Server Memory (KB)’)
Look at the cntr_value. Target Server Memory is the current maximum limit that SQL Server will use, and Total Server Memory is the amount the server is currently using.
Jim, thanks for article.
I have a question. I have Windows 2003 Enterprise 8CPU and 8 GB RAM. OS see 8GB. Also I have on this server SQL2005 Enterprise. I enabled AWE in SQL. I wonder why SQL Agent’s log shows 4096MB RAM?
Should I add /PAE /3G in boot.ini file?
Hi Serik,
I wouldn’t worry about what SQL Agent’s log file says. I had a quick look at one of my servers, and the SQL Agent log also shows 4 GB RAM, even though the server has 16, and SQL Server is properly configured. My theory is that as SQL Agent is a 32-bit program, 4 GB is the maximum it can address, so that’s all the Agent log will display. I wonder what a 64-bit SQL Agent log will say…
To find out exactly what memory SQL Server is using, run this code inside Management Studio. If it reports more than 3 GB, you’ve got the AWE configured correctly.
SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name IN (’Target Server Memory (KB)’,’Total Server Memory (KB)’)
Good luck!
Thanks Jim.
Query shows:
——————–
SQLServer:Memory Manager Target Server Memory (KB) 6291456 65792
SQLServer:Memory Manager Total Server Memory (KB) 2677248 65792
————————
Hi Serik,
The Target Server Memory shows that SQL Server expects to use ~6GB of memory, and the Total Server Memory shows that it’s currently using 2.6 GB. This appears as though you’ve got everything configured correctly, especially as you don’t have the /3GB switch – you’d see Total Server Memory capped at about 1600 MB. Keep monitoring it, especially during peak periods, and you should see it start to creep towards the 6 GB mark, unless your database is only 2-3 GB in size.
Thanks Jim. I feel now comfortable
[...] information on how to configure AWE, see my previous post for 2005, or for SQL Server [...]
Jim,
I have a SQL SERVER 2005 sp2 box running on a Windows 2003 OS. THere are 8 processors and 16G of RAM. Just installed. I checked the AWE checkbox. Using perfmon I saw that memory is not being consumed by SQL SERVER. I have installed SQL SERVER 2005 12 times and this is the first time I am having a problem getting SQL SERVER to recognize and consume memory on the box. Any ideas?
SQLServer:Memory Manager Target Server Memory (KB) 13670048 65792
SQLServer:Memory Manager Total Server Memory (KB) 27392 65792
Reviving a very old thread… hoping someone can give me a quick answer…
I’m running SQL Server 2005 Enterprise 32bit on Windows 2008 64bit with 8GB RAM.
Task manager shows that SQL Server tops out RAM usage at around 3.5GB. I believe our CF applications are getting stalled and crashing because the SQL Server is running out of RAM, or perhaps memory pages are getting swapped out.
Is there a simple list of things I need to change in this scenario, so that (1) SQL Server uses more available RAM, and (2) SQL Server does not use more than it should?
If we can’t get it resolved, we may need to move to a newer, 64bit version of SQL Server on 64bit OS…