THE SQL Server Blog Spot on the Web

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

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Understanding the VAS Reservation (aka MemToLeave) in SQL Server

For a long time now I've had a Word Document I was working on to cover the subject of the VAS Reservation (aka MemToLeave) in SQL Server.  I've been quite busy this year, so I never got around to completing it, and I wasn't sure of where I wanted to publish it, on my blog or as an article submission somewhere else.  Today an article on SQL Server Central titled SQL Server Memory Configuration, Determining MemToLeave Settings was published that has incorrect information and fails to adequately cover the subject.  I've left a comment on the article, but the subject of VAS Reservation in SQL Server is to vast to cover in the comments on a article that are unlikely to be read by someone who has a problem with VAS in their SQL Server instance.

To start off with, MemToLeave is a moniker, or even better put a misnomer.  It was named MemToLeave based on the way that SQL Server allocates memory when it starts up, but it lead to confusion over the idea that it was memory that SQL Server wouldn't allocate.  The appropriate terminology for what is commonly known as the MemToLeave is the VAS Reservation.  This area of memory in SQL Server is frequently the source of many questions online that relate to Out of Memory Exceptions inside of SQL Server, and it is one of the most misunderstood portions of SQL Server memory.  Before getting into the internals of SQL Server memory, lets first take a look at Virtual Address Space in general, and the differences between 32 bit and 64 bit servers.

Differences in VAS Sizing 32bit vs 64bit

Servers that are x86 or 32 bit have a VAS region that is 2^32 in size or roughly speaking 4 GB.  This is by default split 50/50 into kernel mode and user mode VAS regions.  Applications like SQL Server, run inside the user mode VAS region (this article will forgo discussions on AWE and PAE which can be used in conjunction with one another to allow the SQL Server Buffer Cache to utilize memory beyond 4GB), which is why non-AWE instances of SQL Server max out their buffer pool size around 1.6GB for a server with 4GB of memory installed.  To work around this limitation, the /3GB and /USERVA boot.ini switches can be used to change the user/kernel mode proportions from 50/50 to 75/25 (/3GB) or a user defined proportion (/USERVA) to allow SQL Server to address more memory for its buffer pool.

Servers that are x64/IA64 or 64 bit have a VAS region that is 2^64 in size or roughly speaking 16 trillion GB (I am sure there is a proper name for this size, but I don't know it off hand, lets just call it a ridiculously large number for today's memory needs).  Since this is well beyond the physical capabilities for memory, the user mode and kernel mode VAS are capped at 8TB each for x64 based 64 bit servers (IA64 has a 7TB user mode cap).  This expanded VAS changes the dynamic of how applications such as SQL Server allocate memory.

Startup Allocation of Memory

When SQL Server starts up, the first memory item that is reserved by the SQLOS is the VAS Reservation memory area.  This is a special contiguous memory area that is separate completely from the Bpool, and is for use by external consumers such as Extended Procedures, COM objects (OLE Automation calls), Linked Servers, OLEDB providers, SQL CLR, and is also used for allocations larger than 8KB (>8KB), also known as multi-page allocations, by the database engine (extemely large and complex query plans).  It has a calculated fixed size that is determined at startup based on the following formula:

(MaxWorkerThreads * StackSize) + DefautReservationSize

The MaxWorkerThreads default on SQL Server 7.0 is 128 and on SQL Server 2000 it is 255.  However, on SQL Server 2005 and 2008 it is dynamic and depends on the number of schedulers (processors) you have configured in the SQL Server.  The formula for the number of worker threads is:

((NumberOfSchedulers - 4) * 8) + BaseThreadCount

For 32 bit SQL Servers the BaseThreadCount is 256. For 64 bit SQL Servers the BaseThreadCount is doubled to 512.

Note: On SQL Server 2005 and 2008, if the number of schedulers is less than four, then the BaseThreadCount is the default value for the MaxWorkerThreads; 256 for 32 bit and 512 for 64 bit servers respectively.  You can also query this information from the system:

