RE: SQL Server Memory Configuration, Determining MemToLeave Settings

One Orange Chip

Points: 26778

July 6, 2009 at 10:40 am

#1020499

I am not really sure where to begin with commenting on this article. It has incorrect information in it, and the code provided for looking at VAS comes from Christian Bolton's blog, but the article doesn't provide credit.

The coverage of MemToLeave doesn't adequately address what the VAS reservation is for, what/how/why it gets used, or how it is calculated. I can see this article being a reference point for people adding the -g startup parameter to solve every problem and then being referenced in forums posts when the -g doesn't solve the issue. If you are going to cover something as complex as the MemToLeave area in SQL Server, you had better do it correctly, which this article doesn't do.

Now suppose your environment uses a fair amount of managed code such as .NET CLR or perhaps utilises SQL Server Integration Services (SSIS) quite heavily. These technologies naturally require memory just as SQL Server does however one critical point to note is that the memory for these resources is allocated from outside of the SQL Server Buffer, in a portion of memory known as MemToLeave.

This is very wrong. SSIS is its own process, and doesn't use SQL Server Memory allocations from MemToLeave.

MemToLeave is virtual address space (VAS) that's left un-used when SQL Server starts so that external components called by SQL Server are saved some address space. So in order for these technologies, .NET CLR and SSIS, to operate efficiently you must ensure that they too have access to sufficient memory.
This is why it is often recommended that you explicitly set the maximum amount of memory that SQL Server uses, as opposed to allowing it to consume all that is available on your server.

MemToLeave is a VAS Reservation on 32bit SQL Server systems that is reserved for multipage and external allocations and the SQL Server processes, again not SSIS which is its own process. The max server memory settings have no affect on the VAS reservation at all, they only affect the size of the buffer pool. The VAS reservation is calculated at startup time and uses a base 256MB reservation + the calculated additional workspace size. The -g parameter only applies to 32 bit instances of SQL Server and it only increases the 256MB base to values higher than 256MB, values lower than 256 are ignored for the parameter. It shouldn't just be used without understanding the impacts it has, like decreasing the size of the buffer pool.

If you are unsure of how much additional memory to allocate to MemToLeave, then it is best to increase the volume assigned in smaller increments of say 250MB, until your original indicators are no longer being raised i.e. your application domain unload messages are no longer appearing.

This is dangerous information to offer since increasing the VAS reservation decreases the size of the buffer pool, and there is a limit to how much VAS there is on a 32 bit server. You can't keep increasing the value. Anything beyond a 128 MB increase (which would be -g384) should be carefully considered. You likely have a bigger problem if you are increasing the base beyond this size that needs to be corrected.

If you are going to cover a topic like this one, you need to cover it in detail, and be accurate in what you write.