1977268 - How to handle HANA Alert 40: 'Total memory usage of column-store tables'

Symptom

You experience HANA Alerts 'Total memory usage of column-store tables' (Alert ID = 40).

Environment

This KBA has been created based on SAP HANA 1.0 revision 81

Cause

This alert determines what percentage of the effective allocation limit is being consumed by individual column-store tables as a whole (that is, the cumulative size of all of a table's columns and internal structures).
The default settings for the alert are:

Severity	Low	Medium	High
Threshold	20 %	25 %	30 %

Usually this alert indicates issues with high memory usage.

Resolution

Procedure

1. Check memory consumption of column-store tables
2. Monitor data volume in regular intervals

Path:

1. SAP HANA Studio - SAP HANA Administration Console - Alerts
2. SAP HANA Studio - SAP HANA Administration Console - SQL Console
3. SAP HANA Studio - SAP HANA Administration Console - System Information
4. SAP HANA Studio - SAP HANA Administration Console - Configuration
   

How to:

This alert is implemented to control the memory usage of resident table data in the column store, together with the Alert 45 – Memory usage of main storage of Column Store tables. The basic analysis steps are:

1. Check memory consumption of column-store tables In order to understand the current and historic SAP HANA memory consumption of column-store tables, we should focus on the following questions:
   
   • What is the main memory, delta memory and total memory used by Column Store tables?
   • Which Column Store tables have the largest memory footprint?
   • Which Column Store tables have been least recently loaded into memory?
   There are several circumstances after checking the memory consumption:
   
   • Sum of main memory plus delta memory should be about the total memory displayed. If this is not the case, create a ticket to SAP Support to investigate the reason (Application Component: HAN-DB).
   • If delta memory << main memory, then processed according to KBA - 1977269 - How to handle HANA alert 45: Memory usage of main memory of a column-store table.
   Consider partitioning, data volume management and hardware extension based on a new sizing.
   
   • If delta memory >> main memory, then analyze the reason for the large delta according to KBA 1977314 - How to handle HANA Alert 29: Size of delta storage of a column-store table
   You can use the following tools and approaches to check values for memory of column-store tables:
   a)   Initial analysis from SQL commands
   For initial analysis use the following statements published in SAP note 1969700 - SQL Statement collection for SAP HANA
   - HANA_Tables_LargestTables
   - HANA_Tables_TopGrowingTables_Size_History
   If you require further assistance please contact SAP Support. Check with your Enterprise Support Advisor or Technical Quality Manager or create a ticket to SAP Support in component HAN-DB.
   b)   Memory information in SAP HANA studio
   Access System Information Tab in the SAP HANA Studio and check the content of Used Memory by Tables and Schema Size of Loaded Tables.
   The Used Memory by Tables shows total memory consumption of all column and row tables, while the Schema Size of Loaded Tables displays the aggregated memory consumption of loaded tables in MB for different database schemas. The aggregation comprises both Column Store and Row Store tables. Order by the Schema size column and find the largest consumers.
   c)   Memory information from SQL commands
   To get a high-level overview of the amount of memory used for Column Store tables, you can execute the following SQL statement:
   

   SELECT   ROUND(SUM(MEMORY_SIZE_IN_TOTAL)/1024/1024) AS "CS Memory (MB)", ROUND(SUM(MEMORY_SIZE_IN_MAIN)/1024/1024) AS "CS Memory In Main (MB)", ROUND(SUM(MEMORY_SIZE_IN_DELTA)/1024/1024) AS "CS Memory In Delta(MB)" FROM M_CS_TABLES WHERE LOADED <> 'NO'

   To get a breakdown by host, service, and schema, you can execute the following statement:
   

   SELECT S.HOST AS "Host", SERVICE_NAME AS "Service", SCHEMA_NAME AS "Schema", ROUND(SUM(MEMORY_SIZE_IN_TOTAL)/1024/1024) AS "Schema CS Memory (MB)" FROM M_CS_TABLES AS T JOIN M_SERVICES AS S ON T.HOST = S.HOST AND T.PORT = S.PORT WHERE LOADED <> 'NO' GROUP BY S.HOST, SERVICE_NAME, SCHEMA_NAME ORDER BY "Schema CS Memory (MB)" DESC

   You can use the following technique to examine the amount of memory consumed by a specific table. This also shows which of its columns are loaded, and the compression ratio that was accomplished. For example, list all tables for schema “SYSTEM”:
   

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

   Or drill down into columns of a single table, for instance the table "LineItem", 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 "Compr. Ratio" from M_CS_COLUMNS where TABLE_NAME = 'LineItem'

   
   
2. Monitor data volume in regular intervals
   After having resolved the current issue, monitor data volume of your largest tables in regular intervals based on the SAP Early Watch Alert report.
   

For details about further steps please refer to the referenced Knowledge Base Articles.
If you require further assistence please contact SAP Support. Check with your Enterprise Support Advisor or Technical Quality Manager or create a SAP incident on component SV-BO-DB-HAN. if you assume a product issue, then use component HAN-DB.

See Also

SAP HANA Troubleshooting and Performance Analysis Guide

Keywords

HANA Alert
Large Table
Partitioning
Operation Recommendations
#OpsRec-HANA