cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
SQLPFE
Occasional Visitor
‎03-15-2019 11:36 AM

SQL Server memory models (Part I)

‎03-15-2019 11:36 AM

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

 

  • 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

 

 

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

0 Likes
Like