Monitoring VAS/MTL Memory Consumption

Question

Hi Johnathan,
   I have run the VAS_Summary query. 

   We have been dealing with MS Support as we are getting memory errors on our production SQL Server 2005.  Box is 32 bit.   They have suggested upgrading to SP3 and increasing the startup option to -g 384.  I did this over the weekend.

    What I have found is that initially after I restart SQL, the query returns over 400,000KB for Total Available RAM

    However, when I run any CLR routine (even a simple add function), the value shrinks to just over 200,000KB immediately.  To get the value back to 400,000 KB, I have to stop/start SQL Server.

    MS Support says there is no way to accurately monitor MTL, and to just look for Memory error messages in the ErrorLog.  Is there anything else I can do?

Jeremy

Answer 1

MTL and VAS Reservation are one in the same.  The VAS Summary script is monitoring the MTL/VAS reservation space.  What is the specific problem that you are trying to solve here?  Are you having App Domain unloads from memory pressure?  Hard OOM errors during the execution of your CLR processes?

What does your assembly do?  What is the output from the following query?

select single_pages_kb + multi_pages_kb + virtual_memory_committed_kb

from sys.dm_os_memory_clerks

where type = 'MEMORYCLERK_SQLCLR'

This will show the memory from the VAS/MTL reservation that is actually being used by SQLCLR.

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 2

SELECT multi_pages_kb, virtual_memory_committed_kb, shared_memory_committed_kb
 FROM sys.dm_os_memory_clerks
 WHERE type <> 'MemoryClerk_SQLBUFFERPOOL'

Above query will give total on non-bpool member consumption.