How to measure CLR and / or AppDomain memoray usage?

• DennisPost

  SSCrazy

  Points: 2691

  More actions

  June 29, 2016 at 7:12 am

  #323656

  Hi,

  Last night the BufferPool on one of our servers got reduced to 0 and all OS memory was also gobbled up. A restart was needed to get things working again.

  Now that everything is working again, I want to beef up the VAS (Virtual address space. Aka MemToLeave) monitoring.

  I'm now monitoring the difference between sys.configuration Max. Memory and perfmon counter Server Total Memory.

  If this isn't the way to go about it, please let me know if there is a better way.

  When the Server Total Memory is lower than the Max. memory value I'll record the values from this query:

  -- https://blogs.msdn.microsoft.com/sqlclr/2006/03/24/memory-usage-in-sql-clr/

  SELECT[Type],

  SinglePagesMB= SUM(single_pages_kb) / 1024,

  MultiPagesMB= SUM(multi_pages_kb) / 1024,

  VirtualMemCommittedMB= SUM(virtual_memory_committed_kb) / 1024,

  TotalMemoryMB= SUM(single_pages_kb + multi_pages_kb + virtual_memory_committed_kb) / 1024

  FROMsys.dm_os_memory_clerks

  GROUPBY [type]

  HAVINGSUM(single_pages_kb + multi_pages_kb + virtual_memory_committed_kb) / 1024 > 0

  ORDERBY TotalMemoryMB DESC

  I'm assuming MEMORYCLERK_SQLCLR is the culprit, but just proving that is not enough. I would really like detailed data about how much memory each CLR is using.

  I'd also like to monitor how much memory each AppDomain is using.

  Can memory usage be correlated to CLRs and / or AppDomains?

  I haven't found any other DMVs to join to the CLR DMVs to give the data I want.

  SELECTTOP (10) * FROM sys.dm_clr_appdomains

  SELECTTOP (10) * FROM sys.dm_clr_loaded_assemblies

  SELECTTOP (10) * FROM sys.dm_clr_tasks

  Resources:

  Memory Usage in SQL CLR. Steven Hemingray[/url]

  SQL Server Memory Configuration, Determining MemToLeave Settings. John Sansom[/url]

  Understanding the VAS Reservation (aka MemToLeave) in SQL Server. Jonathan Kehayias