Query to find what modules are loaded inside SQL Server VAS (Virtual Address Space)
Code of sqlservr.exe and any other dll's it depends on need to be loaded into SQL Server memory space for execution. So if you are having VAS fragmentation, there are multiple causes and one of the cause is numerous dll's loaded by extended stored procedures. So to find what dll's are loaded and how much space (VAS allocated just for the code of the dll's and not the size allocated or committed by these dll's) used by these dll's then this is the right blog for you.
I was researching on this topic and got a excellent blog by Slava Oak:
https://blogs.msdn.com/b/slavao/archive/2005/01/28/362423.aspx
As per his instruction, I then wrote a query to get the modules and their region size to see the VAS used by these modules
SELECT olm.[name], olm.[file_version], olm.[product_version], olm.[description], ova.[region_size_in_bytes], olm.[base_address], ova.[region_base_address], ova.[region_type]
FROM sys.dm_os_virtual_address_dump ova
INNER JOIN sys.dm_os_loaded_modules olm ON olm.base_address = ova.region_allocation_base_address
ORDER BY name
I get a complete list of dll's loaded into SQL Server Virtual Address Space with their address region and size information like:
The query above shows the size split up based on each address region.
To find the cumulative VAS size per modules, then run this query:
SELECT olm.[name], olm.[file_version], olm.[product_version], olm.[description], SUM(ova.[region_size_in_bytes])/1024 [Module Size in KB], olm.[base_address]
FROM sys.dm_os_virtual_address_dump ova
INNER JOIN sys.dm_os_loaded_modules olm ON olm.base_address = ova.region_allocation_base_address
GROUP BY olm.[name],olm.[file_version], olm.[product_version], olm.[description],olm.[base_address]
ORDER BY [Module Size in KB] DESC
Here is the query to get the total virtual address space allocated to load the code of modules inside SQL Server address space:
SELECT SUM(ova.[region_size_in_bytes])/1024.0/1024.0 [Total Module Size in MB]
FROM sys.dm_os_virtual_address_dump ova
INNER JOIN sys.dm_os_loaded_modules olm ON olm.base_address = ova.region_allocation_base_address
For example the size of msfte.dll is 3807256 bytes in my D:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\Binn folder. The size of msfte.dll from the output of query above shows 3858432 bytes. So where is the difference? The difference is that size allocated to load this dll is rounded off to the next 4096 bytes region.
You can see that file size of these modules from the physical folder is matching with the region_size_in_bytes but rounded off to next 4096 bytes.