select max_workers_count
from sys.dm_os_sys_info

For a 32 bit SQL Server the default StackSize is 512KB so to determine the memory allocation to the VAS Reservation for a 2 processor 32 bit SQL Server the calculation would be (256*512KB) + 256MB or 384MB of space. 

For a 64 bit SQL Server the default StackSize is 2048KB or 2MB, so for the same server only 64 bit, it would be (512*2048KB) + 256MB or 1280MB.  Keep in mind that only 256MB of this space is available for use by external objects both of these scenarios since the worker thread memory reservation is used to manage the worker threads in SQL Server.

Once this calculation and allocation has been completed, the next memory space setup by the SQL Server is the BPool or buffer pool.  The buffer pool size is determined by a number of factors, beginning with whether the server has AWE enabled on not.  If AWE is not enabled on the SQL Server, then the buffer pool is sized based on the remaining memory in the user address space minus the VAS Reservation allocation that has been previously calculated.  On 32 bit SQL Servers, the VAS is 4GB in size, and by default is split 50/50 into user address space and kernel address space, with 2GB assigned to each.  By using the /3GB or /USERVA startup switches in the Windows boot.ini file, this default configuration can be overridden to provide 3GB of VAS to the user address space, leaving 1GB for the kernel address space.

Once the user address space size has been determined by the SQL Server, the previously calculate VAS Reservation reservation is subtracted from it.  Then the resulting value is compared to the amount of available physical memory on the SQL Server, and the lower of the two numbers is the maximum available space for the buffer pool size.  This is then compared to the max server memory setting and the lower of the two values is then the maximum size of the buffer pool.  For a SQL Server with 1GB of Physical RAM and the default configuration of the Operating System and SQL Server options, this would equate to a maximum buffer pool size of 1GB based on the following calculations:

2048MB (2GB) user VAS - 384MB = 1664MB available VAS

1024MB (1GB) Physical Memory

Since the smaller of the two is the physical memory size, then this is the buffer pool size.  However, on a 2GB of Physical Memory server, the maximum size of the buffer pool would be 1664MB since the available user VAS is the smaller amount.  This is why adding the /3GB switch is often done in the boot.ini, which would change the calculation to be:

3072MB (3GB) user VAS - 384MB = 2688MB available VAS

4096MB (4GB) Physical Memory

Here the maximum size of the buffer pool is going to be 2688MB.  If however AWE is enabled on the SQL Server, then the maximum size of the buffer pool is the upper limit of the Physical RAM on the SQL Server or the max server memory setting, whichever is smaller. 

On 64 bit SQL Servers, the VAS Reservation at startup does not occur due to the size of VAS available on 64 bit systems.  ( Thank you Linchi Shea and Bob Ward for correcting me on this.) 

After reserving and allocating the BPool memory area, SQL Server will then release the VAS Reservation area it reserved in the first step in order to have this region of address space available for objects which utilize VAS Reservation memory.

Understanding allocations from the VAS Reservation

The VAS Reservation itself isn't necessarily the limit of VAS available to SQL Server after the BPool size is calculated.  Any remaining user mode VAS can be used by SQL Server for VAS/multi-page allocators inside of SQL Server.  For this reason, the available VAS region inside SQL Server can be larger than the VAS Reservation computed and reserved at startup.  Consider for example the 32 bit server with 1GB of physical memory in the previous example.  The VAS reservation + the Buffer Pool size of 1GB is still short of the 2GB user mode VAS on the server.  When this occurs, the balance of user mode VAS is still useable for multi-page allocations by SQL Server, though it can lead to over commitment of memory which can itself be problematic.  For 64 bit servers, the available VAS is always multiple terabytes in size, so the limit to memory allocation is the limit of uncommited physical memory on the server.

