none
How to clean the clerk "MEMORYCLERK_SQLCLR" RRS feed

  • Question

  • Hi guys,

    Can I clean the clerk "MEMORYCLERK_SQLCLR" without restart the SQL Server service???

    I already close the connections but this clerk are using 20 GB of memory.

    Thanks for your time.


    Fabrício França Lima | MCP, MCTS, MCITP | Twitter: @fabriciodba | Consultoria Remota SQL Server: http://fabriciolima.net/blog/consultoria-remota/

    Wednesday, May 29, 2013 12:31 AM

Answers

  • Hi Fabricio

    No you cant clear memory used by CLR without restart using any command

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

    If it is not coming from buffer pool (single page allocators) max memory setting cant help.

     In SQL Server 2012 the memory management was changed. In 2012 Max Server Memory was set to include all page (single and multi) along with many other things. This leads to having more control over total memory usage (this includes CLR) which may be helpful for you... on the other hand since it now includes CLR, you may thrash the BP if your CLR starts eating away too much memory... so it doesn't, again, fix the root cause but allows it to be slightly more manageable.


    Thanks Saurabh Sinha

    http://saurabhsinhainblogs.blogspot.in/ ://saurabhsinhainblogs.blogspot.in/

    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    Wednesday, May 29, 2013 3:52 AM

