SQL Articles

On a 32-bit system there is a maximum of 4GB of virtual address space (VAS). This is made up of: 2GB for the kernel and 2GB user mode for the Buffer Pool minus the Mem-To-Leave amount. Not all the memory is committed at start-up. The Mem-To-Leave is an area in the user mode portion of the 4GB address space that is reserved only at start-up and then released after start-up, so that external items to SQL Server can have contiguous blocks of memory to draw upon. This is typically extended stored procedures, linked servers, SQLCLR etc. However, this area of memory is not guaranteed to be contiguous. The Mem-To-Leave amount is calculated using the following formula:

(Stack Size x Max Worker Threads) + -g startup parameter.

The default setting for Max Worker Threads (in 2005/2008) is 0 which lets SQL Server allocate this value based on the number of CPU’s. In SQL 2000 it was set at 255.

Default thread stack size is 512k (512k on x86 and 2Mb on x64)

Run the following query to confirm both these running values…

SELECT max_workers_count
, stack_size_in_bytes / 1024 AS stack_size_kb
FROM sys.dm_os_sys_info ;

The default setting for –g parameter is 256MB. If it is set to more than 50% of VAS, it is ignored. On a 32-bit dual proc system, the calculation would be…

Mem-To-Leave = StackSize x MaxWorkerThreads + 256MB
= (512k x 256) + 256MB
= 128MB + 256MB
= 384MB

Now, calculate how much physical memory SQL Server has…

2048MB VAS – 384MB Mem-To-Leave = 1664MB

This will explain why on a 32-bit SQL Server with 4GB RAM that SQL will max out at approx 1.6GB. If there is less than 2GB physical memory on the server then SQL Server will reserve that amount – not more than it actually has. The buffer pool cannot reserve more physical memory than actually exists on the server. The VAS for the kernel is still 2GB.

With /3GB or /USERVA the user VAS is increased to 3GB (or a user defined amount with USERVA) and the kernel is restricted to 1GB. This is done in the boot.ini file or in Windows 2008 using BCDEdit.exe as there is no boot.ini in Windows 2008. Using the /3GB switch will increase memory to things like the procedure cache. This doesn’t change the Mem-To-Leave calculation but SQL Server gets 3GB minus Mem-To-Leave. This can cause problems for applications that require memory other than SQL Server which they would acquire from the Mem-To-Leave region if there isn’t enough free contiguous memory available. An example of this was Red-Gate SQL Backup wouldn’t run on one of our systems with the /3GB switch enabled but once it was removed – no problems. There is no way to defragment the Mem-To-Leave region.

The maximum virtual address space on a 64-bit Windows server it is 8TB. This is a Windows limitation as the actual amount is 16EB. However, no Windows server supports more than 2TB of RAM. Note that 64-bit does not have a Mem-To-Leave as there is no need because of the much increased VAS. Again, the kernel addressable space is also 8TB.

The following query will show the amount of physical memory on the server and the maximum virtual address space…

SELECT physical_memory_in_bytes / 1024 / 1024 AS physical_memory_mb
, virtual_memory_in_bytes / 1024 /1024 AS max_possible_virtual_memory_mb
FROM sys.dm_os_sys_info ;

The CTE query below will show the size of the Mem-To-Leave VAS and the largest available block in the Mem-To-Leave. If the largest block free is less than 4MB the server is considered to be experiencing VAS memory pressure.

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(Size * Free) / 1024 AS total_available_mem_kb
, CAST(MAX(Size) AS INT) / 1024 AS max_free_size_kb
FROM VASummary
WHERE Free <> 0 ;


This query below shows memory usage. It also shows available_virtual_memory_mb to show you how much virtual memory SQL Server has left (if you suspect you are getting low). SQL 2008 only

SELECT total_physical_memory_kb / 1024 AS total_physical_memory_mb
, available_physical_memory_kb / 1024 AS available_physical_memory_mb
, system_cache_kb / 1024 AS system_cache_mb
, ( kernel_paged_pool_kb + kernel_nonpaged_pool_kb ) / 1024 AS kernel_pool_mb
, total_page_file_kb / 1024 AS total_virtual_memory_mb
, available_page_file_kb / 1024 AS available_virtual_memory_mb
, system_memory_state_desc
FROM sys.dm_os_sys_memory