One of the biggest problems associated with the VAS Reservation on 32 bit servers is fragmentation, which occurs when blocks of memory are allocated from the VAS Reservation and then released later, leaving smaller contiguous blocks of memory available.  When SQL Server allocates memory from the VAS Reservation it requires contiguous blocks of memory to make the allocation.  This means that you can have 100MB of memory available but if the largest contiguous block is only 4MB and a 8MB allocation is required the request will fail.  VAS fragmentation is one of the most difficult scenarios to troubleshoot because there is no means to defragment the VAS space, and it often requires looking at the VAS consumers in your workload to determine why the fragmentation is occurring.

Troubleshooting VAS and Out of Memory Exceptions

How you actually go about troubleshooting Out of Memory (OOM) problems related to VAS in SQL Server depends on a number of factors including what version of SQL Server you are running, what architecture it is running on, and what potential VAS consumers you have in your system.  By far the worst problems troubleshooting VAS problems occur on SQL Server 2000, where the ability to look at VAS information is very limited.  SQL Server 2005 and 2008 offer much more in the way of troubleshooting problems with the Dynamic Management Views.  For SQL Server 2000, the only way to get information on VAS inside SQL Server is to use the vmstat executable.  This tool is available as a part of the PSS Labs download from Microsoft in Lab #3:

http://www.microsoft.com/downloads/details.aspx?FamilyId=AEC18337-887F-4EC6-A858-81F84DE8082F&displaylang=en

The output from the tool is a table that shows information similar to:

TYPE       MINIMUM    MAXIMUM    AVERAGE  BLK COUNT
~~~~       ~~~~~~~    ~~~~~~~    ~~~~~~~  ~~~~~~~~~
Commit        4096  769654784    2141981        833
Reserve       8192    4128768     387799        121
Free          4096  166080512    1964121        161
Private       4096  769654784    4089322        438
Mapped        4096    1019904     131891         40
Image         4096    7598080      73099        476

The free Type and Maximum column show the largest available contiguous block of VAS available to SQL Server.  This information is in bytes.  In SQL Server 2005 and 2008, this same information is available in the DMV's.  Christian Bolton offers a great VAS Summary query on his blog post SQL Server memtoleave, VAS and 64-bit - Christian Bolton's SQL Server Blog:

WITH VASummary(Size,Reserved,Free) AS
(SELECT
   
Size = VaDump.Size,
   
Reserved SUM(CASE(CONVERT(INT, VaDump.Base)^0)
   
WHEN 0 THEN 0 ELSE 1 END),
   
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
   
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
   
SELECT  CONVERT(VARBINARY, SUM(region_size_in_bytes))
   
AS Size, region_allocation_base_address AS Base
   
FROM sys.dm_os_virtual_address_dump 
   
WHERE region_allocation_base_address <> 0x0
   
GROUP BY region_allocation_base_address 
 
UNION  
    SELECT
CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
   
FROM sys.dm_os_virtual_address_dump
   
WHERE region_allocation_base_address  = 0x0
)
AS VaDump
GROUP BY Size)


SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail mem, KB] ,CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB] 
FROM VASummary 
WHERE Free <> 0

Further when troubleshooting what is consuming memory in SQL Server 2000, the best you can get is the output from DBCC MEMORYSTATUS, which is very limited in SQL Server 2000 when compared to the same output from SQL Server 2005 and 2008.  However, in SQL Server 2005 and 2008, the sys.dm_os_memory_clerks DMV can be used to look at where VAS allocations are being used in much more detail.

SELECT type, virtual_memory_committed_kb, multi_pages_kb
FROM sys.dm_os_memory_clerks
WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0

From this, you can begin to look at the clerks that are the highest consumers and begin troubleshooting form there.  Understanding the consumers of VAS in your environment is one of the keys to troubleshooting VAS allocation issues.  As previously covered, common consumers of VAS in SQL are Extended Procedures, COM objects (OLE Automation calls), Linked Servers, OLEDB providers, SQL CLR, and is also used for allocations larger than 8KB (>8KB), also known as multi-page allocations, by the database engine.  Other consumers can include third party backup tools (LiteSpeed for example), and connections that have a network packet size > 8000 bytes (this puts them into multi-page allocation). 

