SQLOS Team : denali

Memory Manager Configuration changes in SQL Server 2012

SQLOS Team — Thu, 12 Jul 2012 14:07:00 GMT

Continuing from yesterday's article about Memory Manager surface area changes in SQL Server 2012, this post looks at corresponding configuraton changes for the new Memory Manager. The surface area change article mentioned that the Memory Manager redesign resulted in being able to more accurately size and govern all SQL Server memory consumption. Let's look at what this means in terms of sizing the total memory usage.

Max server memory

In SQL Server 2008, the max server memory configuration setting only governed single 8K page allocations. CLR allocations, multi-page allocations, direct Windows allocations (DWA) and memory required by thread stacks would not be included, so you'd have to set the -g memory_to_reserve startup setting on 32-bit systems if you're using CLR for example, to reserve sufficient address space.

Setting max server memory becomes more straightforward with SQL Server 2012. The redesigned Memory Manager takes a central role in providing page allocations for the other components, and the max server memory setting governs all memory manager allocations.

With all page allocations are governed, including CLR page allocations, instance sizing is more predictable, which can help a lot in memory constrained and multi-instance scenarios. Direct Windows Allocations, (i.e. calls to VirtualAlloc()) will still remain outside of Memory Manager control.

Here's a summary of the how the memory startup option settings changed in 2012:

SQL Server 2008 R2

Sp_configure option

Default setting

Lowest possible value

Highest possible value

Min server memory

16 (MB)

Value
less than max server

memory setting

Max
server memory

2147483647

(available memory in the system)

16 (MB)

2147483647

(available memory in the

system)

SQL Server 2012

Sp_configure option

Default setting

Lowest possible value

Highest possible value

Min server memory

16 (MB)

Value
less than max server

memory setting

Max
server memory

2147483647

(available memory in the system)

32 bit - 64 (MB)

64 bit – 128 (MB)

2147483647

(available memory in the

system)

sp_configure awe_enabled

SQL Server 2008 R2 was the last release to support the awe_enabled option, which allowed use of memory above 4GB on 32-bit systems. In SQL Server 2012 32-bit architectures can no longer use >4GB, though you can still set this value on 32-bit systems to allow the use of locked pages but it's not needed on 64-bit. Note this setting does not affect the way Address Windowing Extensions are use to implement locked pages in memory on 64-bit systems.

- Guy

Originally posted at http://blogs.msdn.com/b/sqlosteam/

Memory Manager surface area changes in SQL Server 2012

SQLOS Team — Wed, 11 Jul 2012 13:31:00 GMT

There were various changes to memory related DMVs, DBCC memory status, and Perfmon counters in SQL Server 2012 as part of the redesign of the Memory Manager component of SQLOS. The Memory Manager redesign resulted in being able to more accurately size and govern all SQL Server memory consumption, making page allocations more efficient, and better handling and tracking of NUMA node memory allocations. This post aims to document these surface area changes in one place to serve as a quick reference.

Some of these changes were to standardize memory counters as "kb" rather than bytes or pages, continuing a process begun earlier, but now more relevant since the single and multi-page allocators were replaced by an any-size page allocator, so counting pages allocated would no longer give a meaningful view of memory use. This consolidation of allocators also resulted consolidated counters for page allocation.

Other changes reflect the central role of the redesigned Memory Manager in being the interface to the OS, providing page allocations for the other components. A modular summary of the architectural changes is shown below:

DMV Changes

sys.dm_os_memory_nodes

The consolidation of the single and multi-page allocators is reflected in the pages_kb column in sys.dm_os_memory_nodes. The new foreign_committed_kb counter is a result of the improved NUMA management and reporting in SQL 2012 and identifies memory on a noe that belongs to a remote node, which can be useful if you're troubleshooting slower memory access. We will always try and use local memory first, at least until the target memory is reached.

sys.dm_os_memory_nodes
SQL Server 2012	SQL Server 2008 R2
pages_kb	single_pages_kb + multi_pages_kb
foreign_committed_kb	-

