THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLOS Team

A blog for members of the SQL Server SQLOS team to share information and get your feedback.

Memory Manager Configuration changes in SQL Server 2012

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

0

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

0

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/
Published Thursday, July 12, 2012 8:07 AM by SQLOS Team

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Pramod Kumar P C said:

I set on my SQL server Minimum server memory=0 and maximum = 16000 in sql 2012 sp1 My Physical server memory = 20GB. Once after upgraded from 2008 to 2012 sp1, I am facing a severe Performance outage and memory pressure. What to do ?

November 7, 2014 4:35 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement