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:
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:
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
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)
SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size, region_allocation_base_address AS Base
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
WHERE region_allocation_base_address = 0x0
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]
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
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.
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