When any one of combination of many of these exists in your environment you need to look beyond the VAS size being the issue and determine if a problem in design, code or utilization is actually the problem.  For example, backups that use a large maxtransfersize or buffer size, or a network packet size > 8000 bytes can become heavy VAS consumers.  You may need to balance any performance increase realized by these tools in their default configuration with the need to resize your VAS allocation.  If you utilize OLE Automation to call COM objects, you need to make sure that for every call to sp_OACreate, you have a corresponding call to sp_OADestroy, or you will leak VAS from the open pointers.

If you are on a 32 bit SQL Server, one thing that can be tried is to bump the size of the Base VAS Reservation so that more memory is reserved up front.  To increase the available size of space in the MemToLeave area, the -g startup parameter can be used to specify a value larger than 256MB.  Values less than or equal to 256MB specified with the -g startup parameter are ignored by SQL Server and the default 256MB allocation is used.  This can not be used on 64 bit servers since the VAS Reservation calculations do not occur on 64 bit servers. 

The problem with using this startup parameter on a 32 bit server is that it reduces the size of the buffer pool, especially on servers that don't utilize AWE, so there is a trade off that happens.  For this reason, any use of this startup parameter should be carefully considered, and then slowly increased if necessary.  If the VAS problem is with fragmentation or worse a true memory leak from a custom Extended Stored procedure, increasing the base allocation may not resolve the problem, it will only make the time to encounter the error longer, so it isn't a catch all fix to the problem.  If your VAS problems are related to overuse of VAS then adding this parameter may solve the problem, you have to test to find out, but one note is that changing this value requires a server restart to take effect.

If you are on a 64 bit SQL Server, the only real solution to the problem that I have found to date is to back down the size of the max server memory setting, which leaves additional physical memory available to be used for multi-page allocations and VAS requirements.  This essentially is doing the same thing that bumping the VAS reservation with the -g startup parameter does, by removing memory from the BPool allocation and leaving it available for VAS allocations.

I hope this helps those who have had questions on how memory is determined during the startup of SQL Server.

References:

Guru Guide to SQL Server Architecture - Chapter 11

Memory-mapped files and SQL Server - Ken Henderson's WebLog

Be careful with xproc memory allocation - Ken Henderson's WebLog

Inside SQL Server 2000's Memory Management Facilities

SQL Server Memory - Microsoft SQL Server Team

SQLOS's memory manager and SQL Server's Buffer Pool

SQL Server memtoleave, VAS and 64-bit - Christian Bolton's SQL Server Blog

Published Tuesday, July 07, 2009 2:41 AM by Jonathan Kehayias

Comments

 

Grumpy Old DBA said:

very nice. I'm so glad we're moving away from x32, so much becomes easier in x64, although you do tend to get a new set of problems to face.

July 7, 2009 4:21 AM
 

C. Villalobos said:

As much as I can stand SQL Server, I always appreciate your posts because they go above and beyond the default Microsoft explainations and are useful to the every-man sysadmin. Keep it strong.

July 7, 2009 7:53 AM
 

Linchi Shea said:

This upfront reservation of virtual address space does not apply to 64 bit. It only applies to 32 bit.

> For 64 bit servers, the balance of VAS is always multiple terabytes in size, so the limit of VAS available is the limit of uncommited physical memory on the server.

Does the balance of VAS have anything to do with the amount of physical memory on the server at all?

July 7, 2009 8:55 AM
 

Jonathan Kehayias said:

Linchi,

