Mapping Virtual Address Space in T-SQL

The Dynamic Management Views (DMVs) introduced in  SQL Server 2005 provide a comprehensive window into the state of the SQL engine and also the machine it is running on. This post will look at how the SQL Server process uses DMVs to keep track of its own Virtual Address Space (VAS).

Querying the sys.dm_os_virtual_address_dump DMV results a map of SQL Server VAS by allocation, effectively returning the same information as a VirtualQuery Windows API call.

Here's a query (tested on SQL Server 2008 R2) which reads the address space and interprets the region state, protection and type flags for each allocated/unallocated region of the SQL Server process VAS. I'm sure there are better ways to translate the protection flags (for example this version won't handle combinations of flags, should they be present, but is sufficient for the protection types I see in my memory map), suggestions are very welcome:

/* Map the process virtual address space by querying sys.dm_os_virtual_address_dump */
SELECT
region_base_address 'Base addr'
,region_size_in_bytes / 1024 size_kb
,case (region_state) when CONVERT(int, 0x1000) then 'COMMITTED'
when CONVERT(int, 0x2000) then 'RESERVED'
when CONVERT(int, 0x10000) then 'FREE'
end State
,case
when (region_current_protection = 0) then 'NONE'
when (region_current_protection = CONVERT(int, 0x104)) then 'READ/WRITE/GUARD'
when (region_current_protection ^ 1 = 0) then 'NO ACCESS'
when (region_current_protection ^ 2 = 0) then 'READ'
when (region_current_protection ^ 4 = 0) then 'READ/WRITE'
when (region_current_protection ^ 8 = 0) then 'WRITE/COPY'
when (region_current_protection ^ CONVERT(int, 0x20) = 0) then 'EXECUTE/READ'
when (region_current_protection ^ CONVERT(int, 0x40) = 0) then 'EXECUTE/READ/WRITE'
when (region_current_protection ^ CONVERT(int, 0x80) = 0) then 'EXECUTE/WRITE/COPY'
end Protection
,case (region_type)
when 0 then 'FREE'
when CONVERT(int, 0x20000) then 'PRIVATE'
when CONVERT(int, 0x40000) then 'MAPPED'
when CONVERT(int, 0x1000000) then 'IMAGE' end 'Region Type'
FROM sys.dm_os_virtual_address_dump
order by region_base_address
GO

The output from this query will look like:

Each row in the output shows the base address and size in KB of contiguous regions in the SQL Server process memory map along with their attributes. For more information on interpreting the VAS flags refer to the MEMORY_BASIC_INFORMATION structure.

A convenient way to view a detailed map of the virtual address space for any process is to use the VMMAP memory analysis tool from Sysinternals. For example if I select the sqlservr.exe process as used in the T-SQL query above, a graphical view of the same memory map emerges:

VMMAP can also be scripted to run in a test suite (e.g. vmmap -p sqlservr.exe outputfile) which makes it a useful tool to chart the effect of a workload on VAS.

Another simple query to run on sys.dm_os_virtual_address_dump for a quick snapshot of allocated memory is to sum the different types of allocation. E.g.

/* Get total Committed, Reserved, Free pages at a glance */
select
SUM(case (region_state) when CONVERT(int, 0x1000) then region_size_in_bytes /1024 else 0 end) "Committed K",
SUM(case (region_state) when CONVERT(int, 0x2000) then region_size_in_bytes /1024 else 0 end) "Reserved K",
SUM(case (region_state) when CONVERT(int, 0x10000) then region_size_in_bytes /1024 else 0 end) "Free K"
from sys.dm_os_virtual_address_dump
where region_base_address > 0 

which on my laptop results in the following output:

Other potentially useful queries on this DMV are to track VAS allocations and free blocks by size to troubleshoot memory allocation problems. Here's an example from troubleshootingsql.com.

Slava Oks's articles on Yukon (SQL Server 2005) VAS make good further reading and are still current for SQL 2008 (though check the DMV column names, there are a few changes in Yukon since they were written):
Tracking VAS in Yukon, A look at Virtual Address Space (VAS), A look at Windows virtual memory mechanisms.