We all know that the user mode VAS(Virtual Address Space) limit for x86 bit server is 2GB and the same value for x64 bit server is pretty huge,and its 8 TB.
I was trying to figure out a way on how I can demonstrate/confirm this fact to the users when dealing with technical talks or during initial discussion phase of a project.
The answer to my question was found in sys.dm_os_ring_buffers DMV.This dmv exposes the ring buffer structure within SQL Server and can be used to pull multiple diagnostic infotmation.
This DMV is relatively undocumented,however there are enough blog posts out there in web written by deep technical experts which will come to your rescue.
To double check the VAS details you need to query this DMV with a condition for ring_buffer_type = RING_BUFFER_RESOURCE_MONITOR. This allows you to see memory state changes due to various types of memory pressure.
SELECT Cast(record AS XML), * FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' go
We will get the xml record as the output for this code and you can review the XML to get information related to VAS.
Below are the captured view of XML records for both x86 and x64 bit versions of SQL Server.You can clearly understand the VAS limit from this.
For x86 bit versions
For x64 bit versions
If you closely observe the XML data,you can see that there are lot of very useful memory related information which you can pull.These info can be really helpful for your troubleshooting process.
sys.dm_os_ring_buffers is really a powerful DMV which expose the ring buffer structure with in SQL Server and you can leverage this DMV to troubleshoot multiple issues.
Thanks for reading.