How to find the largest free block in the MTL

Often we need to find the largest free block when troubleshooting “MemToLeave” issues with SQL Server Memory. Since, this is related to virtual address space and we didn’t expose any method to query the process address space on SQL Server 2000, you would have to rely on a tool called VMSTAT to find out the largest free block. This tool is used by CSS to troubleshoot MTL issues. However in SQL Server 2005, we added a new DMV which would inform you the status of the virtual address space for SQL. The query below can be used to find the largest contiguous block on the system.

If you are getting the following error:

WARNING: Failed to reserve contiguous memory of Size= 2162688 (*This value is always in bytes)

If the query below is run during the problem period, then you will find one of the following conditions to be true:

1. The value returned by the query is larger than the value in the error (once you have done the necessary conversions) – This means that when the error occurred, the MemToLeave region of SQL Server was being used by some component due to which a current allocation request failed. This is due to a virtual address space crunch.

2. The value returned by the query is lower than the requested size – This means that you either have a virtual address space fragmentation issue or some component has used up all the available MemToLeave region. (A lot of my colleagues believe that MTL is a misnomer but I will continue to use these throughout the post as it makes referencing the memory region simpler.) In such a case, it would always be advisable to get a Microsoft CSS Engineer to setup data collection tools in place for the next problem occurrence and analyze the existing logs for any tell-tale signs.

Query (for 64-bit plaform)

SELECT convert(varchar,getdate(),120) as [Timestamp], max(region_size_in_bytes)/1024 [Total max contiguous block size in KB] from sys.dm_os_virtual_address_dump where region_state = 0x0000000000010000 --MEM_FREE

Query (for 32-bit plaform)

SELECT convert(varchar,getdate(),120) as [Timestamp], max (region_size_in_bytes)/1024 [Total max contiguous block size in KB] from sys.dm_os_virtual_address_dump where region_state = 0x00010000 -- MEM_FREE

For more information on this DMV, read about it on sys.dm_os_virtual_address_dump

NOTE: The above query is very very performance intensive and shouldn’t be run very frequently on a production system. On a 64-bit system, frequent use of this query can actually degrade performance severely due to the large virtual address space of 64-bit systems.