Memory-Optimized Tables

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.

Share this:

CloudIQ is a leading Cloud Consulting and Solutions firm that helps businesses solve today’s problems and plan the enterprise of tomorrow by integrating intelligent cloud solutions. We help you leverage the technologies that make your people more productive, your infrastructure more intelligent, and your business more profitable. 

US

626 120th Ave NE, B102, Bellevue,

WA, 98005.

 sales@cloudiqtech.com

INDIA

No. 3 & 4, Venkateswara Avenue,Bazaar Main Rd, Ramnagar South, Madipakkam, Chennai – 600091


© 2019 CloudIQ Technologies. All rights reserved.