Total memory used by SQL Server – this is for SQL2008 only

SELECT (SUM(virtual_address_space_committed_kb)
+ SUM(locked_page_allocations_kb)
+ SUM(multi_pages_kb)) / 1024 AS total_sql_memory_usage_mb
FROM sys.dm_os_memory_nodes ;

On systems with more than 4GB RAM, PAE gives access to the additional physical memory. PAE is enabled in the boot.ini file and it has nothing to do with AWE or /3GB. Individually an application cannot access all this extra memory given by PAE but in totality they can. AWE is a Windows API mechanism that allows an application like SQL Server to access the additional memory over the 4GB VAS, provided by PAE. Memory allocated with AWE is not part of the processes working set so it cannot be paged out. To enable AWE you must grant the “Lock pages in memory” priviledge to the SQL Server service account. This priviledge is granted automatically if the service account is running under the local system account. Also, using sp_configure set “AWE Enabled” to 1. On 64-bit systems the AWE mechanism is still used however it is known as Locked Pages. The AWE setting in sp_configure is ignored on 64-bit systems.

The following query shows how much memory is allocated through AWE

SELECT SUM(awe_allocated_kb) / 1024 AS awe_allocated_mb
FROM sys.dm_os_memory_clerks ;

Check SQL Server errorlog to see a message that AWE can be used.

AWE extends database pages in the buffer pool only. Other memory objects in 32-bit SQL Server like the Procedure Cache cannot use AWE memory. Technically, single pages allocated for the Procedure Cache come from the buffer pool. This is why the Mem-To-Leave area is still important on 32-bit systems with more than 4GB RAM. Virtualalloc() is the routine that SQL Server uses for allocating memory. For your standard database page that is 8k, single page allocator is used and this comes from the buffer pool. For multi page allocations, that is greater than 8k, SQL uses the multi page allocator. These pages are allocated from outside the buffer pool – the mem-to-leave area.

The following query show the top 10 consumers of buffer pool memory
SELECT TOP 10
type
, SUM(single_pages_kb) AS buffer_pool_consumers_kb
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC ;

The following shows how much memory is allocated through the multi-page allocator. Remember that the multi-page allocator allocates memory from outside the buffer pool.
SELECT type
, SUM(multi_pages_kb)
FROM sys.dm_os_memory_clerks
WHERE multi_pages_kb <> 0
GROUP BY type ;

The amount of reserved memory on a 32-bit system without AWE is the visible amount that can actually be accessed. The visible amount also equals the Target amount, the amount of memory SQL Server would like to use. The committed amount will increase as physical memory is accessed. With AWE enabled, the target amount will increase to include the amount of AWE memory. The visible counter will stay the same.Visible always equals target on 64-bit. The following query shows these amounts.

SELECT ( bpool_committed * 8192 ) / 1024 / 1024 AS buffer_pool_committed_mb
, ( CAST(bpool_commit_target AS BIGINT) * 8192 ) / 1024 / 1024 AS buffer_pool_target_mb
, ( bpool_visible * 8192 ) / 1024 / 1024 AS buffer_pool_visible
FROM sys.dm_os_sys_info ;

The following query will show all memory allocations. Look at the single_pages_kb column where the value is greater than 0 to see what processes are allocated memory from the buffer pool.

SELECT *
FROM sys.dm_os_memory_clerks
WHERE ( single_pages_kb > 0 )
OR ( multi_pages_kb > 0 )
OR ( virtual_memory_committed_kb > 0 ) ;

SQL Server myths…busted!
AWE API’s are not used on 64-bit SQL Server – FALSE (the “locked pages” uses the AWE API’s)
SQL Server never allocates more than “max server memory” – FALSE (virtual memory can be more)
PAE required for SQL Server 32-bit to use AWE on Windows 64-bit – FALSE (PAE overcomes the 32-bit limitation)
“Lock Pages” is required to avoid working set trim – FALSE (lock pages only locks buffer pool memory)
SQL Server allocates all of its memory at start-up – ONLY LARGE PAGES

Many thanks to Bob Ward at Microsoft for reviewing this article for accuracy.code