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.

SQL Server Memory Basics

I will give a brief account of how SQL Server memory is organized (in a nutshell). There is much more to it under the covers! 🙂

SQL Server Memory space is organized in the following manner:

 

Memory


Each instance of Microsoft SQL Server 2000 has an address space with two main components, each of which has several subcomponents:

There are two main areas of memory within SQL Server’s address space, the buffer pool (BPool) and a second memory pool sometimes called the “MemToLeave” area. The contents of the SQL Server buffer pool include cached table data, workspace memory used during query execution for in-memory sorts or hashes, most cached stored procedure and query plans, memory for locks and other internal structures, and the majority of other miscellaneous memory needs of the SQL Server. The Bpool handles memory allocations for 8K and below. All allocation requirements above 8K is satisfied from the MTL region.

Executable code

  1. The number and size of the executable files and dynamic link libraries (DLLs) used by an instance of SQL Server varies over time. In addition to the executable files and DLLs used by Open Data Services, the SQL Server engine, and server Net-Libraries, the following components load in their own DLLs, and these DLLs can allocate memory themselves:
  2. Distributed queries can load an OLE DB Provider DLL on the server running the instance of SQL Server.
  3. Extended stored procedures are implemented as DLLs that are loaded into the address space of the instance of SQL Server.
  4. The OLE Automation system stored procedures are used to create instances of OLE Automation objects. Each class of OLE Automation object loads its own code into the address space of the instance of SQL Server.

Memory pool

The memory pool is the main unit of memory for an instance of SQL Server. Almost all data structures that use memory in an instance of SQL Server are allocated in the memory pool. The main types of objects allocated in the memory pool are:

System-level data structures

These are data structures that hold data global to the instance, such as database descriptors and the lock table.

Buffer cache

This is the pool of buffer pages into which data pages are read.

Procedure cache

This is a pool of pages containing the execution plans for all Transact-SQL statements currently executing in the instance.

Log caches

Each log has a cache of buffer pages used to read and write log pages. The log caches are managed separately from the buffer cache to reduce the synchronization between log and data buffers. This results in fast, robust code.

Connection context

Each connection has a set of data structures that record the current state of the connection. These data structures hold items such as parameter values for queries and stored procedures, cursor positioning information, and tables currently being referenced.

The high level memory architecture remains the same in SQL Server 2005 and 2008. The difference is that we introduced a change called Memory Clerks which were responsible for memory allocation requests for requirements for a particular object/cache store. The allocators changed to Single Page Allocator (any allocation required below 8KB) and Multi Page Allocator (any allocation above 8KB). With the help of SPA and MPA, we are now able to track Buffer Pool and non-Buffer Pool memory requests in a more structured manner when components within SQL Server are requesting for memory. However, if your DLL/CLR code hosted within SQL Server memory is calling VirtualAlloc() directly, then tracking those requires extensive data collection and analysis.

Useful Articles

How to track SQL Server 2000 memory usage using DBCC Memorystatus

How to track SQL Server 2005 memory usage using DBCC Memorystatus