Memory management with memory optimized tables

  • N_Muller

    SSCarpal Tunnel

    Points: 4035

    My database has several memory optimized tables and procedures that use the memory optimized table (non-native compiled procedures).  We are getting an error in our development system indicating lack of memory resource when running a stored procedure that uses the memory optimized tables.

    The development server has 32 GB.  The maximum server memory is set to 25 GB.  Currently, the allocated, used and unused (data + index) memory by memory optimized tables are:

    * Allocated:  2755 MB
    * Used: 1663 MB
    * Unused: 1092 MB

    I did not expect the server running out of memory from the pool with only 2.7GB of memory usage from memory-optimized tables out of 25GB available to SQL Server,

    How often does SQL server performs garbage collection from the memory optimized tables?  Is it possible to run those on demand?  If so, what would be the performance penalty in doing so?

  • TheSQLGuru

    SSC Guru

    Points: 134017

    search web for sql server in-memory oltp checkpoint files. You could do garbage collection or memory management too instead of checkpoint files. 

    If you do a lot of DML you can get a LOT of memory usage. 

    Remember that the memory will be shared with EVERYTHING in sql server - and 32GB is a VERY low amount of memory, even for a dev box!! My LAPTOP has that much!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • sqlnyc

    SSCommitted

    Points: 1726

    SQL Server performs garbage collection of row versions when ever it needs to, i.e. when there is memory-pressure that causes it to do so. Garbage collection for row versions cannot be controlled by anything external. 

    You posted this question in the SQL 2014 forum, so I assume that's what you're using. But no matter what version you are using, it should be fully patched, as there have been a lot of bugs fixed for the In-Memory feature.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply