HANA Memory usage

During the development process, we are often the question arises: what is using memory at the moment? Well, we have a little request which helps in part to answer it:

select HOST,
       COMPONENT,
       CATEGORY,
       USED_MEMORY_SIZE
  from (SELECT HOST,
               PORT,
               CAST('Column Store Tables' AS VARCHAR(64)) AS COMPONENT,
               schema_name || '.' || table_name as CATEGORY,
               sum(memory_size_in_total) AS USED_MEMORY_SIZE
          FROM SYS.M_CS_TABLES_
         GROUP BY host,
                  port,
                  schema_name || '.' || table_name
        UNION ALL
        SELECT HOST,
               PORT,
               'Persistence' AS COMPONENT,
               CATEGORY,
               INCLUSIVE_SIZE_IN_USE AS USED_MEMORY_SIZE
          FROM SYS.M_HEAP_MEMORY_
         WHERE CATEGORY = 'Pool/PersistenceManager'
        UNION ALL
        SELECT HOST,
               PORT,
               'Column Store Intermediate Results and Shared Datastructures' AS COMPONENT,
               CATEGORY,
               INCLUSIVE_SIZE_IN_USE AS USED_MEMORY_SIZE
          FROM SYS.M_HEAP_MEMORY_
         WHERE CATEGORY = 'Pool/malloc/libhdbcscommon.so'
        UNION ALL
        SELECT HOST,
               PORT,
               'Memory Management Information' AS COMPONENT,
               CATEGORY,
               (EXCLUSIVE_SIZE_IN_USE) AS USED_MEMORY_SIZE
          FROM SYS.M_HEAP_MEMORY_
         WHERE CATEGORY like '%MemoryMapLevel2Blocks'
        UNION ALL
        SELECT HOST,
               PORT,
               'Database Kernel Statistics and Merge Statistics' AS COMPONENT,
               CATEGORY,
               (INCLUSIVE_SIZE_IN_USE) AS USED_MEMORY_SIZE
          FROM SYS.M_HEAP_MEMORY_
         WHERE CATEGORY = 'Pool/Statistics'
            OR CATEGORY = 'Pool/MergeMonitor'
        UNION ALL
        SELECT HOST,
               PORT,
               'Prepared Statement Cache' AS COMPONENT,
               CATEGORY,
               INCLUSIVE_SIZE_IN_USE AS USED_MEMORY_SIZE
          FROM SYS.M_HEAP_MEMORY_
         WHERE CATEGORY = 'Pool/RowEngine/SQLPlan'
        UNION ALL
        SELECT HOST,
               PORT,
               'Transaction Management' AS COMPONENT,
               CATEGORY,
               INCLUSIVE_SIZE_IN_USE AS USED_MEMORY_SIZE
          FROM SYS.M_HEAP_MEMORY_
         WHERE CATEGORY = 'Pool/RowEngine/Transaction'
        UNION ALL
        SELECT HOST,
               PORT,
               'Auxiliary Row Store Components' AS COMPONENT,
               CATEGORY,
               (INCLUSIVE_SIZE_IN_USE) AS USED_MEMORY_SIZE
          FROM SYS.M_HEAP_MEMORY_
         WHERE (CATEGORY = 'Pool/RowEngine' OR
               CATEGORY = 'Pool/malloc/libhdbrskernel.so')
           and CATEGORY != 'Pool/RowEngine/SQLPlan'
           and CATEGORY != 'Pool/RowEngine/Transaction'
        UNION ALL
        SELECT HOST,
               PORT,
               'Kernel Datastructures (Stack-Allocated)' AS COMPONENT,
               CATEGORY,
               INCLUSIVE_SIZE_IN_USE AS USED_MEMORY_SIZE
          FROM SYS.M_HEAP_MEMORY_
         WHERE CATEGORY = 'StackAllocator'
        UNION ALL
        SELECT HOST,
               PORT,
               'Row store tables + Indexes' as COMPONENT,
               table_name as CATEGORY,
               sum(MEMORY_SIZE)
          FROM (SELECT HOST,
                       PORT,
                       schema_name || '.' || table_name as table_name,
                       sum(ALLOCATED_FIXED_PART_SIZE) +
                       sum(ALLOCATED_VARIABLE_PART_SIZE) AS MEMORY_SIZE
                  FROM SYS.M_RS_TABLES_
                 GROUP BY host,
                          port,
                          schema_name || '.' || table_name
                UNION ALL
                SELECT HOST,
                       PORT,
                       schema_name || '.' || table_name as table_name,
                       sum(INDEX_SIZE) AS MEMORY_SIZE
                  FROM SYS.M_RS_INDEXES_
                 GROUP BY host,
                          port,
                          schema_name || '.' || table_name)
         GROUP BY host,
                  port,
                  table_name
        UNION ALL
        SELECT host,
               port,
               'Other Engine Components (misc.)' AS COMPONENT,
               CATEGORY,
               (EXCLUSIVE_SIZE_IN_USE) AS USED_MEMORY_SIZE
          FROM SYS.M_HEAP_MEMORY_
         where CATEGORY not in ('Pool/malloc/libhdbrskernel.so',
                                'StackAllocator',
                                'Pool/RowEngine/SQLPlan',
                                'Pool/RowEngine/Transaction',
                                'Pool/MergeMonitor',
                                'Pool/Statistics',
                                'Pool/malloc/libhdbcscommon.so',
                                'Pool/itab',
                                'Pool/malloc/libhdbbasement.so',
                                'Pool/malloc/libhdbcs.so',
                                'VirtualAlloc')
           and CATEGORY not like '%MemoryMapLevel2Blocks'
           and CATEGORY not like 'Pool/RowEngine%'
           and CATEGORY not like 'Pool/PersistenceManager%'
           and CATEGORY not like 'Pool/AttributeEngine%')
 order by USED_MEMORY_SIZE desc

The result looks like this:

Explanation of the fields:

HOST – The host name

Component – the type of object that occupies memory.

Category – their table or object that occupies memory.

USED_MEMORY_SIZE  – The size.

Of interest to us are usually two: Column Store Tables, Other Engine Components (misc.). The first table shows the number of which is space in the memory, the second shows no explicit costs when using queries or procedures. For example the growth rate “Pool / ValueArray” during the procedure suggests that one of the elements is stored in uncompressed form.. See note 1840954.