Pull user mode VAS limit details for x86 and x64 bit server using sys.dm_os_ring_buffers


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.

If you are interested in reading a lot about VAS,then I would recommend you reading the article written by Jonathan Kehayias(B/There.

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.

Conclusion

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.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s