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.

SQL Server Memory Manager Changes in Denali

The next version of SQL Server will contain significant changes to the memory manager component.  The memory manager component has been rewritten for Denali.  In the previous versions of SQL Server there were two distinct memory managers.  There was one memory manager which handled allocation sizes of 8k or less and another for greater than 8k.  For Denali there will be one memory manager for all allocation sizes.

 

The majority of the changes will be transparent to the end user.  However, some changes will be visible to the user.  These are listed below:

·         The ‘max server memory’ configuration option has new lower limits.  Specifically, 32-bit versions of SQL Server will have a lower limit of 64 MB.  The 64-bit versions will have a lower limit of 128 MB.

·         All memory allocations by SQL Server components will observe the ‘max server memory’ configuration option.  In previous SQL versions only the 8k allocations were limited the ‘max server memory’ configuration option.  Allocations larger than 8k weren’t constrained.

·         DMVs which refer to memory manager internals have been modified.  This includes adding or removing columns and changing column names.

·         The memory manager configuration messages in the error log have minor changes.

·         DBCC memorystatus output has been changed.

·         Address Windowing Extensions (AWE) has been deprecated.

 

In the next blog post I will discuss the changes to the memory manager DMVs in greater detail.  In future blog posts I will discuss the other changes in greater detail.

 

Published Tuesday, January 04, 2011 3:30 PM 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

 

Adam Machanic said:

Given that multipage allocations will now respect max server memory, will there be additional knobs to configure the amount of non-bpool VAS grant (e.g. for heavy users of SQLCLR components)? If this is not the case I'm concerned that we're going to see a lot more issues with memory pressure causing various caches to unload.

January 4, 2011 7:36 PM
 

Paul White said:

You say that 32-bit AWE has been deprecated in Denali...but Books Online has that feature deprecated in 2008 R2, and removed entirely from Denali (http://msdn.microsoft.com/en-us/library/ms190731.aspx).

The "Setting Server Configuration Options" page (http://msdn.microsoft.com/en-us/library/ms189631(v=SQL.110).aspx) retains a (broken) link to 'awe enabled', but I am assuming the page just hasn't been fully updated for Denali yet.

Now that we cannot reduce VAS pressure by windowing the data cache, it seems 32-bit Denali will be entirely unworkable for SQLCLR use (whereas previous 32-bit versions were merely ill-suited to it).  I'm not too sad about that.

With 32-bit Denali limited to 2GB memory by default (up to 3GB with the /3GB and /USERVA switches, up to 4GB in a [problematic] WOW64 configuration), it will be fascinating to see if an 'Enterprise' version is offered :)

Nice to see the database engine getting some (overdue) love in Denali though.  Looking forward to future posts.

Paul

January 4, 2011 8:52 PM
 

SQLOS Team said:

Paul, you are correct AWE has been removed from Denali.  Sorry for the confusion.  The Server Configuration Options page you linked to is referring to SQL Server 2008 R2.  It will be updated for Denali.

January 5, 2011 2:48 PM
 

Adam Machanic said:

How about my question?? :-)

January 5, 2011 4:54 PM
 

SQLOS Team said:

Adam, Denali will still have the -g startup parameter.  On 32-bit versions of Denali if SQLCLR allocates a lot of memory it can cause caches to shrink.

January 5, 2011 8:13 PM
 

Adam Machanic said:

I am referring to 64-bit SQL Server. In SQL Server 2005/2008 I set max server memory a bit lower on servers that make heavy use of SQLCLR, in order to leave some room for its memory allocations. How will I control this in Denali?

January 5, 2011 9:44 PM
 

Glenn Berry said:

In previous versions of SQL Server, only the buffer pool was affected by the Max Server Memory setting. If you had other things running, such as CLR or iFTS, it was standard practice to set Max Server Memory lower to allow for them (without starving the OS for memory).

Are other Engine components going to be included in the Max Server Memory setting in Denali?

What about other SQL Server components, like RS, IS, and AS?

Or, is this just related to the allocation size in the buffer pool?

January 7, 2011 5:32 PM
 

SQLOS Team said:

Glenn, you no longer need to leave so much overhead since CLR and multi-page allocations are part of max server memory. This only applies to sqlserver.exe. We plan to provide more details about that in subsequent posts, and an upcoming MCM webcast.

- Guy

January 7, 2011 6:12 PM
 

SQLOS Team said:

To clarify that a bit further.. max server memory limits all memory committed by SQL Server. Leave enough room for OS and SQL worker thread stacks (2MB on x64)

- Guy

January 7, 2011 6:24 PM
 

SQLOS Team said:

Adam, there are no new knobs in Denali for fine tuning SQLCLR memory allocations.  However, in Denali we allow multipage allocations to temporarily exceed the 'max server memory' configuration value.  This way components like SQLCLR should continue to work as they did in previous versions.  When Resource Monitor detects that 'max server memory' has been exceeded it will trim other caches to bring the server memory usage below the 'max server memory' setting.  

January 7, 2011 8:46 PM
 

Aaron Bertrand said:

In a previous post about changed system objects in Denali , I talked about the changes to memory-related

January 8, 2011 1:44 PM
 

Adam Machanic said:

So just to clarify, let's say that we have a system with max server memory configured for 16 GB, and we have a 14 GB buffer cache and a 2 GB procedure cache, and then a few CLR procs spin up and allocate 1 GB in that region (does it have a name in this new scheme?)... which of the other caches will be trimmed first? If I'm getting too detailed, perhaps another blog post is in order? :-)

January 10, 2011 11:02 PM
 

Linchi Shea said:

> All memory allocations by SQL Server components

Perhaps it would add clarity to identify what 'SQL Server components' precisely include in the above sentence. It may be self-evident to the SQLOS team, but may not be so to the rest of us. From your later comments, this appears to apply only to whatever inside sqlservr.exe, no more and no less. Is that correct?

January 13, 2011 11:31 AM
 

SQLOS Team said:

Linchi, yes sqlservr.exe.

Adam, good questions, we'll get to more detail.

- Guy

January 17, 2011 3:57 PM
 

Aaron Bertrand said:

Denali is coming, whether you like it or not. You may not be an early adopter and you may not have plans

June 27, 2011 8:50 AM

Leave a Comment

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