Udostępnij za pośrednictwem

Memory Usage in SQL CLR

  • Artykuł

There are two important memory considerations you may want to track when using SQL CLR functionality: 1) How much memory is SQL CLR using? And 2) How much memory is SQL CLR allowed to use?

 

The answer to the first question is pretty easy to answer thanks to the dmv sys.dm_os_memory_clerks.  The field single_pages_kb is for memory allocated in the SQL Buffer Pool, multi_pages_kb is for memory allocated by the SQL CLR Host that is outside the SQL Buffer pool, and virtual_memory_committed_kb is the amount of memory allocated by the CLR directly through bulk allocation interface (instead of heap allocation) through SQL server. The memory is mostly used for the managed GC heap and the JIT compiler heap, and it is also stored outside of the SQL Buffer Pool.  So, to get the total memory used by SQL CLR, you would run the following query:

 

select single_pages_kb + multi_pages_kb + virtual_memory_committed_kb from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLCLR'

 

Now that we know how much memory SQL CLR is using on the server, it would be nice to know how much memory SQL CLR is allowed to use.  You may be aware that when there is memory pressure on the server, SQL CLR will try to release memory by explicitly running garbage collection and, if necessary, unloading appdomains.

 

There are two types of memory pressure to be aware of:

- Physical memory pressure based on the amount of available system memory

- Virtual Address Space memory pressure based on the number of available virtual addresses

 

Physical memory pressure is pretty clear; if your server is under load and running low on available memory, then Windows issues a LowMemoryResourceNotification which SQL Server listens for and handles as Slava explains in two posts on his blog.  Understandably, SQL CLR can’t use so much memory that it causes external physical memory pressure.

 

Virtual Address Space memory pressure is more interesting and frequently more limiting from the SQL CLR perspective because it might cause memory pressure even when there is enough physical memory available.  This might happen because as was noted above most SQL CLR memory allocations occur outside of the SQL Buffer Pool in what is sometimes called the MemToLeave section.  The size of this area of memory is set by the –g flag on SQL Server start-up, but by default it is at least 256 MB.  I say “at least” because the value is not explicitly defined, it is simply the amount of VAS not reserved already by the SQL Buffer Pool.  Since the SQL Buffer Pool will not reserve more than the amount of physical ram, this would result in the case where a machine with less ram would actually have more VAS available in MemToLeave.

 

For an example of how this might affect SQL CLR, in a discussion with MVP Adam Machanic, it was noted than on his machine with 1 GB of ram, he was able to use more memory in SQL CLR than I was on my machine with 2 GB of ram.  Adam’s machine would have 1 GB reserved for the buffer pool and 1 GB left for MemToLeave, whereas my machine had 1792 MB reserved for the buffer pool and therefore SQL CLR was limited to the 256 MB left in MemToLeave.

 

Thankfully, Virtual Address Space memory pressure is primarily only an issue for x86 because on 64-bit machines, as Ken Henderson mentions, the user-mode VAS is 8 TB, so there is always plenty of VAS space left for SQL CLR.

 

-- Steven Hemingray

Comments

  • Anonymous
    April 02, 2006
    I see there are two rows where [type] = 'MEMORYCLERK_SQLCLR'.

    So wouldn't we need to sum ?
    Or does is actualy make sense to know both rows situation ?

    select count(*) as Address_Blocks
    , sum(single_pages_kb) as single_pages_kb
    , sum(multi_pages_kb) as multi_pages_kb
    , sum(virtual_memory_committed_kb) as virtual_memory_committed_kb
    , sum(single_pages_kb + multi_pages_kb + virtual_memory_committed_kb) as Total_SQLCLR_Memory
    from sys.dm_os_memory_clerks
    where [type] = 'MEMORYCLERK_SQLCLR'
  • Anonymous
    April 05, 2006
    Good question.  The second row you are seeing is for the DAC, so ordinarily you would not have any CLR memory allocated in that clerk.  

    However, on a NUMA system each node would have its own memory clerk in which case it may definitely be useful to sum all of them.
  • Anonymous
    July 09, 2006
    SQL CLRのメモリ消費とRAM増減時の-gオプションの影響の変化
  • Anonymous
    April 20, 2008
    PingBack from http://stocks-options-trading.info/trading-strategy/?p=982