First published on MSDN on Aug 05, 2009
You may have questioned why, on 64 bit enterprise Edition, DBCC MEMORYSTATUS reports non zero “AWE allocated” memory even if ‘AWE enabled’ option is not enabled. A short description of SQL Server memory model will help to answer.
Here is an example of a DBCC MEMORYSTATUS output on a 32GB X64 Enterprise SQL Server 2005:
Memory Manager KB
------------------------------ --------------------
VM Reserved 33962976
VM Committed 547508
AWE Allocated 29843712
Reserved Memory 1024
Reserved Memory In Use 0
There are 3 available memory allocation types driven by the following condition:
- MMAwe
- Enterprise Edition
- Lock Page In Memory
- TF 835 not enabled
At SQL Server startup you can find messages symptomatic of the chosen memory model based on the conditions exposed previously.
- AWE model
2009-07-01 11:24:46.330 Server Detected 4 CPUs. This is an informational message; no user action is required.
2009-07-01 11:24:46.400 Server Using locked pages for buffer pool.
2009-07-01 11:24:46.400 Server Using dynamic lock allocation.
- Large page model
2009-07-01 11:24:46.330 Server Detected 4 CPUs. This is an informational message; no user action is required.
2009-07-01 11:24:46.400 Server Using large pages for buffer pool.
2009-07-01 11:24:46.400 Server Using dynamic lock allocation.
- Conventional model
2009-07-01 11:46:24.990 Server Detected 4 CPUs. This is an informational message; no user action is required.
2009-07-01 11:46:25.070 Server Using dynamic lock allocation.
First of all memory functions are standard Windows APIs and only two.
- VirtualAlloc
- All Models
- Use MEM_COMMIT for conventional model
- Use MEM_PHYSICAL for AWE model
- Use MEM_LARGE_PAGE for Large model
- AllocateUserPhysicalPages
- AWE Model
- It will show AWE allocated pages in MEMORYSTATUS or any DMV reporting memory clerk allocations.
To sum up these are the default behaviors:
- Enterprise Edition with Lock Page in Memory
- Lock pages are used for buffer pool. (c.f. errorlog)
- Large Page Support is not enabled.
- AllocatePhysicalPages is used
- Standard Edition
- Large Pages are not used for buffer pool.
- Large Page Support is not enabled
- AllocateUserPhysicalPages is not used
- Sp_configure ‘awe enabled’ option
- Is simply ignored on 64 bit editions
In conclusion if you use 64 bit Enterprise Edition with locked pages enabled the engine does use one of the same underlying APIs (AllocateUserPhysicalPages) as is used by AWE in order to acquire the locked pages. Consequently, if you look at DBCC MEMORYSTATUS or memory clerk output (sys.dm_os_memory_clerks.awe_allocated_kb) it will still show a non zero AWE allocated number.
There have been changes brought to Standard Edition I will expose in a following post.
Lionel Pénuchot – Senior SQL Premier Field Engineer
