Skip to Content

     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”) DESChour (“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.

systemAvail3.png

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

hostCPU.png

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.

memoryUsage2.png

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

1899480 – How to handle HANA Alert 31: ‘License expiry’

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Lars Breddemann

    Now that was a disappointing read to me.

    Random selection from the growing number of useful SAP HANA notes and KBAs but no added value.

    No additional insight, no story and nothing beyond a mere screenshot of a sample result set.

    It would have made so much more sense to simply put the content of the KBAs and FAQ notes in a SCN community medium, e.g. documents or a WIKI.

    The fact that content, that was deliberately created for an exclusive user group (those with access to SAP notes), is now re-issued 1:1 to SCN shows how much improvement to the information strategy of SAP is still required.

    And that’s not all your fault Jenny, so don’t take this critique too personal. At least half of it addresses the corporate strategy on this matter.

    Please do continue blogging. Tell us your stories, experiences and insights you’ve gained, maybe by using these technical checks you described. Don’t just document – explain and teach instead.

    Thanks and cheers, Lars

    (0) 
    1. Jenny Lee Post author

      Thank you, Lars. Your feedback is valued and noted. I am not discouraged as I will continue to contribute on SCN hoping that the content I post will benefit others.

      (0) 
    2. John Appleby

      Lars I think you are a bit harsh here. Whilst it is a reprint, it is the collation of data from multiple sources. That has some value.

      You are right, SAP isn’t good enough at this stuff, there is room for improvement.

      (0) 
  2. Ning Tong

    Hello Jenny,

    A good posting, isn’t it.

    You collect a lot of information from different source… 🙂

    It helps me when the problem related encounters.

    Thanks & Best Regards,

    Tong Ning

    (0) 

Leave a Reply