All replies

  • Hi Fabricio

    No you cant clear memory used by CLR without restart using any command

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

    If it is not coming from buffer pool (single page allocators) max memory setting cant help.

     In SQL Server 2012 the memory management was changed. In 2012 Max Server Memory was set to include all page (single and multi) along with many other things. This leads to having more control over total memory usage (this includes CLR) which may be helpful for you... on the other hand since it now includes CLR, you may thrash the BP if your CLR starts eating away too much memory... so it doesn't, again, fix the root cause but allows it to be slightly more manageable.


    Thanks Saurabh Sinha

    http://saurabhsinhainblogs.blogspot.in/ ://saurabhsinhainblogs.blogspot.in/

    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    Wednesday, May 29, 2013 3:52 AM
  • You can not clear memory used by CLR without restart of the service.

    But, I would like to ask a question here.... to understand why changing the CLR Enabled configuration can do clearing the memory allocated for the same? Say for an example, if CLR Enabled configuration has been set up to 0, then why Microsoft is not interested to clear the cache?


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, May 29, 2013 8:38 AM
  • But, I would like to ask a question here.... to understand why changing the CLR Enabled configuration can do clearing the memory allocated for the same? Say for an example, if CLR Enabled configuration has been set up to 0, then why Microsoft is not interested to clear the cache?

    Because that switch only turns off user-loaded assemblies. You can still use the spatial data types and other built-in CLR features when "clr enabled" is 0.

    I would kind of execpt the memory to down if you drop the offending assembly, but this is nothing I have tested.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 29, 2013 10:24 AM
  • Hi Erland, Thanks for your reply.

    Further my reading on the below link, I can see Karthik(hopefully, he might chime in here) mentioned MEMORYCLERK_SQLCLR as aprt of BPOOL. Also he mentioned SQL Server CLR in MTL(NON-BPOOL). So I assume that the built-in, spatial comes uder MTL(NBPOOL) and User defined will come under BPOOL. Please correct me if my assumption is wrong.

    http://blogs.msdn.com/b/karthick_pk/archive/2013/03/16/sql-server-memory.aspx


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, May 29, 2013 11:09 AM
  • Thanks for your answer.

    Today the CLR_Clerk is using only 2 GB of memory.

    The SQL Server service was not restart this night.

    I don't know why, but the SQL cleared the memory of this clerk by himself.


    Fabrício França Lima | MCP, MCTS, MCITP | Twitter: @fabriciodba | Consultoria Remota SQL Server: http://fabriciolima.net/blog/consultoria-remota/

    Wednesday, May 29, 2013 12:49 PM
  • Hi Erland, Thanks for your reply.

    Further my reading on the below link, I can see Karthik(hopefully, he might chime in here) mentioned MEMORYCLERK_SQLCLR as aprt of BPOOL. Also he mentioned SQL Server CLR in MTL(NON-BPOOL). So I assume that the built-in, spatial comes uder MTL(NBPOOL) and User defined will come under BPOOL. Please correct me if my assumption is wrong.

    http://blogs.msdn.com/b/karthick_pk/archive/2013/03/16/sql-server-memory.aspx


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Can someone suggest my assumptions are correct or wrong please?

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, May 31, 2013 6:04 AM
  • Hello Latheesh,

    Most of the SQLCLR allocations are outside Bpool. There will also be very minimal allocation by SQLCLR in bPool (on versions <=SQL2008R2) . Hope that clarifies your question. When you get this sort of doubts please dont hesitate to post comment in blog immediately.

    Hello Fabrico ,

    Did your original issue resolve? If not please let me know the .Net version


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Friday, May 31, 2013 6:14 AM
    Moderator
  • Hi Karthick, Thank you for the reply. Yes I got the answer.

    BTW, Do you any list  that states which comes under the BPOOL and NBPOOL related to SQLCLR like Spatial and builtin(which built in) etc...That would give more info on the same.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, May 31, 2013 6:21 AM
  • The CLR is not enabled, no other features like spatial or anything used but the MEMORYCLERK_SQLCLR is consuming 1 GB +. This is the second top most mem consumer.  The only thing changed is, we have recently enabled Change Tracking. Is change tracking internally use CLR ? The version and edition is SQL Server 2008 R2 Enterprise SP1

    thanks

    Madhu


    MCITP, MCTS, MCDBA,MCP


    Tuesday, June 4, 2013 9:09 PM
    Moderator
  • Madhu, looking at the info in this post:

    SQLCLR and system functionality in SQL Server 2008 – part 1

    Bob states (emphasis mine):

    In SQL Server 2008, system functions that use SQLCLR that immediately come to mind include:
       a. The HierarchyID and upcoming spatial data types, Geometry and Geography
       b. Change Data Capture and the Dynamic Management Framework

    So, I think it is very likely that yes, enabling Change Tracking did affect what is returned by MEMORYCLERK_SQLCLR.

    Friday, September 4, 2015 7:06 PM
  • Yes, to various degrees you can clear the clearable items from SQLCLR memory without needing to restart the SQL Server service. There are some things that do not get cleared, but they make up a rather small amount of memory (less than 50 MB as far as my testing shows).

    There are a few methods, each affecting a different scope:

    1. Create a scalar function (or stored procedure, but I prefer function since it is easier to call and can still be run via EXEC) called GarbageCollect, and that does nothing more than:
      GC.Collect();
      Using this method will free up resources that are dereferenced, and without unloading the app domain or even clearing any of the SQLCLR objects from the cache.

    2. Unload the AppDomain that the Assembly is in by changing the PERMISSION_SET of the Assembly (via ALTER ASSEMBLY ... WITH PERMISSION_SET = something_differnet; ).

      This method unloads only a single AppDomain (the one that the Assembly being modified is in) but not other AppDomains in that same database, if there are any.

    3. Unload all AppDomains for a particular database by changing the TRUSTWORTHY setting (via ALTER DATABASE name SET TRUSTWORTHY opposite_value; ) to the opposite of the current value, and then change it back again. Doing this unloads only the AppDomains in the database being modified, but not AppDomains in other databases.

    4. Unload all AppDomains for all Databases by running:

      DBCC FREESYSTEMCACHE('ALL');

    I would start with #1 and work your way down until something works. And keep in mind that the memory consumption sometimes takes a few moments to clear out (a minute or two?).



    • Proposed as answer by Solomon Rutzky Friday, September 4, 2015 8:41 PM
    Friday, September 4, 2015 8:41 PM