Sap Hana Tables

The dominant part of the SAP HANA Used Memory is evidently the space taken up by the data tables. SAP HANA provides separate measurements for Column Store tables and Row Store tables.

Table Types in HANA:

1> Column Store tables
2>  Row Store tables

Memory Consumption by Column Tables: 

The SAP HANA database loads columnar tables into memory column by column only upon use. This is sometimes called "lazy loading". This means that columns that are never used are not loaded, which avoids memory waste.

When the SAP HANA database runs out of allocated memory, it may also unload rarely used columns to free up some memory. Therefore, if it is important to precisely measure the total, or "worst case", amount of memory used for a particular table, it is best to ensure that the table is fully loaded first by executing the following SQL statement:

LOAD table_name ALL.

To examine the memory consumption of columnar tables, you can use the M_CS_TABLES and M_CS_COLUMNS views.


List All Column Tables of Schema (HANADB):

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


Providing details by Schema Name: 

select SCHEMA_NAME as "Schema", round(sum(MEMORY_SIZE_IN_TOTAL) /1024/1024) as "MB Used" from M_CS_TABLES GROUP by SCHEMA_NAME order by "MB Used" desc


You can use the following query for the amount of memory consumed by a specific table. This also shows which of its columns are loaded. For example, list all tables for schema HANADB: 

select TABLE_NAME as "Table", round(MEMORY_SIZE_IN_TOTAL/1024/1024) as "MB Used" from M_CS_TABLES where SCHEMA_NAME = 'HANADB' order by "MB Used" desc


Columns of a single table, for the table "Item", to view the actual size of the data, the “delta changes” and the compression ratio for each of its columns. 

select COLUMN_NAME as "Column", LOADED, round(UNCOMPRESSED_SIZE/1024/1024) as "Uncompressed MB", round(MEMORY_SIZE_IN_MAIN/1024/1024) as "Main MB", round(MEMORY_SIZE_IN_DELTA/1024/1024) as "Delta MB", round(MEMORY_SIZE_IN_TOTAL/1024/1024) as "Total Used MB", round(COMPRESSION_RATIO_IN_PERCENTAGE/100, 2) as "Comp_Ratio" from M_CS_COLUMNS where TABLE_NAME = 'Item'


Note: The M_CS_TABLES and M_CS_COLUMNS views contain a lot of additional information (such as cardinality, main-storage versus delta storage and more). For example, use the following query to obtain more information:

SELECT * FROM M_CS_COLUMNS WHERE TABLE_NAME = 'Table_name' and COLUMN_NAME = 'Column_name'

Memory Consumption by Row Tables:

Several system tables are in fact row tables. You can use the M_RS_TABLES view to examine the memory consumption of row tables.

select round(sum(USED_FIXED_PART_SIZE + USED_VARIABLE_PART_SIZE)/1024/1024) as "Row Tables MB Used" from M_RS_TABLES

For instance, you can execute the following SQL query, which lists all row tables of schema "SYS" by descending size

SELECT SCHEMA_NAME, TABLE_NAME, round((USED_FIXED_PART_SIZE +USED_VARIABLE_PART_SIZE)/1024/1024, 2) AS "MB Used" FROM M_RS_TABLES WHERE schema_name = 'SYS' ORDER BY "MB Used" DESC, TABLE_NAME