For this blog, I would like to focus on some basic health checks for HANA. These checks can give you a good idea of how your HANA system is performing. We will go through some SQL statements and the thresholds to determine what the status of your HANA system is in. To know how the HANA system is performing, it can allow us to plan ahead and avoid unnecessary system disaster.
System Availability:
The following query shows you how many time each service was restarted in the specified hour and date within the analyzed period.
select to_dats(to_date("SNAPSHOT_ID"))AS "DATE", hour("SNAPSHOT_ID") AS "HOUR",
SUBSTR_BEFORE(T1.INDEX,RIGHT(T1.INDEX, 5)) AS "HOST", RIGHT(T1.INDEX,5)AS "PORT", T2.SERVICE_NAME, count ("ALERT_ID") AS "NUM_RESTART" from "_SYS_STATISTICS"."STATISTICS_ALERTS" T1 JOIN "SYS"."M_VOLUMES"
T2 ON SUBSTR_BEFORE(T1.INDEX,RIGHT(T1.INDEX,5))=T2.HOST AND RIGHT(T1.INDEX, 5)=T2.PORT WHERE ALERT_ID = '004' AND
SNAPSHOT_ID >= add_days(now(), -14) GROUP BY to_date("SNAPSHOT_ID"), hour ("SNAPSHOT_ID"), SUBSTR_BEFORE(T1.INDEX,RIGHT(T1.INDEX, 5)), RIGHT(T1.INDEX,5), T2.SERVICE_NAME ORDER BY to_date("SNAPSHOT_ID") DESC, hour ("SNAPSHOT_ID") DESC
STATUS | THRESHOLDS |
|---|---|
RED | Name server is not running |
Name server/ Index server had 3 or more restarts in the analyzed period | |
YELLOW | Statistics server is not running |
Name server / Index server had up to 2 restarts in the analyzed period | |
Remaining servers had 2 or more restarts in the analyzed period | |
GREEN | All other cases |
The example below shows that this standalone test system got restarted 1 time on October 22nd, 2 times on October 21st at around 11pm and another 2 times at around 10pm. In total, there are 3 restarts of the indexserver and nameserve in the analyzed period. If the nameserver is currently not running, then this will be rated as RED. To find out rather the database is restarted manually or due to some other reasons, you may go to index server and name server traces to get more information. If you need further assistance, please consider opening an incident with Product Support.
Top 10 Largest Non-partitioned Column Tables (records)
The following query displays the top 10 non-partitioned column tables and how many records exist in each.
SELECT top 10 schema_name, table_name, part_id, record_count from SYS.M_CS_TABLES where schema_name not LIKE '%SYS%' and part_id = '0' order by record_count desc, schema_name, table_name
STATUS | THRESHOLD |
|---|---|
RED | If tables with more than 1.5 billion records exist. |
YELLOW | If tables with more than 300 million records exist. |
GREEN | No table has more than 300 million records. |
In the threshold chart, it shows that if the column table has more than 300 million records; then it is in yellow rating.This is not yet critical with regards to the technical limit of 2 billion records but you should consider partitioning those tables that are expected to grow rapidly in the future to ensure parallelization and sufficient performance. For more information, please refer to the below SAP Notes or the SAP HANA Administration Guide.
Useful SAP Notes:
- 1650394 - SAP HANA DB: Partitioning and Distribution of Large Tables
- 1909763 - How to handle HANA Alert 17: ‘Record count of non-partitioned column-store tables’
Top 10 Largest Partitioned Column Tables (records)
This check displays the 10 largest partitioned column tables in terms of the number of records.
select top 10 schema_name, table_name, part_id, record_count
from SYS.M_CS_TABLES
where schema_name not LIKE '%SYS%' and part_id <> '0'
order by record_count desc, schema_name, table_name
STATUS | THRESHOLD |
|---|---|
RED | If table with more than 1.9 billion records exist. |
YELLOW | If table with more than 1.5 billion records and below 1.9 billion records exist. |
GREEN | No table has more than 1.5 billion records. |
The recommendation is to consider re-partitioning after it has passed 1.5 billion records as the technical limit is two billion records per table. If table is more than 1.9 billion records, then you should do the re-partitioning as soon as possible. For more information, please refer to the below SAP Notes or the SAP HANA Administration Guide.
Useful SAP Notes:
- 1650394 - SAP HANA DB: Partitioning and Distribution of Large Tables
Top 10 Largest Column Tables in Terms of Delta size (MB):
This check displays the 10 largest column tables in terms of the size of the delta and history delta stores.
select top 10 schema_name, table_name, part_id, round(memory_size_in_main /(1024*1024),2), round(memory_size_in_delta/(1024*1024),2), record_count, RAW_RECORD_COUNT_IN_DELTA from SYS.M_CS_TABLES
where schema_name not LIKE '%SYS%'
order by memory_size_in_delta desc, schema_name, table_name
STATUS | THRESHOLD |
|---|---|
RED | MEMORY_SIZE_IN_DELTA >10 GB |
YELLOW | MEMORY_SIZE_IN_DELTA >=5 GB AND <=10 GB |
GREEN | MEMORY_SIZE_IN_DELTA < 5 GB |
The mechanism of main and delta storage allows high compression and high write performance. Write operations are performed on delta store and changes are taken over from the delta to main store asynchronously during Delta Merge. The column store performs a delta merge if one of the following events occurs:
- The number of lines in delta storage exceeds the specified limit
- The memory consumption of the delta storage exceeds the specified limit
- The delta log exceeds the defined limit
Ensure that delta merges for all tables are enabled either by automatic merge or by application-triggered smart merge. In critical cases trigger forced merges for the mentioned tables. For more detail, please refer to the following SAP Note or the SAP HANA Administration Guide.
Useful SAP Notes:
-1977314 - How to handle HANA Alert 29: 'Size of delta storage of column-store tables
CPU Usage:
To check the CPU usage in relation to the available CPU capacity, you can go to the Load Monitor from SAP HANA Studio.
STATUS | Header 2 |
|---|---|
RED | Average CPU usage >=90% of the available CPU capacity |
YELLOW | Average CPU usage >=75% and < 90% of the available CPU capacity |
GREEN | Average CPU usage < 75% of the available CPU capacity |
The Load Graph and the Alert tabs can provide the information of time frame of the high CPU consumption. If you are not able to determine the time frame because the issue happened too long ago, check the following StatisticsServer table which includes historical host resource information up to 30 days:
"_SYS_STATISTICS"."HOST_RESOURCE_UTILIZATION_STATISTICS"
With the time frame, you may search through the trace files of the responsible process as they will provide indications on the threads or queries that were running at the time. If the high CPU usage is a recurrent issue that is due to scheduled batch jobs or data loading processes, then you may want to turn on the Expensive Statements trace to record all involved statements. For recurrent running background jobs like backups and Delta Merge, you may want to analyze the two system views: "SYS". "M_BACKUP_CATALOG" and "SYS"."M_DELTA_MERGE_STATISTICS" or "_SYS_STATICTICS"."HOST_DELTA_MERGE_STATISTICS"
For more information, please refer to the following SAP Note and also the SAP HANA Troubleshooting and Performance Analysis Guide.
SAP Note:
- 1909670 - How to handle HANA Alert 5: ‘Host CPU Usage'
Memory Consumption:
To check the memory consumption of tables compare to the available allocation limit, you may go to the Load Monitor From HANA Studio.
STATUS | THRESHOLD |
|---|---|
RED | Memory consumption of tables >= 70% of the available allocation limit. |
YELLOW | Memory consumption of tables >= 50% of the available allocation limit. |
GREEN | Memory consumption of tables < 50% of the available allocation limit. |
As an in-memory database, it is critical for SAP HANA to handle and track its memory consumption carefully and efficiently; therefore, HANA database pre-allocates and manages its own memory pool. The concepts of the in-memory HANA data include the physical memory, allocated memory, and used memory.
- Physical Memory: The amount of physical (system) memory available on the host.
- Allocated Memory: The memory pool reserved by SAP HANA from the operating system
- Used Memory: The amount of memory that is actually used by HANA database.
Used Memory serves several purposes:
- Program code and stack
- Working space and data tables (heap and shared memory) The heap and shared area is used for working space, temporary data, and storing all data tables (row and column store tables).
For more information, please refer to the following SAP Note and also the SAP HANA Troubleshooting and Performance Analysis Guide.
Useful SAP Note:
- 1999997 - FAQ: SAP HANA Memory
HANA Column Unloads:
Check Column Unloads on Load Graph under the Load Tab in the SAP HANA Studio. This graph will give you an idea of the time frame of any high activities of column unloads.
Header 1 | Header 2 |
|---|---|
RED | >= 100,000 column unloads |
YELLOW | >= 1001 and <100,000 column unloads |
GREEN | <=1000 column unloads |
Column Store unloads indicates the memory requirements exceed the current available memory in the system. In a healthy situation, it could be that the executed code request a reasonable amount of memory and requires SAP HANA to free up memory resources that are used rarely. However, if there is a high number of table unloads then it will have an impact on the performance as the tables needs to be fetched again from the disk.
There are a couple of things to look for.
- If the unloads happen on the statistics server, then it might be that the memory allocated for statistics server is not sufficient and most of the time it would accompany by Out of Memory errors. If this is the case, refer to SAP Note 1929538 HANA Statistics Server - Out of memory. On the other hand, if the unload motivation is 'Unused resource' then you should increase parameter global.ini [memoryobjects] unused_retention_period.
- If the unloads happen on the indexserver server and the reason for the unloads is due to low memory then it could be either of the reasons:
1) The system is not properly sized
2) The table distribution is not optimized
3) Temporary memory shortage due to expensive SQL or mass activity
For more detail information on this, please refer to SAP Note 1977207.
1977207 - How to handle HANA Alert 55: Columnstore unloads
License Information:
The view M_LICENSE can show the date that the HANA license will expire. You can also check the HANA license information from HANA Studio, right click the HANA system > Properties > License. If the license expires, the HANA system will be in a lockdown state; therefore, it is important to make sure the license is renewed before it expires.
select system_id, install_no, to_date(expiration_date), permanent, valid, product_name, product_limit, product_usage FROM "SYS"."M_LICENSE"
HANA database supports two kinds of license keys:
1) Temporary license key:
- It is valid for 90 days.
- It comes with a new SAP HANA database. During these 90 days, you should request and apply a permanent license key.
2) Permanent license key:
- It is valid until the predefined expiration date.
- Before a permanent license key expires, you should request and apply a new permanent license key.
For more information and steps to request for a license, please refer the SAP Note 1899480
- SAP Managed Tags:
4 Comments
