Monitoring memory Usage of table

 Memory is SAP HANA is used for a variety of purposes.

 First, the operating systems and support files are using memory.

second, SAP HANA has its own code and stack of program files that alsoconsume memory.

Third,memeory is consumed by the column and row store where data is stored.

Finally, SAP HANA needs memory for its working space where computations occur, temporary results are stored, and shared user memory consumption occurs.

In short, physical memory is the maximum memory available, some of which is used by the operating systems and files.The allocated limit is what is "given" to SAP HANA, and the remaining memory is free space.These allocations are important because you're using preallocated memory pools, and you can't rely on Linux operating system information for memory management.However,you can get this information in many other ways.

In addition to the memory consumption and memory tracking information in the preceding sections, you can also obtain memory information using SQL statements. In the M_SERVICE_MEMORY view, you can  execute a set of predefined SQL statements provided by SAP, including those shown below:


To access these statements, open the SQL console from the context menu of the VIEWS folder in  the SYS schema.


Total Memory Used by All Row Tables:
Select round (sum(USED_FIXED_PART_SIZE +USED_VARIABLE_PART_SIZE)/1024/1024) AS "ROW Tables MB" FROM M_RS_TABLES;

Total Memory used by All Column Tables:

Select round (sum(MEMORY_SIZE_IN_TOTAL)/1024/1024) AS "Column Tables MB" FROM M_CS_TABLES;

Total Memory Used by all column Tables in a Schema

Select SCHEMA_NAME AS "Schema", round (sum(MEMORY_SIZE_IN_TOTAL) /1024/1024) AS "MB" FROM M_CS_TABLES GROUP BY SCHEMA_NAME ORDER BY "MB" DESC;

For column tables, SAP HANA sometimes unloads infrequently used columns from memory to free up space when the allocated memory threshold is near the used capacity.So, when memory consumption is estimated, it's important to look at all column tables, not just those currently loaded in memory. You can see all column table sizes in a schema by using the SAP provided SQL statement:


Total Memory by column Tables in a schema:

Select TABLE_NAME AS "Table", round (MEMORY_SIZE_IN_TOTAL/1024/1024, 2)as "MB" FROM M_CS_TABLES WHERE SCHEMA_NAME = 'SYSTEM' ORDER BY "MB" DESC;

Select TABLE_NAME AS "Table", round (MEMORY_SIZE_IN_TOTAL/1024/1024, 2)as "MB" FROM M_CS_TABLES WHERE SCHEMA_NAME = 'XJRM63' ORDER BY "MB" DESC;