SQL Server memory models (Part I)

[SQLPFE]

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:

• MMLarge
  • Enterprise edition
  • Large page support
  • Lock Page In Memory
  • TF 834 enabled (c.f. http://support.microsoft.com/kb/920093 )

• MMAwe
  • Enterprise Edition
  • Lock Page In Memory
  • TF 835 not enabled

• MMConventional
  • Standard edition

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

• Large Page model
  • Requires TF 834
  • Preallocate memory at startup
  • Described in http:// support.microsoft.com/kb/920093

• 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