sys.dm_os_sys_info

The main memory manager changes here are to standardize memory counters.

sys.dm_os_sys_info
SQL Server 2012	SQL Server 2008 R2
physical_memory_kb	physical_memory_in_bytes
virtual_memory_kb	virtual_memory_in_bytes
committed_kb	bpool_committed
committed_target_kb	bpool_commit_target
visible_target_kb	bpool_visible

sys.dm_os_memory_cache_counters

sys.dm_os_memory_cache_counters
SQL Server 2012	SQL Server 2008 R2
pages_kb	single_pages_kb + multi_pages_kb
pages_in_use_kb	single_pages_in_use_kb + multi_pages_in_use_kb

sys.dm_os_memory_cache_entries

sys.dm_os_memory_cache_entries
SQL Server 2012	SQL Server 2008 R2
pages_kb	pages_allocated_count

sys.dm_os_memory_clerks

sys.dm_os_memory_clerks
SQL Server 2012	SQL Server 2008 R2
pages_kb	single_pages_kb + multi_pages_kb
page_size_in_bytes	page_size_bytes

sys.dm_os_memory_objects

sys.dm_os_memory_objects
SQL Server 2012	SQL Server 2008 R2
pages_in_bytes	pages_allocated_count
max_pages_in_bytes	max_pages_allocated_count

DBCC Memory Status changes

Many of the DBCC memory counters that were previously under Buffer Pool have now been consolidated under Memory Manager to reflect the revised architecture. There is also some additional information to reflect OS errors and NUMA awareness. NUMA growth phase shows memory growth before the target is reached. The “Pages In Use” counter shows the actual number of pages in use, including the stolen pages, database pages, and the reserved memory. The Away Committed counter indicates how much memory from this node is committed from other nodes.

SQL Server 2012	SQL Server 2008 R2
Memory Manager \ Large Pages Allocated	-
Memory Manager \ Emergency Memory	Memory Manager \ Reserved Memory
Memory Manager \ Emergency Memory In Use	Memory Manager \ Reserved Memory In Use
Memory Manager \ Target Committed (KB)	Buffer Pool \ Target (Pages)
Memory Manager \ Current Committed (KB)	Buffer Pool \ Committed (Pages)
Memory Manager \ Pages Allocated (KB)	Buffer Pool \ Stolen (Pages) + Buffer Pool \ Database (Pages)
Memory Manager \ Pages Reserved (KB)	Buffer Pool \ Reserved (Pages)
Memory Manager \ Pages Free (KB)	Buffer Pool \ Free (Pages)
Memory Manager \ Pages In Use	-
Memory Manager \ Page Alloc Potential (KB)	Buffer Pool \ Stolen Potential (Pages)
Memory Manager \ NUMA Growth Phase	-
Memory Manager \ Last OOM Factor	Buffer Pool \ Last OOM Factor
Memory Manager \ Last OS Error	-

SQL Server 2012	SQL Server 2008 R2
Memory Manager \ Large Pages Allocated	-
Memory Manager \ Emergency Memory	Memory Manager \ Reserved Memory
Memory Manager \ Emergency Memory In Use	Memory Manager \ Reserved Memory In Use
Memory Manager \ Target Committed (KB)	Buffer Pool \ Target (Pages)
Memory Manager \ Current Committed (KB)	Buffer Pool \ Committed (Pages)
Memory Manager \ Pages Allocated (KB)	Buffer Pool \ Stolen (Pages) + Buffer Pool \ Database (Pages)
Memory Manager \ Pages Reserved (KB)	Buffer Pool \ Reserved (Pages)
Memory Manager \ Pages Free (KB)	Buffer Pool \ Free (Pages)
Memory Manager \ Pages In Use	-
Memory Manager \ Page Alloc Potential (KB)	Buffer Pool \ Stolen Potential (Pages)
Memory Manager \ NUMA Growth Phase	-
Memory Manager \ Last OOM Factor	Buffer Pool \ Last OOM Factor
Memory Manager \ Last OS Error	-

