I've got a problem with my SQL Server 2012 installation on my Windows 2008 R2 x64 box.
Because my memory seemed to be rapidly disappearing (according to the Task Manager graph), but I couldn't see any programs using it, I downloaded RAMMap to get a better picture of what's going on. Turns out all of my memory was being chewed up by AWE.
My memory gets eaten up by SQL Server fairly quickly, up to the maximum that I allow the instance to have (currently 3GB).
When I try to research this, however, everyone seems to say that AWE isn't enabled for SQL Server 2012 64 bit systems, since it isn't needed; the expanded memory that AWE would enable is directly accessible. But the system still will happily chew up memory in AWE until it reaches its limit.
Not sure what other information would be useful to help diagnose this…or even if I'm totally misinterpreting something…I'm at the limits of my current knowledge about this now. Please don't assume a lot of knowledge on my part…while I am tech savvy in general, I am absolutely not a DBA by any stretch.
- Max memory for SQL server is set to 3GB.
- Physical memory on the Server is 10GB.
- I cannot give SQL Server any more memory – I have a virtual machine that takes up 4GB, Visual Studio, Outlook, IIS, etc.
- Somewhat unusually, it is not really being used as a server, but as a development machine, so the DB workload tends to be quite light (though I say that without any hard metrics).
Best Answer
SQL Server is using the AWE memory allocation API to allocate buffer pool memory if "lock pages in memory" is enabled.
If SQL Server's max server memory setting is not set, SQL Server will eventually use as much memory as it needs. That will largely show up as AWE memory in RAMMap. A little more detail can be found here and all the SQL Server memory settings are explained here.
You should set the max server memory setting to allow an adequate amount for the operating system. If not enough memory is available for the OS, paging can occur and cause performance issues. A good starting point is 3 to 4 GB reserved for the OS, but watch the appropriate counters to see if the OS is under memory pressure.
You can get more information about how to select the right max server memory setting at the following links:
Full details of how SQL Server uses AWE and Locked Pages can be found in the Microsoft CSS SQL Server Engineers article:
Fun with Locked Pages, AWE, Task Manager, and the Working Set