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).
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 */
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'
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'
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'
order by region_base_address
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 */
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"
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.