orkungedik/SAP-basis-dataset huggingface dataset:
## 3.5.1  Issues with Configuration Parameter log_mode (Alert 33)
Alert 33 is raised whenever the write mode to the database log is not set correctly for use in production.
## Context
To ensure point-in-time recovery of the database the log_mode parameter must be set to 'normal' and a data backup is required.
The following steps are recommended when facing this alert:
## Procedure
1. Change the value of the parameter log_mode to normal. You can do this in SAP HANA cockpit or from the command line.
2. Schedule an initial data backup
3. Test successful completion of the backup
4. 4.
5. Backup the database configuration For information on how to perform a backup of database configuration files see SAP Note 1651055 Scheduling SAP HANA Database Backups in Linux.
5. Schedule a regular data backup
## Related Information
Alerts Reference [page 302]
```
SAP Note 1651055 SAP Note 1900267
```
## 3.7 Delta Merge
This section covers troubleshooting of delta merge problems.
The column store uses efficient compression algorithms to keep relevant application data in memory. Write operations on the compressed data are costly as they require reorganizing the storage structure and recalculating the compression. Therefore write operations in column store do not directly modify the compressed data structure in the so called main storage. Instead, all changes are at first written into a separate data structure called the delta storage and at a later point in time synchronized with the main storage. This synchronization operation is called delta merge.
From an end user perspective, performance issues may occur if the amount of data in the delta storage is large, because read times from delta storage are considerably slower than reads from main storage.
In addition the merge operation on a large data volume may cause bottleneck situations, since the data to be merged is held twice in memory during the merge operation.
The following alerts indicate an issue with delta merges:
- Delta merge (mergedog) configuration (Alert 10)
- Size of delta storage of column store tables (Alert 29)
## Related Information
SAP Note 1909641
SAP Note 1977314
## 3.7.1  Inactive Delta Merge
In case the delta merge is set to inactive, Alert 10 Delta merge (mergedog) configuration is raised. In a production system this alert needs to be handled with very high priority in order to avoid performance issues.
## Context
Whenever issues with delta merge are suspected, this alert should be checked first. You can do that from the Alerts tile or the Alert Checker Configuration app. An inactive delta merge has a severe performance impact on database operations.
## Procedure
1. Check the current parameter value in the Configuration of System Properties page of SAP HANA cockpit and filter for mergedog .
Check the value of active in the mergedog section of the indexserver.ini file.
2. To correct the value, click Change Layer and choose Restore Default .
This will delete all custom values on system and host level and restore the default value system-wide.
##  Note
Depending on the check frequency (default frequency: 15 minutes) the alert will stay in the Alert inbox until the new value is recognized the next time the check is run.
## Related Information
SAP Note 1909641
Memory Information from Logs and Traces [page 66]
## 3.7.1.1 Retrospective Analysis of Inactive Delta Merge
Retrospective analysis of the root cause of the parameter change that led to the configuration alert requires the activation of an audit policy in SAP HANA that tracks configuration changes.
Other sources of information are external tools (for example, SAP Solution Manager) that create a snapshot of configuration settings at regular intervals.
For details about configuring security auditing and for analyzing audit logs, refer to the SAP HANA Security Guide .
## Related Information
## SAP HANA Security Guide
## 3.7.2  Indicator for Large Delta Storage of Column Store Tables
If the delta storage of a table gets too large, read operations on the table will slow down. This usually results in degraded performance of queries reading from the affected table.
When the delta storage of a table gets too large, the Alert Size of delta storage of column-store tables (Alert 29) can be raised.
Alert 29 is raised when the amount of memory consumed by the delta storage exceeds the configured thresholds. The thresholds can be customized in the SAP HANA cockpit to take into account the configured size of the delta storage. Note that if the alerts are not configured properly, the symptoms can occur without raising an alert, or there may be no symptoms, even though an alert is raised. For each affected table a separate alert is created.
Usually this problem occurs because of mass write operations (insert, update, delete) on a column table. If the total count of records (record count * column count) in the delta storage exceeds the threshold of this alert before the next delta merge, this alert will be triggered.
Corrective action needs to be taken in one of the following areas:
- Change of an application
- Changed partitioning of the table
- Configuration of delta merge
## Related Information
SAP Note 1977314
## 3.7.4  Delta Storage Optimization
Table partitioning allows you to optimize the size of tables in memory and their memory consumption as each partition has its own delta storage.
The memory consumption of a table in memory during a merge operation depends on the number of records, the number and memory size of columns and the memory size of the table. While the number of records can be kept low by triggering a smart merge from the application, optimization with regards to the size of the table can be achieved by table partitioning. This is due to the fact that each partition holds a separate delta storage. When a merge is performed, the data from the main storage has to be loaded into memory which is a considerably less amount when only a single partition is handled rather than the full table.
When considering partitioning it is recommended to analyze the typical usage of this table. Partitions should be created in a way that avoids as much as possible that single statements need to access multiple partitions. If no application knowledge is available, then hash partitioning with a partition size of about 500.000.000 records is a good initial choice.
See, Table Partitioning in the SAP HANA Database in the SAP HANA Administration Guide.
## Related Information
Table Partitioning
## 3.11 License Issues
This section covers license-related issues.
## Related Information
System Locked Due to Missing, Expired, or Invalid License [page 154] License Problem Identification and Analysis [page 154] Resolution of License Issues [page 155]
## 3.11.1  System Locked Due to Missing, Expired, or Invalid License
New installations of SAP HANA are equipped with a temporary license that expires after 90 days. To keep the system functional after this period, you have to install a permanent license.
Improper licensing may lead to a lockdown of your SAP HANA system. In this case, the only allowed action is to install a valid license.
The system goes into lockdown in the following situations:
- The permanent license key has expired and either:
- You did not renew the subsequently installed temporary license key within 28 days, or
- You did renew the subsequently installed temporary license key but the hardware key has changed
- The installed license key is an enforced license key and the current memory consumption exceeds the licensed amount plus the tolerance.
- You deleted all license keys installed in your database.
For more information, see Managing SAP HANA Licenses in the SAP HANA Administration Guide .
## Related Information
Managing SAP HANA Licenses
## 3.11.2  License Problem Identification and Analysis
The first signs of problems related to licensing will be visible by Alert 31 or Alert 44 being issued.
You can check your current license using the SAP HANA cockpit: Choose the Manage system licenses link on the Overview page.
Alternatively, you can retrieve the same information using SQL:
SELECT * FROM m_LICENSE;
##  Note
To be able to query license information, you must have the system privilege LICENSE ADMIN.
The m_LICENSE system view includes the following information:
- License data:
- SID
- Hardware key
- Installation number
- System number
- Product limit (licensed amount of memory)
- Validity start date
- Expiration date - See SAP Note 1899480 - How to handle HANA Alert 31: 'License expiry'
- Last successful check date
- License status (permanent, valid, enforced, local) See SAP Note 1899511 - How to handle HANA Alert 44: 'Licensed Memory Usage'
Note that in case of system lockdown, only SID and hardware key are displayed. Information on previously installed licenses is available.
SAP HANA licenses can be installed for the system database (global) or for a single tenant database (local). Global licenses are for the system database and all tenants but a license installed in a tenant will govern only the tenant. If the license is installed incorrectly at the local level you can remove the tenant-specific license key to revert the license validity to the global license key installed in the system database.
## Related Information
```
M_LICENSE System View SAP Note 1899480 SAP Note 1899511
```
## 3.11.3  Resolution of License Issues
If your license becomes invalid, you need to install a new license.
You can install a new license either in the SAP HANA cockpit or using SQL.
##  Note
To install a license key, you need the LICENSE ADMIN system privilege.
You install a license key with the following SQL statement:
SET SYSTEM LICENSE '&lt;license file content goes here, line breaks matter>=';
##  Note
Line breaks are essential for interpretation of the license key text, hence they must not be removed. If you use the command line tool SAP HANA HDBSQL to install the license, make sure to enable multi-line statement support (command line option -m or \mu ON when within SAP HANA HDBSQL).
The command will fail if the license key has a different installation number or system number than the current ones in the database instance. If you have successfully installed a license but your system is still locked down, check the following:
- The current system time is within the validity period of the license.
- Your installed license key is correct, in particular, the m_LICENSE view displays only one row with a valid license for the product SAP HANA.
- The SAP Notes in the Related Links section.
For more detailed information about how to install a license key, see the SAP HANA Administration Guide .
## Related Information
SAP Note 1704499
SAP Note 1634687
SAP Note 1699111
Managing SAP HANA Licenses
Run Long Commands in Multiple-Line Mode
## 3.12 Security-Related Issues
This section looks at issues related to authorization and authentication.
## Related Information
Troubleshooting Authorization Problems [page 156]
Troubleshooting Problems with User Name/Password Authentication [page 162]
Troubleshooting Problems with User Authentication and SSO [page 164]
## 3.12.1  Troubleshooting Authorization Problems
SAP HANA implements its authorization concept based on the entities user, privilege, and role.
## General Analysis
The system view EFFECTIVE_PRIVILEGES is useful for checking the privileges of a specific user. It includes information about all privileges granted to a specific user (both directly and indirectly through roles), as well as how the privileges were obtained (GRANTOR and GRANTOR_TYPE column).
Output of Effective Privileges
Examples of querying this view can be found in the topics which follow:
- Troubleshoot the Display of Unrestricted or Incorrect Results for a View Secured with Analytic Privileges
- Troubleshoot the Error "Insufficient privilege: Not authorized" Although User Has Analytic Privileges
- Troubleshoot the Error "Invalidated View" During SELECT Statement Execution
Full details of this view are given in the SAP HANA SQL Reference Guide .
For more information about other system views related to authorization, see System Views for Verifying Users' Authorization in the Security Administration and User Management section of the SAP HANA Administration Guide .
For more information about the authorization concept in SAP HANA, see the SAP HANA Security Guide .
## Insufficient Privilege Errors
To avoid the need to search through the indexserver trace files to analyze insufficient privilege errors, a procedure is available which you can use to quickly find out details of missing privileges: GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS().
The insufficient privilege error message includes a GUID value, for example:
```
insufficient privilege: Detailed info for this error can be found with guid '3DFFF7D0CA291F4CA69B327067947BEE'
```
Use this value as a parameter for the stored procedure:
The procedure returns details of the missing privilege with the user name and the session user name. Object details may also be returned if available, and if the missing privilege is contained in one or more roles then the roles are displayed.
The procedure does not return information about analytical privileges.
For more information about this procedure, see 'Resolve Insufficient Privilege Errors' in the SAP HANA Administration Guide (Security Administration and User Management) and the corresponding topic in the SAP HANA Cockpit Guide , 'Display Information about an Insufficient Privilege Error'.
## Authorization Dependency Viewer
SAP HANA Studio and SAP HANA cockpit include a graphical tool, the Authorization Dependency Viewer, to analyze authorization issues. This may be helpful to troubleshoot authorization errors and invalid object errors for stored procedures and calculation views with complex dependency structures. See the links under 'Related Information'.
## Related Information
System Views for Verifying Users' Authorization EFFECTIVE_PRIVILEGES System View SAP HANA Security Guide Display Information about an "Insufficient Privilege" Error Resolve Insufficient Privilege Errors Resolve Errors Using the Authorization Dependency Viewer (SAP HANA Studio) Resolve Object Authorization Errors (SAP HANA cockpit)
## 3.12.1.1  Troubleshoot the Display of Unrestricted or Incorrect Results for a View Secured with Analytic Privileges
If a user has unrestricted access to a view or sees results that he should not, even though he has been granted an analytic privilege, you need to determine which privileges have been granted to the user and whether or not they are correct.
## Prerequisites
To troubleshoot this issue, you require the following system privileges:
- CATALOG READ
- TRACE ADMIN
## Procedure
- Check which analytic privileges have been granted to the user using the system view EFFECTIVE_PRIVILEGES.
Execute the following SQL statement:
SELECT * FROM EFFECTIVE_PRIVILEGES WHERE USER_NAME = 'myuser' AND OBJECT_TYPE = 'ANALYTICALPRIVILEGE';
In particular, verify that the user does not have the analytic privilege _SYS_BI_CP_ALL. This analytic privilege potentially allows a user to access all the data in all activated views, regardless of any other analytic privileges that apply. Usually, the user will have this analytic privilege through a role, for example, MODELING.
##  Caution
The MODELING role is very privileged and should not be granted to users, particularly in production systems. The MODELING role should only be used as a template.
- Identify wrong filters specified in the analytic privileges granted to the user. Information about filter conditions generated from the relevant analytic privileges can be traced in the indexserver trace file. This can help you to identify wrong filters specified in the analytic privileges granted
- to the user. In the Trace Configuration monitor of the SAP HANA database explorer, set the database trace level for the
component analyticprivilegehandler of the indexserver service to DEBUG .
## Related Information
EFFECTIVE_PRIVILEGES System View System Views for Verifying Users' Authorization
## 3.12.1.2  Troubleshoot the Error "Insufficient privilege: Not authorized" Although User Has Analytic Privileges
Even if a user has the correct analytic privileges for a view, he still may receive the error Insufficient privilege: Not authorized if there is an issue with privileges at another level.
## Prerequisites
To troubleshoot this issue, you require the following system privileges:
- CATALOG READ
## · TRACE ADMIN
## Procedure
- Verify that the _SYS_REPO user has all required privileges (for example, SELECT) with GRANT OPTION on the base tables of the view.
You can do this by selecting from the EFFECTIVE_PRIVILEGES system view:
```
SELECT * FROM EFFECTIVE_PRIVILEGES WHERE USER_NAME = '_SYS_REPO';
```
- Verify that the analytic privileges required for any underlying views have been granted to the user.
- If the view is a top-level view (calculation view) with underlying views, the granted analytic privilege grants access only to this top-level view. Analytic privileges are required for all underlying views. Note that analytic privileges have to contain at least a view attribute with or without filter condition in order to grant access to the view.
You can verify a user's privileges by selecting from the EFFECTIVE_PRIVILEGES system view:
```
SELECT * FROM EFFECTIVE_PRIVILEGES WHERE USER_NAME = '<user>' AND OBJECT_TYPE = 'ANALYTICALPRIVILEGE';
```
- If the analytic privilege uses a database procedure to define dynamic value filters at runtime, check for errors in the execution of the underlying procedure.
To find out the actual error during procedure execution for analytical privileges, check the indexserver_alert_&lt;host>=.trc trace file (accessible in the SAP HANA database explorer via the View trace and diagnostic files link in the SAP HANA cockpit).
## 3.12.1.3  Troubleshoot the Error "Invalidated View" During SELECT Statement Execution
A user may receive the error Invalidated view when executing a SELECT statement against a view that was activated from the repository. Additionally, the data preview for an activated view may not show any data.
## Prerequisites
To troubleshoot this issue, you require the CATALOG READ system privilege.
## Context
In addition to the privileges checks described here, other possible causes of invalidated views are described in knowledge base articles. The cause may be related, for example, to the view not being active or the view may
have been changed and is now unavailable. The KBAs listed below describe troubleshooting procedures in the context of SAP HANA Studio, but the causes and solutions may still be relevant in any context.
## Procedure
- Verify that the _SYS_REPO user has all required privileges (for example, SELECT, SELECT WITH GRANT OPTION) on all base objects of the view (for example, tables) and schemas used.
You can do this by selecting from the EFFECTIVE_PRIVILEGES system view:
```
SELECT * FROM EFFECTIVE_PRIVILEGES WHERE USER_NAME = '_SYS_REPO';
```
- To grant this privilege you can execute the following statement:
```
GRANT SELECT ON SCHEMA <YOUR_SCHEMA> TO _SYS_REPO WITH GRANT OPTION;
```
This example shows GRANT SELECT ON SCHEMA, you can also grant the privilege on tables or views.
- Verify that the user activating the object has at least SELECT privilege over the schemas used within the scenario.
To grant this privilege you can execute the following statement:
```
GRANT SELECT ON SCHEMA <YOUR_SCHEMA> TO <USER_THAT_WILL_ACTIVATE_ANALYTIC_OBJECT>;
```
- Make sure all the dependencies of the analytic object to be activated are contained in the analytic privilege assigned to the user that will actually activate the main analytic object.
## Results
For more information on the GRANT Statement refer also to the details in the SAP HANA SQL Reference Guide for SAP HANA Platform .
## Related Information
GRANT Statement (Access Control)
## Links to Knowledge Base Articles:
SAP Note 2318731 - Error "SAP DBTech JDBC: [391]: invalidated view: sap.hba.ecc" during data preview SAP Note 3048185 - Error "SAP DBTech JDBC: [391]: invalidated view:" error occurs when add object
```
privilege SAP Note 2985592 - Database returned the SQL code 391. Error text: invalidated view SAP Note 1761917 - Error when executing an Analytic Object
```
## 3.12.2  Troubleshooting Problems with User Name/Password Authentication
Common problems with regards to authentication are related to incorrect or expired passwords.
User administrators can change users' passwords on the Edit User monitor in the Cockpit Manager of the SAP HANA cockpit.
For more information about managing users, see Security Administration and User Management and Reset the SYSTEM User Password of a Tenant Database in the SAP HANA Administration Guide .
## Related Information
Security Administration and User Management Reset the SYSTEM User Password in a Tenant Database (SAP HANA Cockpit)
## 3.12.2.1  Resetting the Password for the SYSTEM User
If the password of the SYSTEM user is irretrievably lost, it can be reset.
Follow the procedure as described in the SAP HANA Administration Guide .
##  Note
If you can log on as SYSTEM and you want to change the password, do not use the emergency reset procedure. Simply change the password directly using the SAP HANA cockpit or the ALTER USER SQL statement: ALTER USER SYSTEM PASSWORD new_password .
## Related Information
Resetting the SYSTEM User Password (SAP HANA Cockpit)
## 3.12.2.2  Troubleshoot the Error "User is locked"
A user receives the error User is locked after too many failed log on attempts.
## Prerequisites
You have system privilege USER ADMIN.
## Context
An example of this error might look like:
Error "user is locked; try again later: lock time is 1440 minutes; user is locked until 2014-05-28 21:42:24.12214212" (the time is given in UTC).
Most likely, the user logged on too many times with the wrong password. The default maximum number of failed logon attempts is 6. This is defined by the password policy parameter maximum_invalid_connect_attempts .
For more information about this and other password policy parameters, see Password Policy Configuration Options in the SAP HANA Security Guide .
## Procedure
Reset the invalid connect attempts with the following SQL statement:
```
ALTER USER <user> RESET CONNECT ATTEMPTS;
```
The user can now log on again.
## Related Information
Password Policy Configuration Options
## 3.12.3  Troubleshooting Problems with User Authentication and SSO
Authentication problems manifest themselves as failed user logon. In many cases, the reason for the failure will not be clear to the user. You need to analyze the database trace to determine the cause of the problem.
For security reasons, no information about error conditions are provided to a user directly after a failed logon attempt, since this could be abused by attackers. In case of authentication problems, the affected user must contact the system administrator, who will then analyze the database trace on the server side.
## Tracing for SSO Issues
Logon information is available in the database trace, but by default, it does not log much. The first step in troubleshooting any SSO logon issue therefore is to increase the trace level of the authentication-related components of the database trace. You can do this in the SAP HANA cockpit / Database Explorer or in SAP HANA Studio on the Database Trace section of the Trace Configuration tab.
For problems with JDBC/ODBC-based logon, increase the trace level of the authentication for the index sever to DEBUG .
For problems with HTTP-based logon via SAP HANA XS classic, increase the trace level of the authentication , xssession , and xsauthentication components for the xsengine server to DEBUG .
Once you have increased tracing, reproduce the problematic logon. The traces will now contain more descriptive error messages.
##  Remember
After completing troubleshooting, reduce the authentication trace level back to the default.
In some cases, especially for Kerberos and SPNEGO, it is necessary to use other sources of tracing, such as:
- JDBC, ODBC or SQLDB trace
- Windows event log
- Debugger of browser
- SAP Web dispatcher trace
- Network packet sniffer, for example, Wireshark
For more information about tracing in SAP HANA see the section on traces in the SAP HANA Administration Guide and SAP Note 2119087.
##  Tip
Guided Answers is a support tool for troubleshooting problems using decision trees. A guided answer is available for tracing SSO issues.
## Related Information
Traces Single Sign-On Integration
Kerberos-Related Authentication Issues [page 165]
SAML Authentication [page 166] Traces for SSO Troubleshooting (Guided Answer) SAP Note 2119087
## 3.12.3.2  SAML Authentication
## User cannot connect with SAML assertion
If a user cannot connect to SAP HANA with a SAML assertion, the issuer and subject distinguished names (DNs) in the SAML assertion do not match those configured in the identity provider. Investigate which issuer and subject DNs were used in the SAML assertion. You will find them in the trace file indexserver_alert_&lt;hostname>=.trc . Compare these with those configured in the service provider.
## Useful SAP Notes
|   SAP Note | Description                                         |
|------------|-----------------------------------------------------|
|    1766704 | How to use Fiddler to collect HTTP and HTTPS traces |
|    2284620 | HOW-TO HANADBSSOSAMLandBIPlatform 4.1 / AO2.2       |
##  Tip
Guided Answers is a support tool for troubleshooting problems using decision trees. A guided answer is available for SAML authentication with SAP HANA.
## Related Information
Single Sign-On Using SAML 2.0 SAML Authentication for Single Sign-On (Guided Answer)
## 3.13 Transactional Problems
This section covers troubleshooting of transaction problems. Symptoms seen from an end user perspective may include an application running sluggishly, or not responding or even seeming to hang. These problems may be caused by issues with uncommitted transactions, long-lived cursors blocking garbage collection, a high number of active versions or blocked transactions.
## Maximum Transaction Limit
The default system limit for the maximum number of transactions is 64000. If this limit is exceeded, an error message may be returned when submitting queries: "exceed maximum number of transactions". This may be caused by application programs which are not correctly managing connections/transactions, or a bug within the database engine. This scenario and possible workarounds are described in detail in SAP Note 2368981 SAP HANA DB: Queries fail with error "exceed maximum number of transaction" .
## Related Information
## SAP Note 2368981
## 3.13.1  Blocked Transactions
Blocked transactions are write transactions that are unable to be further processed because they need to acquire transactional locks (record or table locks) which are currently held by another write transaction.
This section focuses on blocking caused by transaction locks though transactions can also be blocked waiting for physical resources like network or disk. A basic general analysis step is to determine if few transactions are blocked or many and this section deals with these two aspects separately.
Administration tools for analyzing the cause of blocked transactions are available in SAP HANA Cockpit and SAP HANA Studio. The analysis steps you take depend firstly on whether the blocking is current or whether it happened in the past. Monitoring views (named m_*) provide information about the current state of the database and the corresponding set of HOST_ views include data from persisted history information, for example:
m_BLOCKED_TRANSACTIONS (current data)
HOST_BLOCKED_TRANSACTIONS (historical data)
The SAP Note 1858357 - HANA DB: Analysis of Wait Situations describes two locking scenarios covering both current and historical lock situations.
The SQL Statement Collection for SAP HANA available with SAP Note 1969700 includes scripts which can be executed to provide a basic analysis. The following scripts can be customized to search current or historical data:
- HANA_Locks_Transactional_LockWaits*
- HANA_Locks_Transactional_LockWaits_Hierarchy* - shows the relationship between dependent transactions
The output of these scripts includes essential information about the type of lock, its duration, transaction ID and information about the lock holder. See also the script HANA_Locks_Transactional_LockWaits_PerObject.
For more information on lock analysis refer to the SAP Note 1999998 - FAQ: SAP HANA Lock Analysis
## Lock Timeout
Transactional lock waits automatically terminate after a predetermined time period - by default this is 30 minutes (1800000 ms). The timeout period is defined in the following parameter: lock_wait_timeout in the [transaction] section of the inexserver.ini file. If a blocked transaction times out it is rolled back with error: SQL error 131: transaction rolled back by lock wait timeout . The value of this parameter is monitored and alert 16 is generated if the value is outside the range of 100,000ms to 7,200,000ms.
## Related Information
```
Identify and Assess Blocked Transaction Issues [page 171] Single or Few Transactions are Blocked [page 173] Many Transactions are Blocked [page 174] Troubleshooting Blocked Transaction Issues that Occurred in the Past [page 175] SAP Note 1969700 SAP Note 1999998 SAP Note 1858357
```
## 3.13.1.2  Identify and Assess Blocked Transaction Issues
The first signs of blocked transactions may be poor application response or alerts 49 or 59 are raised.
The initial indicators of blocked transactions are often given by:
- Users reporting bad application responsiveness
- Alert 49 - Long-running blocking situations
- Alert 59 - Percentage of transactions blocked
To confirm that poor database performance is caused by blocked transactions, you should check the following performance monitoring tools in SAP HANA cockpit:
## Performance Monitor
On the Performance Monitor you can enable the KPI Blocked Transactions to plot the occurrence of any blocked transactions in the current time frame.
## Workload Analysis Monitor
Also on the Performance Monitor, look at the Background Jobs tab which shows currently running SAP HANA background processes like Delta Merge. Since the Delta Merge needs to lock tables to proceed, it is a common cause for blocked transactions. Another background job displayed by this monitor is the savepoint write job which requires a global database lock in its critical phase. The Savepoint Performance topic gives more details about analyzing and troubleshooting the locking phases of this job.
The following views may help to analyze delta merge:
- SYS.m_DELTA_MERGE_STATISTICS
- _SYS_STATISTICS.HOST_DELTA_MERGE_STATISTICS - for historical information.
The following views may help to analyze and check if a savepoint was written during the time of the blockage:
- SYS.SAVEPOINTS
- _SYS_STATISTICS.HOST_SAVEPOINTS - for historical information.
## Sessions Monitor
The Sessions tile and monitoring tool lists all currently opened SQL sessions (meaning user connections). In the context of blocked transaction troubleshooting, the columns Blocked By Connection ID and Blocks No. Of Transactions are of special interest. The first tells you whether the session is blocked by another session and identifies the ID of the blocking one. The latter gives you the corresponding information if a session blocks other sessions, and how many transactions are affected.
## Blocked Transactions Monitor
The Open blocked transactions link on the Monitoring tile is the next drill down step. The display only lists those transactions that are currently blocked. The ordering is done via a blocking/blocked relation. That means transactions that are blockers are highlighted. Directly beneath the blocked transactions are displayed. In the following illustration you can see transaction 46 blocking multiple other transactions:
## Blocked Transactions Monitor
See also Blocked Transactions in the SAP HANA cockpit documentation.
## Threads Monitor
The Threads monitor (available from the Threads tile) gives the most fine-grained view of the current situation by listing all threads currently active in the system, although it is usually not necessary to drill into that level of detail. See also the Threads topic in the SAP HANA cockpit documentation.
## Related Information
## Savepoint Performance [page 118]
## Links to SAP HANA Cockpit Guide:
The Performance Monitor
Blocked Transactions Session Monitoring
Threads
SAP Note 2079396
SAP Note 2081856
## 3.13.1.3  Single or Few Transactions are Blocked
If you identified only a single or a few blocking transactions, there is likely an issue on application side.
## Check the Application
A usual pattern is a flaw in the application coding that does not commit a write transaction. Such a transaction will be a blocker for any other transaction that needs to access the same database object. To release the situation you have to close the blocking transaction.
There are several possibilities to achieve this:
- Contact the Application User
You can identify the user of the application via the Sessions tile. This information is visible in the 'Database User' column or, in case the application has its own user management (for example, SAP BW), in the 'Application User' column. Contact the user and ask them to close the application.
- Contact the Application Developer
As a follow-up, the author of the application should be contacted to discuss how such situations can be avoided in the future by changing the application code.
## Cancel the Session
If you are not able to contact the user to cancel the session, you can also cancel the session from the list of sessions accessed via the Sessions tile. The session cancellation may take some time to complete, but after canceling the session the current transaction will be rolled back.
## Kill the Client Application
In case the session cancellation takes too long or does not complete at all, you can kill the client process that opened the session. This will terminate the blocking transaction as well. Note that killing the client application is safe from a database consistency standpoint, the current transaction will be rolled back gracefully.
To do this you have to have access to the client machine. The information needed for this task can be retrieved from the Sessions tile. See columns &lt;Client IP>= and &lt;Client Process ID>= to determine the host and process to be killed.
## 3.13.1.4  Many Transactions are Blocked
In the case that a large number of transactions are blocked, the troubleshooting should take a slightly different approach.
First you need to determine whether there is a single or few blocking transactions that block a large amount of other transactions. For this, open the Blocked Transactions monitor and check the amount of blocking transactions. If you determine that there are only a few blocking transactions, use the techniques described in Single or Few Transactions are Blocked to resolve the situation.
If there are many transactions in a blocking state, you need to find out whether a specific access pattern causes the situation. In cases where multiple transactions try to write to the same database objects they block each other. To check if this situation exists, open the Blocked Transactions monitor and analyze the 'Waiting Schema Name' , 'Waiting Object Name' and 'Waiting Record Id' columns. If you find a fair amount of blocking transactions that block many other transactions you need to investigate if the following is possible:
- Change the client applications to avoid the access pattern
- If a background job is running that issues many write transactions (for example, a data load job) then reschedule the job to a period with a low user load
- Partition tables that are accessed frequently to avoid clashes. See the SAP HANA Administration Guide for more details on partitioning.
In case you cannot identify specific transactions or specific database objects that lead to transactions being blocked, you have to assume a problem with the database itself or its configuration. One example is an issue with long savepoint durations - see Savepoint Performance for troubleshooting such issues.
## Related Information
Single or Few Transactions are Blocked [page 173] Savepoint Performance [page 118] Table Partitioning
## 3.13.1.5  Troubleshooting Blocked Transaction Issues that Occurred in the Past
Finding the root cause of blocked transaction situations that occurred in the past and which you have resolved is more difficult than troubleshooting issues that are currently happening. Tools such as the Performance Monitor , system views and the SQL Plan Cache are available to help you.
Using SAP HANA cockpit you can firstly use the Performance Monitor to set the time frame covering the period where the issue happened. Using the information returned, investigate what happened at this specific point in time.
You should check the monitoring and Statistics Server view
_SYS_STATISTICS.HOST_BLOCKED_TRANSACTIONS. Analyze the columns 'WAITING_SCHEMA_NAME' ,
'WAITING_TABLE_NAME' and 'WAITING_RECORD_ID' to identify the database objects that lead to blocked transactions.
In addition, the SAP HANA cockpit SQL Plan Cache monitor (available on a tab page from the SQL Statements tile) may be able to provide information about the statements that were involved in the situation. You may need to change the selection of visible columns to see the relevant information and then use the sort feature to search for and identify the relevant entries. For entries that have 'TOTAL_LOCK_WAIT_COUNT' >= 0 compare the column 'MAX_CURSOR_DURATION' against 'AVG_CURSOR_DURATION' . An entry where there is a significant difference (where the transaction took much longer than average) may indicate that it was involved in the situation.
The SQL Statement Collection for SAP HANA available with SAP Note 1969700 includes scripts which can be customized to search historical data (see the topic Blocked Transactions).
## Related Information
The Performance Monitor Monitor and Analyze Statements with SQL Plan Cache Blocked Transactions [page 167]
## 3.13.2  Multiversion Concurrency Control (MVCC) Issues
In this section you will learn how to troubleshoot issues arising from MVCC.
Multiversion Concurrency Control (MVCC) is a concept that ensures transactional data consistency by isolating transactions that are accessing the same data at the same time.
To do so, multiple versions of a record are kept in parallel. Issues with MVCC are usually caused by a high number of active versions. Old versions of data records are no longer needed if they are no longer part of a snapshot that can be seen by any running transaction. These versions are obsolete and need to be removed from time to time to free up memory.
This process is called Garbage Collection (GC) or Version Consolidation. It can happen that a transaction is blocking the garbage collection. The consequence is a high number of active versions and that can lead to system slowdown or out-of-memory issues.
## 3.13.2.1  Row Store Tables
Garbage collection is triggered after a transaction is committed and also periodically (every hour by default).
A transaction that is currently committing can be identified in the Threads tile (see System Performance Analysis ). The Thread Type will be 'SqlExecutor' and the Thread Method 'commit' .
The periodic garbage collection can be identified by Thread Type 'MVCCGarbageCollector' .
Note that the periodic garbage collection interval can be configured in the indexserver.ini file transaction section with the parameter gc_interval .
## Related Information
System Performance Analysis [page 237]
## 3.13.2.2  MVCC Problem Identification
You can check for a number of indicators of MVCC problems.
Problems with a high number of active versions can be identified by:
- users reporting an increase of response times
- the indexserver trace containing "There are too many un-collected versions. The transaction blocks the garbage collection of HANA database."
- checking Active Versions in the Performance Monitor
Transactions blocking garbage collection can originate from:
- Long-running or unclosed cursors
- Long-running transactions with isolation mode 'serializable' or 'repeatable read'
- Hanging threads
In order to validate that there is a problem with MVCC, check the number of active versions in the row-store MVCC manager monitoring view (m_MVCC_TABLES). Note that in a multihost environment, you have to check the master host.
select * from m_mvcc_tables where host='MyHost' and port='30003' and (name='NUm_VERSIONS' or name='MAX_VERSIONS_PER_RECORD' or name='TABLE_ID_OF_MAX_NUm_VERSIONS');
## MVCC Information on a Healthy System
|    | PORT    |    |   VALUE |
|----|---------|----|---------|
| 1  |         |    |    7535 |
|    |         | 2  |    2428 |
| 3  | 30 .003 |    |  131188 |
If the number of active versions (NUm_VERSIONS) is greater than eight million, it is considered a problem and an overall slowdown of the system can be experienced. Similarly, if the maximum number of versions per record (MAX_VERSIONS_PER_RECORD) exceeds 8,000,000, this should be treated as a problem and a slowdown of accesses to a specific table is expected. Use TABLE_ID_OF_MAX_NUm_VERSIONS and join it against the SYS.TABLES system view to determine the table which is having the problem.
## Related Information
Performance Trace [page 289]
## 3.13.2.3  Analysis of MVCC Issues
You have to find out which transactions are blocking the garbage collection and to which connection they are related.
The following queries will return the transaction that may block the garbage collection. You have to check both.
```
SELECT top 1 host, port, connection_id, transaction_id, update_transaction_id, primary_transaction_id, transaction_type, isolation_level FROM M_TRANSACTIONS WHERE MIN_MVCC_SNAPSHOT_TIMESTAMP > 0 order by min_mvcc_snapshot_timestamp ASC; SELECT top 1 host, port, connection_id, transaction_id, update_transaction_id, primary_transaction_id, transaction_type, isolation_level FROM M_TRANSACTIONS WHERE MIN_MVCC_SNAPSHOT_TIMESTAMP = (SELECT MIN(VALUE) FROM M_MVCC_TABLES WHERE NAME = 'MIN_SNAPSHOT_TS') order by min_mvcc_snapshot_timestamp ASC; HOST PORT (ONNECTION_ID TR-NSACTION_ID UPD-TE_TRANS PRIMARY_TRANS TRANSACTION_TYPE ISOLATION_LEVEL 278
```
User Transaction Possibly Blocking Garbage Collection
In case of a user transaction being the candidate (TRANSACTION_TYPE='USER TRANSACTION'), you can directly determine the connection ID the transaction belongs to (see an example in the figure above).
External Transaction Possibly Blocking Garbage Collection
If the candidate's transaction type is 'EXTERNAL TRANSACTION' , use the following query to find out which other transaction spawned the candidate and determine its connection ID.
```
SELECT t.connection_id AS "Kill this connection id", t.transaction_id AS "Belonging to user transaction id", e.transaction_id AS "To get rid of external transaction id" FROM m_transactions t JOIN m_transactions e ON e.primary_transaction_id = t.transaction_id AND e.volume_id = t.volume_id
```
## 3.13.2.4  Solution of MVCC Issues
Solving MVCC issues is similar to solving blocked transaction issues. Use the following approaches in the given order for transactions where you know the connection ID.
1. Contact the user to stop his activity.
2. Cancel the statement/cancel the internal transaction.
3. Cancel the connection.
4. Kill the client application.
##  Note
There is no guarantee that these measures will stop a transaction which blocks the garbage collection. If necessary contact development support to get further help.
## Related Information
Resolving CPU Related Issues [page 85]
## 3.14 Statement Performance Analysis
This section gives an overview of issues and solutions concerning SQL statement performance.
For more information about optimizing and fine-tuning queries refer to the SAP HANA Performance Guide for Developers which includes sections such as:
## Ongoing Development of SQL Query Processing Engines
Starting from HANA 2 SPS 02, two new processing engines to execute SQL queries are being phased in to SAP HANA:
- Extended SQL Executor (ESX)
- HANA Execution Engine (HEX)
The functionality of the product remains the same but these engines offer better performance.
In the SPS 02 release these engines are active by default (no configuration is required) and are considered by the SQL optimizer during query plan generation. In the SPS 02 release, the focus of the HEX engine is on queries that are typical in OLTP scenarios. Queries that are not supported by HEX or where an execution is not deemed beneficial are automatically routed to the standard engine.
If necessary (for example, if recommended by SAP Support), you can set configuration parameters to completely disable these engines. Each engine has a single parameter which can be switched to disable it:
| File            | Section   | Parameter   | Value                                  | Meaning                        |
|-----------------|-----------|-------------|----------------------------------------|--------------------------------|
| indexserver.ini | sql       | esx_level   | Default 1, set to 0 to disable.        | Extended SQL executor enabled. |
| indexserver.ini | sql       | hex_enabled | Default True, set to False to disable. | HANAexecution engine enabled.  |
These engines should not be disabled permanently because they are being actively developed and improved in each release.
## Query Hints
Further control of how these engines are used is available (from SPS 02.01) using hints with queries to explicitly state which engine should be used to execute the query. For each engine two hint values are available to either use or completely ignore the engine. The following table summarizes these and is followed by examples:
| Hint value      | Effect                                                                  |
|-----------------|-------------------------------------------------------------------------|
| USE_ESX_PLAN    | Guides the optimizer to prefer the ESX engine over the standard engine. |
| NO_USE_ESX_PLAN | Guides the optimizer to avoid the ESX engine.                           |
| USE_HEX_PLAN    | Guides the optimizer to prefer the HEX engine over the standard engine. |
| NO_USE_HEX_PLAN | Guides the optimizer to avoid the HEX engine.                           |
## Example
```
SELECT * FROM T1 WITH HINT(USE_ESX_PLAN); SELECT * FROM T1 WITH HINT(NO_USE_HEX_PLAN);
```
Similar hints are available for the OLAP engine as described later in this section: Using Hints to Alter a Query Plan .
## Related Information
Using Hints to Alter a Query Plan
## 3.14.1  SQL Statement Optimization
This section provides an overview of tools, traces and SAP HANA cockpit areas that can be used to identify critical SQL statements.
SQL statements that are not executed efficiently can cause local and system-wide problems. The most critical are the following areas:
- A long runtime can result in delays of the business activities
- A high CPU consumption can lead to system-wide CPU bottlenecks
- High memory consumption can be responsible for out-of-memory situations and performance penalties due to unload of tables from memory
SQL statements consuming significant resources are called expensive SQL statements.
## Identification of Critical SQL Statements
A key step in identifying the source of poor performance is to understand how much time is spent in the SAP HANA engine for query execution. By analyzing SQL statements and calculating their response times, you can better understand how the statements affect application and system performance.
Before you are able to analyze and optimize a SQL statement, you have to identify the critical SQL statements. We can distinguish between the following scenarios:
- SQL statements that have caused problems in the past
- SQL statements that are currently causing problems