I am not sure that I follow what you are asking.  To my best knowledge the upfront reservation calculations still occur in 64 bit, but it doesn't have an impact like it does in a 32 bit server because of the 8TB VAS.  There is always available VAS, but there may not be available physical memory to back it on a 64 bit server.  It is similar to a 32 bit server with only 1GB of physical RAM.  The VAS Reservation is calculated and then the buffer pool is sized, resulting in a 1GB max size for the buffer pool, and then the VAS instead of being 384MB is 1GB, the balance of VAS available after the buffer pool max size is set.  If you have a machine with 1GB of memory on it, you can see this by querying the DMV's.  If you query a 64 bit machine, you will always get a 7TB+ number for available VAS.

July 7, 2009 9:37 AM
 

Linchi Shea said:

I don't believe 64-bit SQL Server acctually makes that reservation any more.

I was having trouble undersanding "the limit of VAS available is the limit of uncommited physical memory on the server". That seems to me to be a limit of physical mmeory instead of a limit of VAS.

July 7, 2009 10:23 AM
 

Jonathan Kehayias said:

Linchi,

You are correct, about 64 bit not even performing the calculations.  This was confirmed by Bob Ward from CSS so I will update the information and correct that.  Thanks for pointing that out.

I guess for the last part, the wording might not be working out to good, so I'll try and rethink how I put that to see if it makes more sense.  The limitation with 64 bit that I bump into isn't that there isn't enough available VAS, but that there isn't available physical memory to allocate, commonly corrected by backing down the size of the max server memory so that the buffer pool is smaller leaving available memory on the server.  Perhaps you could suggest a better wording for that.

July 7, 2009 10:37 AM
 

Linchi Shea said:

Personally, I'd take 64 bit out of any discussion on MemToLeave except pointing out that it's no longer a practical issue with 64 bit.

July 7, 2009 11:09 AM
 

Andrew Kelly said:

>>>Personally, I'd take 64 bit out of any discussion on MemToLeave except pointing out that it's no longer a practical issue with 64 bit.<<

Absolutely. In 64 bit there is no memory set aside like in 32bit so there is nothign to leave :)

July 7, 2009 11:35 AM
 

Jonathan Kehayias said:

Linchi,

It has been adjusted above.  64 bit servers do still encounter OOM issues that are related to the calls requiring VAS allocations, so I don't think it should be left out completely.  I guess it could be separated into different post, on dealing with the 32bit VAS Reservation (MemToLeave) and another dealing with VAS allocation and OOM problems, which would remove any confusion that could be caused.  I appreciate your feedback and help on this, its been a learning experience for me.

July 7, 2009 11:40 AM
 

Adam Machanic said:

In conversations I've had with various members of the SQL Server team (especially in the SQLCLR area) they have referred to the remainder of physical memory in a 64-bit system as mem-to-leave (or MemToLeave, depending on how you like to write it--I prefer the former). While the mechanism may not be exactly the same, the results are similar and at least sometimes the memory areas are referred to using the same verbiage.

July 7, 2009 11:52 AM
 

Linchi Shea said:

Whatever the remnants of that verbiage may be, I guess the point is that there is nothing interesting to discuss when it comes to 'MemToLeave' on 64 bit. There were unique MemToLeave issues because of the 32-bit architecture, there is none with 64 bit. You'll still have mmeory allocation issues, but they are not unique to VAS Resevation/MemToLeave.

July 7, 2009 12:12 PM
 

Justin M said:

This was a great article, and is a great explanation of the VAS Reservation. Thank you very much for taking the time to write this. Since -g only applies to 32-bit editions of SQL Server, I wondered how this could be adjusted in 64-bit editions. Then I hit refresh and I see that you made some corrections that cleared things up. Thanks again.

July 7, 2009 2:41 PM
 

Kyle Van Andel said:

Thanks Jonathan, that was an excellent post, and it answered a few questions I had in a recent forum post. Appreciate your insight.

July 7, 2009 4:07 PM
 

PJ said:

Great post. Summarizes the memory architecture brilliantly in an understandable article.

