SQL Server memtoleave, VAS and 64-bit

Published 07 January 08 11:39 AM | Christian

GrumpyOldDBA started a nice thread here: http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/01/06/configuring-windows-2003-x64-for-sql-server.aspx on 64-bit which I began to reply to but it got big enough for a full post.

This is a great topic to start a dialog on, I'm a great supporter of x64 as well having worked through lots of problems with PAE and AWE on the 32-bit platform in the past.

One of the common issues we used to see was with "MemToLeave" being too small or fragmented.  "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.  In the cases I saw, these components were always extended stored procs or linked servers.  The reason we need memtoleave is because the virtual address space on 32-bit is only 2GB which would all be taken by the buffer pool if we let it.  I think the memtoleave value settled at about 340MB after a few service packs of SQL 2000 although it could still be 256MB or a dynamic value in between.  The point is that its only a few hundred MB so it not unusual to run out although you can increase it if you get desperate by using the -g startup parameter.

In SQL 2000 you have to run the VMSTAT utility to get an accurate view of MemToLeave which is quite intrusive but in SQL Server 2005 you can get the information from DMV's.  This script will show you the largest available block of virtual address space (memtoleave) outside the buffer pool:

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

Whats really interesting is comparing the results between 32-bit and 64-bit:

My 32-bit laptop with SQL Server 2005 (32-bit obvously) and 2GB RAM:
Total avail mem, KB
340680
max free size, KB
120016

A 64-bit server with SQL Server 2005 x64 with 8GB RAM:
Total avail mem, KB
8574741364
max free size, KB
6703778112

My laptop has memtoleave of 120MB and the server has over 6TB. Beautiful.

Its not entirely glorious as it sounds though because you can still run out of VAS even on 64-bit if you run low on physical memory.  This is why its always recommend to have max server memory set on 64-bit SQL Server to prevent SQL Server taking too much.

Regards,

Christian Bolton
Database Architect
http://coeo.com - The SQL Server Experts

 

 

 

Filed under: , ,

Comments

# GrumpyOldDBA said on January 8, 2008 09:19 AM:

The figures actually appear meaningless, the query reports 6.4Tb and 8.1TB for a x64 ent box, as the server only currently has 12Gb ram installed I'm not sure what to conclude.

# Christian said on January 8, 2008 10:32 AM:

The virtual address space is capped at 8TB on x64 and 7TB on IA64.  Comparing this to 2GB on x86 you start to see the justification for 64-bit.  The 6.4TB figure is the largest contiguous range of virtual address space in your SQL Server's address space which can be a factor sometimes because some applications need to allocated large contiguous blocks.

# TrackBack said on January 14, 2008 09:32 AM:
# Justin Langford's Blog said on January 25, 2008 12:28 PM:

I've worked with Christian Bolton for a number of years - and known him a few years longer! Since establishing