Monitor Memory Optimized Table Space Usage

Memory-optimized tables are fully durable by default, and, like transactions on (traditional) disk-based tables, transactions on memory-optimized tables are fully atomic, consistent, isolated, and durable (ACID). Memory-optimized tables and natively compiled stored procedures support only a subset of Transact-SQL features. The following blog post shows how to monitor the table space usage.

                ;
        WITH    system_allocated_memory ( system_allocated_memory_in_mb )
              AS ( SELECT   ISNULL(( SELECT CONVERT(DECIMAL(18, 2), 
             ( SUM(TMS.memory_allocated_for_table_kb)
             + SUM(TMS.memory_allocated_for_indexes_kb) )
             / 1024.00)
             FROM   [sys].[dm_db_xtp_table_memory_stats] TMS
             WHERE  TMS.object_id <= 0
             ), 0.00)
         ),
             table_index_memory ( table_used_memory_in_mb, table_unused_memory_in_mb, 
             index_used_memory_in_mb, index_unused_memory_in_mb )
             AS ( SELECT   ISNULL(( SELECT CONVERT(DECIMAL(18, 2), 
             ( SUM(TMS.memory_used_by_table_kb)
             / 1024.00 ))
             ), 0.00) AS table_used_memory_in_mb ,
             ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb)
             - SUM(TMS.memory_used_by_table_kb) )
             / 1024.00)
             ), 0.00) AS table_unused_memory_in_mb ,
             ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_used_by_indexes_kb)
             / 1024.00 ))
             ), 0.00) AS index_used_memory_in_mb ,
             ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_indexes_kb)
             - SUM(TMS.memory_used_by_indexes_kb) )
             / 1024.00)
             ), 0.00) AS index_unused_memory_in_mb
             FROM     [sys].[dm_db_xtp_table_memory_stats] TMS
            WHERE    TMS.object_id > 0
           )
        SELECT  s.system_allocated_memory_in_mb ,
                t.table_used_memory_in_mb ,
                t.table_unused_memory_in_mb ,
                t.index_used_memory_in_mb ,
                t.index_unused_memory_in_mb ,
            ISNULL(( SELECT DATABASEPROPERTYEX(DB_NAME(DB_ID()),
            'IsXTPSupported')
            ), 0) AS has_memory_optimized_filegroup
        FROM    system_allocated_memory s ,
           table_index_memory t
        
        SELECT  t.object_id ,
            t.name ,
            ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_used_by_table_kb )
            / 1024.00)
            ), 0.00) AS table_used_memory_in_mb ,
                ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_allocated_for_table_kb
                    - TMS.memory_used_by_table_kb )
            / 1024.00)
            ), 0.00) AS table_unused_memory_in_mb ,
                ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_used_by_indexes_kb )
            / 1024.00)
            ), 0.00) AS index_used_memory_in_mb ,
                ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_allocated_for_indexes_kb
                    - TMS.memory_used_by_indexes_kb )
            / 1024.00)
            ), 0.00) AS index_unused_memory_in_mb
        FROM    sys.tables t
            JOIN sys.dm_db_xtp_table_memory_stats TMS ON ( t.object_id = TMS.object_id )
          
           

All Memory Used by Memory Optimized Table across Database Engine

                
        -- this DMV accounts for all memory used by the hek_2 engine 
        SELECT type ,
        name ,
        memory_node_id ,
        pages_kb / 1024 AS pages_MB
        FROM sys.dm_os_memory_clerks
        WHERE type LIKE '%xtp%' 

Enable Natively Compiled Stored Procedure Stats Collection

                
        EXEC [sys].[sp_xtp_control_proc_exec_stats] @new_collection_value = 1  
        DECLARE @c BIT  
        EXEC sp_xtp_control_proc_exec_stats @old_collection_value = @c OUTPUT  
        SELECT  @c AS 'collection status' 

DBCC FREEPROCCACHE does not remove natively compiled stored procedures from Plan Cache

                -- https://connect.microsoft.com/SQLServer/Feedback/Details/3126441
        
        DECLARE @sql NVARCHAR(MAX) = N''
        
        SELECT  @sql += N'EXECUTE sp_recompile N'''
                + QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) + '''
        '
        FROM    sys.sql_modules sm
                JOIN sys.objects o ON sm.object_id = o.object_id
        WHERE   uses_native_compilation = 1
        
        EXECUTE sp_executesql @sql
        
                
        -- Reset wait and latch statistics.
        DBCC SQLPERF('sys.dm_os_latch_stats' , CLEAR)
        DBCC SQLPERF('sys.dm_os_wait_stats' , CLEAR) 

Errors Encountered During Migration :

Msg 41317, Level 16, State 5, Line 6
A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.