Many Thanks

July 13, 2009 8:22 AM
 

Vic said:

Hey Jonathan, great post! keep up the good work.

July 13, 2009 5:08 PM
 

Al Jones said:

Thanks for the great post - This looks like the answer we've been having for past year.  

I've read where on setting up Provider - does the "AllowInProcess" switch being yes brings the memory allocation within the SQL reserved memory?

September 2, 2009 10:50 AM
 

Maurice De Vidts said:

Thanks for the write-up!

First one I have read that ties it all together nicely.

I had also been wondering if the MemToLeave reservation was actually being made or the code removed in 64-bit systems, as the only write-ups I found about this mentioned it was moot. I gleaned from all of this that in the 64-bit environment vas is vas is vas, and the max memory setting should be cut back a bit below physical in order to minimize paging and leave enough physical ram availble for a persistent vas mapping.

September 24, 2009 3:20 PM
 

Maurice De Vidts said:

I would also still use the memToLeave formula in a 64-bit system to understand how much to cut back the maximum memory setting, it is a good starting point.

September 24, 2009 3:25 PM
 

Jonathan Kehayias said:

Bob Ward at Microsoft posted a blog post regarding 64 bit and no MemtoLeave calculation on the following post:

http://blogs.msdn.com/psssql/archive/2009/08/26/come-on-64bit-so-we-can-leave-the-mem.aspx

September 24, 2009 5:57 PM
 

Ekrem Önsoy said:

Hi Jonathan,

You say "((NumberOfSchedulers - 4) * 8) + BaseThreadCount".

There's are ITA servers in my environment and when I run "select max_workers_count from sys.dm_os_sys_info" against one of them it returns 576. There are 8 cores on this server and all of those cores are used by SQL Server.

Are you sure that formula is correct above? If I use a formula like "((NumberOfSchedulers) * 8) + BaseThreadCount" then it gives me the correct answer.

September 25, 2009 4:04 AM
 

Jonathan Kehayias said:

Ekrem,

There is actually a table that makes looking up max worker threads by common configurations in the Books Online:

http://msdn.microsoft.com/en-us/library/ms187024.aspx

After doing some research and mathematics, it appears that there is a different formula for 64 bit servers than 32 bit servers. If you look at the "((NumberOfSchedulers - 4) * 8) + BaseThreadCount" formula it will work in every case for 32 bit.  

((NumberOfSchedulers - 4) * 8) + BaseThreadCount

((4-4)*8)+256 =  (0*8)+256 =   0+256 = 256 threads

((8-4)*8)+256 =  (4*8)+256 =  32+256 = 288 threads

((16-4)*8)+256 = (12*8)+256 =  96+256 = 352 threads

((32-4)*8)+256 = (28*8)+256 = 224+256 = 480 threads

For a 64 bit server, the multiplier is doubled from 8 to 16, so the formula should be:

((NumberOfSchedulers - 4) * 16) + BaseThreadCount

((4-4)*16)+512 =  (0*16)+512 =   0+512 = 512 threads

((8-4)*16)+512 =  (4*16)+512 =  64+512 = 576 threads

((16-4)*16)+512 = (12*16)+512 = 192+512 = 714 threads

((32-4)*16)+512 = (28*16)+512 = 448+512 = 960 threads

I didn't actually notice this until you posted your comment, but that should clarify the information and I'll put up a correction in the blog post in a bit.

Thanks for pointing that out.

September 25, 2009 9:28 AM
 

Paul Storm said:

Hi Jonathan,

Thanks for the post. Enlightening and, as usual, cogently expressed.

In the VASummary query I'm struggling to understand the purpose of the OR ("^") operator when summing "VaDump.Base" to produce the Reserved and Free values. It doesn't have any effect, does it?

Thanks,

Paul

October 21, 2010 2:01 AM
Anonymous comments are disabled

This Blog

Syndication

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