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:

Read more: SQLOS Team Blog

Posted via email from .NET Info