SQL Server 2012	SQL Server 2008 R2
Memory node Id = n \ Pages Allocated	Memory node Id = n \ MultiPage Allocator + Memory node Id = n \ SinglePage Allocator
Memory node Id = n \ Target Committed	-
Memory node Id = n \ Current Committed	-
Memory node Id = n \ Foreign Committed	-
Memory node Id = n \ Away Committed	-
Memory Clerks \ Pages Allocated	Memory Clerks \ SinglePage Allocator + Memory Clerks \ MultiPage Allocator

Perfmon counters

The Perfmon counter changes follow the same consolidation of counters under the Memory Manager object, and the standardization of counters to KB. Several deprecated AWE counters are now removed.

SQL Server 2012	SQL Server 2008 R2
Object - SQLServer:Memory Manager	Object - SQLServer:Buffer Manager
Database Cache Memory (KB)	Database pages
Free Memory (KB)	Free pages
Reserved Server Memory (KB)	Reserved pages
Stolen Server Memory (KB)	Stolen pages
Target Server Memory (KB)	Target pages
Total Server Memory (KB)	Total pages
-	AWE lookup maps / sec
-	AWE stolen maps / sec
-	AWE write maps / sec
-	AWE unmap calls / sec
-	AWE unmap pages / sec

SQL Server 2012	SQL Server 2008 R2
Object - SQLServer:Memory Node	Object - SQLServer:Buffer Node
Database Node Memory (KB)	Database pages
Free Node Memory (KB)	Free pages
Foreign Node Memory (KB)	Foreign pages
Stolen Node Memory (KB)	Stolen pages
Target Node Memory (KB)	Target pages
Total Node Memory (KB)	Total pages

I'll follow up this article with some additional surface area changes for configuration and new memory manager messages that appear in the errorlog.

The goal of these changes is to make the new Memory Manager activity easier and more intuitive to track, and to clean up some of the values that no longer make sense with the any size page allocator and deprecation of AWE. Anything you used to use but can't find any more? Let me know.

Originally posted at http://blogs.msdn.com/b/sqlosteam/

What causes Multi-Page allocations?

SQLOS Team — Thu, 27 Jan 2011 05:40:00 GMT

Writing about changes in the Denali Memory Manager In his last post Rusi mentioned: " In previous SQL versions only the 8k allocations were limited by the ‘max server memory’ configuration option. Allocations larger than 8k weren’t constrained."

In SQL Server versions before Denali single page allocations and multi-Page allocations are handled by different components, the Single Page Allocator (which is responsible for Buffer Pool allocations and governed by 'max server memory') and the Multi-Page allocator (MPA) which handles allocations of greater than an 8K page. If there are many multi-page allocations this can affect how much memory needs to be reserved outside 'max server memory' which may in turn involve setting the -g memory_to_reserve startup parameter. We'll follow up with more generic articles on the new Memory Manager structure, but in this post I want to clarify what might cause these larger allocations.

So what kinds of query result in MPA activity? I was asked this question the other day after delivering an MCM webcast on Memory Manager changes in Denali. After asking around our Dev team I was connected to one of our test leads Sangeetha who had tested the plan cache, and kindly provided this example of an MPA intensive query:

A workload that has stored procedures with a large # of parameters (say > 100, > 500), and then invoked via large ad hoc batches, where each SP has different parameters will result in a plan being cached for this “exec proc” batch. This plan will result in MPA.

Exec proc_name @p1, ….@p500

Another workload would be large adhoc batches of the form:

Select * from t where col1 in (1, 2, 3, ….500)

…

In Denali all page allocations are handled by an "Any size page allocator" and included in 'max server memory'. The buffer pool effectively becomes a client of the any size page allocator, which in turn relies on the memory manager.

- Guy

Originally posted at http://blogs.msdn.com/b/sqlosteam/