orkungedik/SAP-basis-dataset huggingface dataset:
## 2 Analyzing Generic Symptoms
The purpose of this section of the document is to help you to find the probable root cause of some generic problems and refer you to more detailed sections of the SAP HANA Troubleshooting and Performance Analysis Guide to proceed with your analysis.
Performance issues may be difficult to diagnose; problems may be rooted in a number of seemingly unrelated components. Checking for system alerts is a good starting point if you experience any trouble with your SAP HANA system. If the system issues an alert, refer to the Reference: Alerts section to find the part of this guide, an SAP Note or Knowledge Base Article which addresses the problem.
However, alerts are configurable (see Memory Problems for information on configuring alerts) and do not cover all aspects of the system, problems can still occur without triggering an alert. This section therefore describes some generic symptoms which you may observe and helps you to analyze the underlying problem.
## Related Information
Memory Problems [page 65] Alerts Reference [page 302]
## 2.1 Using the SQL Statement Collection for Analysis and Health Checks
A collection of predefined customizable statements is available for checking the database.
An extensive SQL statement collection is available for analyzing the SAP HANA database, this can be used to examine specific problems or for routine monitoring. The script collection is available in a zip file attached to the SAP Note 1969700 SQL Statement Collection for SAP HANA . Several scripts are referred to in this Troubleshooting Guide but a general starting point for system analysis is to check the overall health of the SAP HANA database by running the script HANA_Configuration_MiniChecks as illustrated below.
## Identify the Correct Version of a Script
Many of the statements are version specific, you must therefore choose the correct version of the script for the version and database revision of SAP HANA you are running. For example, if you are on SAP HANA 2.00.023 and script versions 2.00.000+, 2.00.010+, and 2.00.030+ are available, you should use the 2.00.010+ version. Script versions for earlier releases than your database release are expected to work, but you should avoid using script version for SAP HANA releases later than the release you are using. They may describe monitoring views or columns not yet available with your SAP HANA version.
## Modify a Script
Where relevant, the SQL support scripts contain sections which can be customized to enter your own parameter values, these are marked with the comment ' /* Modification section */ '. For example:
```
( SELECT               /* Modification section */ '%' HOST, '%' PORT, '%' OBJECT_TYPE, ... )
```
If you want to analyze a specific time range, you can modify the time parameters using the codes described in the comment block of the script, in the section INPUT_PARAMETERS . Y ou can use 'C-H12' BEGIN_TIME , or you can enter a specific date and time.
To do this, in place of 'C-H12' BEGIN_TIME and 'C' END_TIME , enter the date and time you want to analyze, in the format 'YYY/MM/DD HH24:MI:SS' .
Example: To check for transaction lock waits that happened between 19/08/2022 at 2pm and 20/08/2022 at 2pm, the modification part of the script would look like this:
```
SELECT Modification section 2022/08/19 2022/08/20 SERVER, UTC SITE_Id, HOST , SERVICE_NAME ,
```
## Script HANA_Configuration_MiniChecks
The output of the HANA_Configuration_MiniChecks script is illustrated here; the output report gives an overview of the overall health of the SAP HANA database. The report covers various aspects of the system including Memory, CPU, I/O, locks and other issues that may cause a performance problem. If a potentially critical issue is found the column 'C' is marked with an 'X' . The last column of the report references relevant SAP notes or Knowledge Base Articles that will help you to resolve the issues.
Example Output of the Configuration_MiniChecks Script
## Related Information
SAP Note 1969700
## 2.4 Common Symptoms and Troubleshooting
Typical symptoms and the related troubleshooting information are described in this section.
System-side slow performance, slow individual statement performance, and OOM problems are issues that you might experience while using the SAP HANA database. For each section, this document will cover the known symptoms and the corresponding troubleshooting steps to follow depending on the causes.
## 2.4.1  Slow System-wide Performance
Slow system-wide performance issues are problems that could be caused by excessive use of CPU, database resource locks or incorrect configuration of OS parameters.
Generally, when you encounter a performance issue, you may see these symptoms in SAP HANA cockpit:
- Continually high CPU usage according to OS commands or load graph (visible from CPU Usage tile Performance Monitor )
- Many pending or waiting threads in the thread view (details visible from the Threads tile).
To look for the cause at the operating system level refer to the topic System Appears to Hang with High System CPU Usage .
If the performance issue persists or if it recurs sporadically you may need to contact Support to analyze the root cause. In this case please generate at least two runtime dumps at 3 minute intervals while the system performance is slow for further investigation. For details refer to SAP Note 1813020 - How to generate a runtime dump on SAP HANA or the Guided Answer How to generate a runtime dump .
## Related Information
System Appears to Hang with High System CPU Usage [page 54]
SAP Note 1813020
How to generate a runtime dump (Guided Answer)
## 2.4.1.3 System Appears to Hang with High System CPU Usage
The SAP HANA database is an in-memory database and by its nature it consumes large amounts of memory. Some performance issues of SAP HANA can be caused by the operating system's memory management features.
For this case, you can see high usage of SYSTEM CPU that can be monitored in the Performance Monitor , available from SAP HANA cockpit Monitoring and Administration CPU Usage .
You can begin an analysis of CPU related problems by running a Kernel Profile trace either:
- via the SQL console (see ALTER SYSTEM START KERNEL PROFILER statement)
- in hdbcons as described in KBA 1804811 SAP HANA Database: Kernel Profiler Trace .
The kernel profiler trace is required for analysis by SAP Support. Refer to the topic HANA System-wide Performance Analysis for how to analyze the kernel profiler trace. Refer to KBA 2222250 - FAQ: HANA Workload Management which gives guidance on CPU-related configuration settings.
Root cause: Problem caused by the configuration of transparent huge page
## Required action:
1. Check Transparent Huge Page whether it is set to [never] by running the command cat /sys/ kernel/mm/transparent_hugepage/enabled .
2. Apply KBA 2031375 - SAP HANA: Transparent HugePages (THP) setting on Linux .
Root cause: Problem caused by the configuration of OS page cache
## Required action:
1. Check sar file ( /var/log/sa ) whether kbcached usage ( sar -r ) is higher than 10% of physical memory and high page in/out occurred.
2. Check and apply SAP Note 1557506 - Linux paging improvements .
Root cause: Problem caused by translation lookaside buffer (TLB) shootdown
## Required action:
1. Check plan trace.
Root cause : Due to high context switches (High SYS CPU) by many SqlExecutor threads
## Required action:
1. Check Performance Monitor and indexserver.ini ->= sql ->= sql_executors / max_sql_executors and refer to Controlling Parallelism of SQL Statement Execution .
## Related Information
Transparent Huge Pages on Linux [page 204]
System Hanging Situations [page 204] HANA System-wide Performance Analysis [page 46] Controlling Parallel Execution of SQL Statements [page 87]
```
SAP Note 2031375 SAP Note 1557506 SAP Note 2222250 SAP Note 1804811
```
## 2.4.1.4 Slower Response with High User CPU Usage
The performance may degrade with increased usage of the CPU and applications.
Root cause: Not many executor threads but high CPU in a few nodes and a few tables accessed
Required Action: In an SAP Business Warehouse system, check for non-even partitioning of huge column tables. For more information, see SAP Note 1819123 - BW on SAP HANA SP5: landscape redistribution and Indicator for Large Delta Storage of Column Store Tables .
Root Cause: Performance degradation by huge MVCC versions
Required Action: Check for KPI Active Version in the Performance Monitor to find the MVCC garbage blocker, then kill it via the Threads tile on the Overview page. For more information, see Troubleshooting Blocked Transactions .
Refer also to the section 'Analysis of Former Performance Problems' in the topic HANA System-wide Performance Analysis.
## Related Information
Indicator for Large Delta Storage of Column Store Tables [page 131]
Blocked Transactions [page 167]
HANA System-wide Performance Analysis [page 46]
SAP Note 1819123
## 2.4.2  Slow Individual SQL Statements
This section looks at the causes of slow individual SQL statements although there is no significant performance issue on system level and the associated troubleshooting steps.
If the following doesn't help to resolve the problem, see Getting Support . In this case, to analyze further, collect the explain plan, plan visualizer file, performance trace and catalog export. Also refer to SQL Statement Analysis and Query Plan Analysis for more information. SAP KBA 2000002 - FAQ: SAP HANA SQL Optimization explains general information about SQL optimization.
## Related Information
```
SQL Statement Analysis [page 238] Query Plan Analysis [page 248] SAP Note 2000002 Getting Support
```
## 2.4.2.1 A Statement is Sometimes Slow and Sometimes Fast
There are a number of things to check when you experience inconsistent query execution time.
Check the following possibilities:
Root Cause: Same query shows different execution plans according to the HANA SQL Plan Cache.
Required Action: Analyze the difference in the execution plan by using the Explain Plan tool as outlined in SAP Note 2410208 Collect Explain Plan of a Prepared Statement .
Once you have found the corresponding statement hash for a problematic query, you can check how many different plans are stored in the Plan Cache by running the following query:
```
SELECT PLAN_ID, STATEMENT_HASH FROM M_SQL_PLAN_CACHE WHERE STATEMENT_HASH = '<statement_hash>'
```
Typically, the result will show multiple rows for a single statement hash value each with a different PLAN_ID value. Using the Plan IDs listed in the result you can execute the statements given in SAP Note 2410208 to display the execution plan and identify the differences there.
Additionally, you can check why a new execution plan was generated by selecting the column LAST_INVALIDATION_REASON. The reasons mentioned there are discussed in SAP KBA 2124112 FAQ: SAP HANA Parsing ('How can I determine the reason for a statement recompilation?')
If you want to prevent this from happening consider using the Plan Stability feature which captures the execution plans of the SQL statements and applies them later when the statements are compiled again. In this way Plan Stability can provide consistent performance across different revisions. You can use Plan Stability in SAP HANA Cockpit or from the command line as described in the SQL Plan Stability section of this guide. The following SAP Note gives additional information: 2639193 SAP HANA SQL Plan Stability .
Root Cause: If a related table was unloaded, it takes some time to load tables
Required Action: Check unload trace and execute after table loaded fully. You can refer to LOADED column of m_CS_TABLES.
Root Cause: Query compilation time is long.
Required Action: Check the execution time after adding 'with hint (ignore_plan_cache)' at the end of query. This hint will always cause the query to be compiled. If a long running compiled query plan has been evicted frequently from the plan cache, increase the query cache size. For more information, see SQL Plan Cache Analysis .
Root Cause: Merge status of column table can affect query plan
Required Action: Check MEMORY_SIZE_IN_DELTA, RAW_RECORD_COUNT_IN_DELTA, and LAST_MERGE_TIME columns of m_CS_TABLES whether there is large amount of data in delta. Check m_DELTA_MERGE_STATISTICS to see when the last merge occurred. For more information, see Delta Merge .
## Related Information
SQL Plan Cache Analysis [page 185]
```
Delta Merge [page 129] SQL Plan Stability [page 284] SQL Plan Stability (SAP HANA Cockpit) SAP Note 2410208 SAP Note 2124112 SAP Note 2639193
```
## 2.4.2.2 A Statement is Slower After an Upgrade
After upgrade, the query execution time can be different because of changes in the query execution plan.
Root cause: After an upgrade, a query can have a different plan, which leads to a different execution time.
Required Action: If you have an instance running on an older revision, compare the plan and collect the plan visualizer file. See Getting Support for further help.
Refer also to the SQL Plan Stability section of this guide; this feature offers the option to preserve a query's execution plan by capturing an abstraction of the plan and reusing it after the upgrade to regenerate the original plan and retain the original performance.
For more information, see Expensive SQL Statements .
## Related Information
Expensive SQL Statements [page 77]
```
Analyzing SQL Execution with the Plan Visualizer [page 253] SQL Plan Stability [page 284] Getting Support
```
## 2.4.2.3 A Query on Multiple Nodes Can Be Slower
In distributed systems, query execution can be routed to other nodes for better performance. However, there is a chance of having slow performance in the case where the network used for transferring intermediate results generated during query execution is slow or where there is an inefficient network configuration.
Root cause: A misconfigured network can lead to slow queries.
Required Action: Check your network configuration and its bandwidth/latency among SAP HANA servers. For more information see Network View and SAP Note 2183363 - Configuration of SAP HANA internal network .
Root cause: Statement routing and huge data transfer among distributed nodes can cause differences of query execution times due to the difference of anchor nodes.
Required Action: Check how much data is transferred among distributed nodes and the network performance. You can use the PlanViz Tool to check which nodes in the network are involved during the execution of the query and how much data is transferred (see Overview Page, Plan Viz). The KPI values for the distribution show:
- Number of nodes
- Number of network transfers
- Network traffic
Consider the locations of joined tables to reduce transferred intermediate result size.
## Related Information
Network View [page 261]
Performance Trace [page 289]
Overview Page [page 256]
SAP Note 2183363
## 2.4.2.4 Slow Select for all Entries (FAE) Query
There are a number of points to check if you have performance issues with SELECT FOR ALL ENTRIES (FAE) from an ABAP query.
For FAE on SAP HANA, please generally refer to SAP Note 1662726 - Optimization of select with FOR ALL ENTRIES on SAP HANA database .
Root cause: Indexes are missing.
Required Action: Check WHERE clause and check concat indexes for all fields used in WHERE clause.
Root cause: Due to DBSL behavior, slower operator can be chosen.
Required Actions: Apply parameters for ABAP optimization. For more information, see SAP Note 1987132 SAP HANA: Parameter setting for SELECT FOR ALL ENTRIES .
Root cause: When using less than or greater than ('&lt;' or '>=') filters in FAE query, it can be slower than having no filter.
Required Actions: Do not use less than or greater than ('&lt;' or '>=') filters in an FAE query.
## Related Information
```
SAP Note 1662726 SAP Note 1987132
```
## 2.4.2.5 All Statements Accessing a Specific Table are Slow
In case queries run on specific tables are slow, check if there are too many versions.
Root cause: If there are too many versions of single records, accessing the table can be slow. In this case, the number of system-wide MVCC versions is in acceptable range. To verify further whether there are too many versions for a specific table, check the result of this query:
```
SELECT * FROM M_RS_TABLE_VERSION_STATISTICS where table_name='mytable';
```
This can be caused by a cursor unnecessarily being held on a query result or a long-lived transaction without a commit/rollback.
Required Action: Applications should commit as early as possible or close any cursors that are not needed. Check the application logic to see whether it really needs to update single records frequently.
## 2.4.3  Frequent Out of Memory (OOM)
If Out Of Memory situations happen frequently, it can also lead to performance drop by unloading tables or shrinking memory jobs.
First check Memory Problems and SAP KBA 1999997 - FAQ: SAP HANA Memory as they provide information on SAP HANA memory and its problems.
This section introduces common problems and their troubleshooting steps.
## Related Information
Memory Problems [page 65] SAP Note 1999997
## 2.4.3.1 Out of Memory Caused by Sudden Increased Memory Usage
Check the memory consumption of statements in the event of OOMs caused by suddenly increased memory usage.
Root cause : Huge intermediate results during query processing.
Required Actions: Enable memory tracking by setting the following parameters to on in the global.ini file resource_tracking section.
- enable_tracking = on
- memory_tracking = on
Enable the Expensive Statement Trace by setting the status to Active .
Then, check the memory consumption of statements using m_EXPENSIVE_STATEMENTS.MEMORY_SIZE. After your analysis you can optimize any problematic queries that were found.
## 2.4.3.2 Out of Memory Caused by Continuously Increased Memory Usage
Check if many statements trigger an Out Of Memory in a system where used memory is continuously increased.
Root cause: Commit/rollback within stored procedure can lead to memory leakages. Do not use exec('commit') or exec('rollback') within a stored procedure. If this syntax is used, the system cannot reclaim the memory used for query execution because its related transactions are left hanging.
Required Actions: Remove exec('commit') or exec('rollback'). If you would like to use commit/rollback within stored procedure, see SAP HANA SQLScript Reference for more information.
Root cause: Due to memory leakage caused by a programming error
Required Action: Check the Performance Monitor in SAP HANA cockpit Overview Memory Usage to determine whether used memory continuously increases without a significant increase in data size. If you find a suspicious component which keeps allocating memory, create a support ticket attaching a full system dump, mm trace, and the output of _SYS_STATISTICS.HOST_HEAP_ALLOCATORS_BASE.
## Related Information
SAP HANA SQLScript Reference
## 2.4.3.3 Out of Memory While High Usage Of Column Store Components' Allocators
If a system is undersized, Out Of Memory (OOM) can happen frequently.
Root cause:
Undersized memory
## Required Action:
1. Check top allocators in [MEMORY_OOM] section of OOM dump to see whether they are for column store components as described in section 13 of KBA 1999997 - FAQ: SAP HANA Memory .
2. Check the unload trace whether frequent unloading of tables took place.
3. Reduce the amount of data in column store or increase physical memory.
## Related Information
SAP Note 1999997
## 2.4.3.4 Out of Memory Caused by Large Memory Usage of Statistics Server
This case can happen if the majority of memory used by the statisticsserver is due to many alerts or undersized memory.
Root cause: Due to big STATISTICS_ALERTS_BASE table size, an OOM can occur.
Required Action: Check table size of _SYS_STATISTICS.STATISTICS_ALERTS_BASE and truncate STATISTICS_ALERTS_BASE from hdbsql based on solution from SAP Note 2170779 - SAP HANA DB: Big statistics server table leads to performance impact on the system .
Root cause: Big Pool/Statistics allocator size
Required Action: Check SAP Note 2147247 - FAQ: SAP HANA Statistics Server first.
## Related Information
SAP Note 2170779
SAP Note 2147247
## 2.4.3.5 Out of Memory Occurs due to High Usage of Shared Memory
Shared memory is space where system information and row store tables are stored.
Check the following if the used amount of shared memory is high.
Root cause: Severely fragmented row store tables
## Required action:
1. Check whether SHARED_MEMORY in [MEMORY_OOM] ->= IPMM short info of OOM dump is unusally high.
2. Apply SAP Note 1813245 - SAP HANA DB: Row store reorganization .
Root cause: Memory shortage is caused by high usage of memory of row store tables in an SAP Business Warehouse (BW) system
Required action: In an SAP Business Warehouse system the used amount of shared memory is high and SAP Note 1813245 doesn't recommend row store reorganization, first apply SAP Note 706478 - Preventing Basis tables from increasing considerably , then apply SAP Note 1813245 - SAP HANA DB: Row store reorganization again.
Root cause: Memory shortage is caused by high usage of memory of row store tables in non-SAP Business Warehouse (BW) systems
Required Action: Check if you can convert some row store tables into column store tables or archive old data to reduce the memory size, or else increase the system memory.
## Related Information
SAP Note 1813245
SAP Note 706478
## 2.4.4  Table Consistency Check
This section gives basic information for solving problems related to the Table Consistency Check.
The table consistency check is a procedure available in the SAP HANA database that performs a range of consistency check actions on database tables. It can be run from the command line or scheduled within the statistics service. Alert #83 Table Consistency is generated if the consistency check detects a given number of errors.
## Monitoring Views
Results of all consistency checks, whether executed manually or scheduled in the statistics service, are available in two monitoring views:
- m_CONSISTENCY_CHECK_HISTORY
- m_CONSISTENCY_CHECK_HISTORY_ERRORS
The first view gives high level information about parameters used for the check, runtime duration and the total number of tables with errors; each run is identified by a CHECK_EXECUTION_ID value. The errors table lists and gives details of all errors found within each check run.
Additionally, the monitoring view m_CS_TABLES, holds values for: LAST_CONSISTENCY_CHECK_TIME, LAST_CONSISTENCY_CHECK_ERROR_COUNT.
## Common Errors
If you encounter errors when running the table consistency check you should first try to rerun the check as some errors may only be due to timing issues related to the current workload and would not occur again if the table consistency check is repeated.
If the same error does recur check the following trace files:
- Database trace file available in SAP HANA Studio or Database Explorer: &lt;service>=_&lt;host>=.&lt;port>=.&lt;counter>=.trc
- Dedicated trace file: &lt;service>=_&lt;host>=.&lt;port>=.table_consistency_check.&lt;timestamp>=.trc. For this trace file the parameter enable_table_consistency_check_trace must be enabled.
If necessary, increase the trace level to debug to get sufficient diagnostic information:
```
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('trace', 'table_consistency_check') = 'debug'
```
Search the trace file for any errors and make a note of the error code. This information will be helpful if it is finally necessary to contact SAP Support for assistance.
## More Information
For background information see Table Consistency Check in the SAP HANA Administration Guide . Refer also to the following SAP Notes:
- 2116157 FAQ: SAP HANA Consistency Checks and Corruptions
- 1977584 Technical Consistency Checks for SAP HANA Databases
## Related Information
SAP Note 2116157
SAP Note 1977584
## 3 Root Causes and Solutions
This section provides detailed information on the root causes of problems and their solutions.
System alerts are a good indicator for the underlying problem. The SAP Notes you will be directed to are a source of background information, explanations, alternative options, FAQs or useful SQL statements, for example. Detailed monitoring and administration of the SAP HANA databases is possible via the SAP HANA cockpit and the SAP HANA database explorer.
## 3.1 Memory Problems
This section discusses the analysis steps that are required to identify and resolve memory related issues in the SAP HANA database.
For general information on SAP HANA memory management see the SAP HANA Academy video SAP HANA Memory Management which discusses the memory concept and comments on the difference between used, allocated, and resident Memory.
Alerts related to memory problems and corresponding SAP Notes are documented in the Alerts reference table (see category 'Memory'). SAP Note 1840954 - Alerts related to HANA memory consumption includes information on how to analyze out-of-memory (OOM) dump files.
The SAP HANA Administration Guide gives details on using the tool SAP HANA cockpit to analyze memory problems.
In order to understand the current and historic SAP HANA memory consumption you can use the following tools and approaches:
- Memory information in SAP HANA cockpit
- Memory information from logs and traces
- Memory information from SQL commands
- Memory information from other tools (see the tools overview in the SAP HANA Administration Guide ).
## Related Information
SAP Note 1840954
Alerts Reference [page 302]
SAP HANA Administration Guide
Video HANA Academy: SAP HANA Memory Management
## 3.1.1  Memory Information in SAP HANA Cockpit
There are a number of sources of information in SAP HANA cockpit that can assist you in understanding memory utilization.
- Open the Manage Services page from the Overall Database Status tile for high-level information about physical memory, allocation limit, and used memory for each service.
- To get a graphical overview about physical memory, allocation limit, used memory, and resident memory open the Performance Monitor from the Memory Usage tile.
- Open the Memory Analysis app from the Memory Usage tile for details about memory utilization as well as history information. For example, click the Components tab in the Memory Analysis app to view the used memory grouped by different components like "Statement Execution & Intermediate Results" or "Column Store Tables". When you choose a component, the corresponding historic information of memory usage is displayed by a graph.
## 3.1.4  Memory Information from Other Tools
Other tools are also available to analyze high memory consumption and out-of-memory situations.
A number of SAP Notes and 'How-to' documents are available to provide help with some of the most common questions and difficulties related to memory. The tool hdbcons provides expert functionality to analyze memory issues. You can use this tool (typically with guidance from the SAP Product Support team) to create runtime dump files and analyze the details of memory consumption. If necessary, dump files can be sent to Product Support for further analysis.
The following SAP Notes may be helpful in solving some problems and when analyzing memory issues with hdbcons .
- 1999997 FAQ: SAP HANA Memory
- 2400007 FAQ: SAP HANA Runtime Dumps
- 2222218 FAQ: SAP HANA Database Server Management Console (hdbcons)
- 1786918 Required information to investigate high memory consumption (hdbcons)
- 1813020 How to generate a runtime dump on SAP HANA
- 1984422 How-To: Analyzing SAP HANA Out-of-memory (OOM) Dumps
- How to troubleshoot HANA High Memory Consumption (Guided Answer)
##  Tip
Guided Answers is a support tool for troubleshooting problems using decision trees. A guided answer is available for How to troubleshoot HANA High Memory Consumption .
## Related Information
```
SAP Note 2400007 SAP Note 2222218 SAP Note 1786918 SAP Note 1813020 SAP Note 1984422 How to troubleshoot HANA High Memory Consumption (Guided Answer)
```
## 3.1.5  Root Causes of Memory Problems
Once you have completed your initial analysis, you have the information required to start the next phase of your analysis.
Based on the results from the analysis approaches you are now able to answer the following questions:
- Is it a permanent or a sporadic problem?
- Is the memory consumption steadily growing over time?
- Are there areas with critical memory consumption in heap, row store or column store?
- Is there a big difference between used memory and allocated memory?
In the following you can find typical root causes and possible solutions for the different scenarios.
A valuable tool for analyzing memory problems is the Expensive Statements Trace which is useful in cases where individual statements seem to exhaust the system's memory. This is described separately in the Tools and Tracing section: Analyzing Expensive Statements Traces.
## Related Information
Analyzing Expensive Statements Traces [page 243]
## 3.1.5.1 Significant External Memory Consumption
If the database resident memory of all SAP HANA databases on the same host is significantly smaller than the total resident memory, you have to check which processes outside of the SAP HANA databases are responsible for the additional memory requirements.
Typical memory consumers are:
- Operating system (for example, caches, mapping structures)
- Third party tools (for example, backup, virus scanner)
How to identify top memory consumers from non-SAP HANA processes is out of scope of this guide. However, when you are able to identify the reason for the increased memory consumption of the external program, you can check if it is possible to optimize its configuration.
## 3.1.5.2 Space Consumed by Large Tables
If particularly large tables consume significant amounts of space in the row store or column store, you should check if the amount of data can be reduced.
The following references will be helpful:
- SAP Note 2388483 - How To: Data Management for Technical Tables describes archiving and deletion strategies for typical SAP tables with a technical background; for example, tables required for communication, logging or administration.
- General recommendations for managing data can be found on the Information Lifecycle Management page.
For more information on memory management for resident table data, see Managing Tables in the SAP HANA Administration Guide and the following SAP Notes:
- SAP Note 2222277 - FAQ: SAP HANA Column Store and Row Store
- SAP Note 2220627 - FAQ: SAP HANA LOBs
- SAP Note 2388483 - How-To: Data Management for Technical Tables
## Related Information
```
https://www.sap.com/products/information-lifecycle-management.html Managing Tables SAP Note 2222277 SAP Note 2220627 SAP Note 2388483
```
## 3.1.5.3 Internal Columns in the Column Store
For several reasons, SAP HANA creates internal columns in the column store. Some column store tables are automatically loaded into memory.
## Internal Columns
You may be able to optimize or remove internal columns in order to reduce memory usage. In some situations a cleanup is possible, for example, in the case of CONCAT attribute columns that were created in order to support joins. For more information see SAP Note 1986747: How-To: Analyzing Internal Columns in SAP HANA Column Store .
## Column Store Table Loads and Unloads
The SAP HANA system dynamically loads column store tables into memory during system restart and when required by the application. You may be able to optimize the number of pre-loaded tables. For more information, see SAP Note 2127458: FAQ: SAP HANA Loads and Unloads .
## Related Information
```
SAP Note 2127458 SAP Note 1986747
```
## 3.1.5.4 Memory Leaks
A memory leak is a memory area (typically a heap allocator) that grows over time without any apparent reason.
If you have identified a suspicious area, proceed as follows:
- Check for SAP Notes that describe the memory leak and provide a solution.
- Check if the problem is reproducible with a recent SAP HANA revision.
- If you can't resolve the problem yourself, open an SAP customer message and use the component HANDB.
Before you open a Support incident refer to SAP Note 1786918 Required information to investigate high memory consumption .
Information about a specific memory area can be obtained from the output of the following hdbcons command giving the name of the heap allocator as a parameter. This information is also particularly helpful to the Support team when analyzing memory problems.
```
hdbcons "mm -bl <allocator_name>"
```
## Related Information
```
SAP Note 1786918 Problem Analysis Using hdbcons
```
## 3.1.5.5 Large Heap Areas
Some heap areas can be larger than necessary without being a memory leak.
SAP Note 1840954 - Alerts Related to HANA Memory Consumption contains an overview of heap allocators with a potentially large memory consumption and possible resolutions.
Refer also to SAP Note 1999997 - FAQ: SAP HANA Memory .
## Related Information
SAP Note 1840954
## 3.1.5.6 Expensive SQL Statements
SQL statements processing a high amount of data or using inefficient processing strategies can be responsible for increased memory requirements.
See SQL Statement Analysis for information on how to analyze expensive SQL statements during times of peak memory requirements.
## Related Information
SQL Statement Analysis [page 238]
Setting a Memory Limit for SQL Statements [page 79] Analyzing Expensive Statements Traces [page 243]
## 3.1.5.7 Memory Consumption Related to Transactional Problems
High memory consumption can be caused by problems with transactions.
In some cases, high memory consumption is caused by wait situations, which can have different reasons:
- Long-running or unclosed cursors,
- Blocked transactions,
- Hanging threads.
As one of the negative impacts, used memory is not released any more. In particular, the number of table versions can grow up to more than 8,000,000 which is considered the amount where an action is required.
For more information, see the separate section Transactional Problems .
## Related Information
Transactional Problems [page 167]
## 3.1.5.8 Used Space Much Smaller than Allocated Space
In order to optimize performance by minimizing the memory management overhead or due to fragmentation, SAP HANA may allocate additional memory rather than reusing free space within the already allocated memory.
This can lead to undesired effects that the SAP HANA memory footprint increases without apparent need.
The SAP HANA license checks against allocated space, so from a licensing perspective it is important to keep the allocated space below the license limit.
In order to limit the amount of allocated space, you can set the parameter global_allocation_limit to a value not larger than the maximum memory that should be allocated.
See Set the global_allocation_limit Parameter in the SAP HANA Administration Guide .
## Related Information
Change the Global Memory Allocation Limit
## 3.1.5.9 Fragmentation
Fragmentation effects are responsible for inefficiently used memory. They can occur in different areas.
In order to minimize fragmentation of row store tables you can proceed as follows:
- If the fragmentation of row store tables in the shared memory segments of indexserver processes reaches 30% and the allocated memory size is greater than 10GB, a table redistribution operation is needed.
SAP Note 1813245 - SAP HANA DB: Row Store reorganization describes how to determine fragmentation and perform a table redistribution.
## Related Information
SAP Note 1813245
## 3.1.5.10  Large Memory LOBs
LOB (Large Object) columns can be responsible for significant memory allocation in the row store and column store if they are defined as memory LOBs.
To check for memory LOBs and switch to hybrid LOBs see SAP Note 1994962 - Activation of Hybrid LOBs in SAP HANA .
## Related Information
```
SAP Note 1994962
```
## 3.1.5.11  Large Delta Store
The delta store can allocate a significant portion of the column store memory.
You can identify the current size of the delta store by running the SQL command: HANA_Tables_ColumnStore_Overview ( SAP Note 1969700 - SQL Statement Collection for SAP HANA ). If the delta store size is larger than expected, proceed as described in the section Delta Merge .
## Related Information
```
SAP Note 1969700 Delta Merge [page 129]
```
## 3.1.5.12  Undersized SAP HANA Memory
If a detailed analysis of the SAP HANA memory consumption didn't reveal any root cause of increased memory requirements, it is possible that the available memory is not sufficient for the current utilization of the SAP HANA database.
In this case you should perform a sizing verification and make sure that sufficient memory is installed on the SAP HANA hosts.
## 3.2 CPU Related Root Causes and Solutions
This section covers the troubleshooting of high CPU consumption on the system.
A constantly high CPU consumption will lead to a considerably slower system as no more requests can be processed. From an end user perspective, the application behaves slowly, is unresponsive or can even seem to hang.
Note that a proper CPU utilization is actually desired behavior for SAP HANA, so this should be nothing to worry about unless the CPU becomes the bottleneck. SAP HANA is optimized to consume all memory and CPU available. More concretely, the software will parallelize queries as much as possible in order to provide optimal performance. So if the CPU usage is near 100% for a query execution, it does not always mean there is an issue. It also does not automatically indicate a performance issue.
## 3.2.1  Indicators of CPU Related Issues
CPU related issues are indicated by alerts issued or in views in the SAP HANA cockpit.
The following alerts may indicate CPU resource problems:
- Host CPU Usage (Alert 5)
- Most recent savepoint operation (Alert 28)
- Savepoint duration (Alert 54)
You notice very high CPU consumption on your SAP HANA database from one of the following:
- Alert 5 (Host CPU Usage) is raised for current or past CPU usage
- The CPU usage displayed in the CPU Usage tile on the Overview screen
- The Performance Monitor shows high current or past CPU consumption. Furthermore, the CPU usage of the host as well as the individual servers is displayed.
## 3.2.4  Retrospective Analysis of CPU Related Issues
There are a number of options available to analyze what the root cause of an issue was after it has been resolved.
A retrospective analysis of high CPU consumption should start by checking the Performance Monitor and the Alerts tile in SAP HANA cockpit. Using the alert time or the graph in the Performance Monitor , determine the 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 scripts in the SQL Statement Collection for SAP HANA (attached to SAP Note 1969700):
- HANA_LoadHistory_Services_*
- HANA_Resources_CPUAndMemory_History*
Once a problematic time-period is identified you can proceed with thread sample analysis as described in the section 'Analysis of Former Performance Problems' in HANA System-wide Performance Analysis .
## Monitoring Views and Trace Files
You can search through the trace files of the process causing high consumption if you can narrow down the time period when the event happened. The following views
If you are not able to determine the time frame because the issue happened too long ago, check the following statistics server table which includes historical host resource information up to 30 days:
HOST_RESOURCE_UTILIZATION_STATISTICS (_SYS_STATISTICS schema). A longer history can be found in the statistics server table HOST_DELTA_MERGE_STATISTICS.
With this information, search through the trace files of the responsible process. Be careful to choose the correct host when SAP HANA runs on a scale-out landscape. The information contained in the trace files will give indications on the threads or queries that were running during the affected time frame.
If the phenomenon is recurrent due to a scheduled batch job or data loading processes, turn on the Expensive
Statement Trace during that time to record all involved statements (see Expensive Statements Trace ). Furthermore, check for concurrently running background jobs like backups and Delta Merge that may cause a resource shortage when run in parallel. Historical information about such background jobs can be obtained from the system views:
- m_BACKUP_CATALOG
- m_DELTA_MERGE_STATISTICS
## Related Information
HANA System-wide Performance Analysis [page 46]
The Performance Monitor (SAP HANA Cockpit) Expensive Statement Trace (SAP HANA Cockpit) [page 243] Alerts (SAP HANA Cockpit) SAP Note 1969700 m_BACKUP_CATALOG m_DELTA_MERGE_STATISTICS HOST_DELTA_MERGE_STATISTICS
## 3.2.7  Basic NUMA Analysis
In this section we present a basic workflow for analyzing performance issues, especially on large scale-up systems running SAP HANA.
## Introduction
Server machines with many CPU cores which are packed in multiple sockets today follow a Non-Uniform Memory Architecture (NUMA). A NUMA node comprises the CPU cores that access a set of DRAM DIMMs via a memory controller as a NUMA node. In most systems a NUMA node and a socket refer to the same packaging of these hardware components, see SAP Notes 2470289 and 2632231. Hence, the access to some physical memory address can be to locally attached DRAM DIMMs or DRAM DIMMs attached to another NUMA node. While the hardware hides the complexities of accessing the memory attached to different NUMA nodes, it may still result in different (that is, non-uniform) bandwidth and latency when accessing some memory address.
When SAP HANA is deployed on systems with many NUMA nodes an analysis of the NUMA effects for a given workload may be needed. This topic explains the basic steps for collecting information required for identifying NUMA-related problems. This will be the typical information requested by the SAP HANA quality team when NUMA-related problems are suspected. This topic focuses on the SAP HANA Cockpit as the administration interface because it should be available to every SAP HANA customer. A more detailed analysis may be possible when access to the operating system is available (see also the topic 'Memory Information from Other Tools').
This analysis has the following three sections:
- Collect System Information
- Examine the Workload
- Tuning for Better NUMA Behavior
The analysis should be complemented by a general analysis of performance problems; see related topics in the SAP HANA Performance Guide for Developers .
## Related Information
Indicators of CPU Related Issues [page 83]
Statement Performance Analysis [page 180]
Memory Information from Other Tools [page 73]
SAP HANA Performance Guide for Developers
SAP Note 2470289
SAP Note 2632231
## 3.2.7.3 Tuning for Better NUMA Behavior
This section shows the performance benefits which can be achieved by partitioning tables.
One option to distribute the contention for memory accesses and potentially also CPU consumption across more NUMA nodes is to partition tables. By default, SAP HANA would calculate the NUMA node of the first partition of a table with a hashing scheme and then distribute subsequent partitions on a round-robin basis across the NUMA nodes. In this example we consider a (hash-)range partitioning of the two largest tables as shown below because it is a commonly used partitioning option for TPC-H. As discussed in the FAQ SAP Note 2044468, table partitioning needs to be done with care considering the application characteristics and related workload.
```
ALTER TABLE "TPCH_100GB"."LINEITEM" PARTITION BY HASH (L_ORDERKEY, L_LINENUMBER) PARTITIONS 1, RANGE (L_SHIPDATE) (PARTITION '1992-01-02' <= VALUES < '1993-01-01',
```
```
PARTITION '1993-01-02' <= VALUES < '1994-01-01', … PARTITION '1997-01-02' <= VALUES < '1998-01-01', PARTITION  OTHERS);
```
```
ALTER TABLE "TPCH_100GB"."ORDERS" PARTITION BY HASH (O_ORDERKEY) PARTITIONS 1, RANGE (O_ORDERDATE) (PARTITION '1992-01-02' <= VALUES < '1993-01-01', PARTITION '1993-01-02' <= VALUES < '1994-01-01', … PARTITION '1997-01-02' <= VALUES < '1998-01-01', PARTITION  OTHERS);
```
From the figure below one can see that all columns of the largest table, LINEITEM, are distributed across all four NUMA nodes.
Looking at the distribution of memory consumption for the tables across the NUMA nodes it is evident that NUMA node 3 still has a larger share of data assigned. But overall, one can see a more even distribution of memory allocations.
This analysis is confirmed when looking at the largest table partitions and on which NUMA nodes they are allocated. The report below shows how the tables LINEITEM and ORDERS are now distributed across all NUMA nodes.
In these exemplary measurements the throughput increased by 3%. At the same time, an increase in the CPU time (+2.3%) and peak memory (+20%) could be observed. These effects are due to a higher degree of parallelism when working on the large partitioned tables, and it is also confirmed by looking at the number of active threads in the performance monitor which shows higher values for the average and maximum number of active threads and also average CPU consumption.
Looking at the SQL plan cache we see that the most expensive query - Q9 - has a reduced total execution time. However, for Q10 a higher total execution time is reported indicating some trade-offs when partitioning the tables.
## Related Information
## 3.3.2  Analyze and Resolve Internal Disk-Full Event (Alert 30)
When it is no longer possible to write to one of the disk volumes used for data, log, backup or trace files, the database is suspended, an internal event is triggered, and alert 30 is generated. A disk-full event must be resolved before the database can resume.
## Context
If the disks on which the database data and log volumes are located run full, space on the volumes must be freed or additional space added before the database can resume. In the SAP HANA cockpit, potential disk-full events are displayed in the alerts app.
However, running out of disk space is not the only reason that SAP HANA may be prevented from writing to disk. Other possible causes, all of which lead to a disk-full event and alert 30, include:
- File system quota is exceeded
- File system runs out of inodes
- File system errors (bugs)
##  Note
A number of SAP Notes are available to give troubleshooting advice in specific scenarios. For a log volume full scenario refer first to the note 1679938 - DiskFullEvent on Log Volume to resolve the issue and bring the HANA database back online. Then refer to note 2083715 - Analyzing log volume full situations for root cause analysis to prevent the scenario from happening again.
For log volume full in a replication context refer to LogReplay: Managing the Size of the Log File in this document.
- SAP Note 1679938 - DiskFullEvent on Log Volume.
- SAP Note 2083715 - Analyzing log volume full situations.
- SAP Note 1898460 - How to Handle Alert 30 'Internal disk-full event' .
- SAP Note 1870858 - HANA Alerts related to file system utilization.
## Procedure
1. Analyze disk space usage using the standard administration tools in SAP HANA cockpit: Alerts , Disk Usage , Disk Volume Monitor .
2. Optional: Perform the following steps if helpful:
##  Note
You must execute the commands from the command line on the SAP HANA server.
- a. Determine the file system type:
df -T
- b. Check for disk space using file system specific commands:
- c. Check if the system is running out of inodes (NFS):
| Option   | Description    |
|----------|----------------|
| XFS/NFS  | df             |
| GPFS     | mmfscheckquota |
df -i
- d. Check quota:
| Option   | Description    |
|----------|----------------|
| XFS/NFS  | quota -v       |
| GPFS     | mmfscheckquota |
## Next Steps
The most serious system events (including the disk-full event) are logged as internal database events in the table m_EVENTS (see also Alert 21 which is also triggered when an internal event occurs). They are initially logged with a status of 'NEW' and, once the issue has been resolved (in this case, free up disk space), the event must be set to 'HANDLED' to be able to continue. You can do this by executing the following SQL statements:
- ALTER SYSTEM SET EVENT ACKNOWLEDGED '&lt;host>=:&lt;port>=' &lt;id>=
- ALTER SYSTEM SET EVENT HANDLED '&lt;host>=:&lt;port>=' &lt;id>=
If you cannot track down the root cause of the alert, contact SAP Support.
## Related Information
LogReplay: Managing the Size of the Log File [page 220]