Very very long queries can occur in sap hana. There are various reasons why a query could take longer to execute than normal.
It could be due to bad query execution plan which would deteriorate the query performance and make it run for ages each time you execute it. On the other hand, long queries could be related to resource starvation because of too many transactions running at the same time. A query may be stuck within an infinite deadlock waiting for a manual action to kill one of the blocked sessions. There are few other examples where a query performance could deteriorate. We are going to look at why, but also at what to do in each case.
Blocked transactions are queries that have come to a standstill. They executions are frozen. Each query requires to acces data which is locked by another transaction. Transactions are simply waiting for locks to be released.
How to respond to the alert "Transaction < X > has been blocked by transaction < Y > for more than < N > minutes" ?
Step by Step investigation questions.
1. Is the hana alert locking problem related to one or to many transactions?
2. Are performance issues coming from a general hana system degradation?
3. Find out about any blocked transaction details
4. Find out about lockholder details
5. Is the hana error occuring during a particular time frame?
6. How the sap hana locked event has been released?
Normally or killed by the hana system.(parameter grace time)
7. Has the sap hana waiting event been resolved quickly enough?
8. Is there a sap hana deadlock holding several transactions?
Important Note :
A frozen transaction could also be due to an external event such as Full FS or Disk Quota limit reached
: Do not stop and try to restart your hana system without checking those particular issues.
How to check whether a lock alert is related to one or to several sap hana transactions?
The first step would be to look at the transaction locking level.
Open Hana studio and check if many transactions are actually waiting or not :
[ Administration console, performance tab, sessions tab ]
A scenario of a queue of transactions waiting for one transaction to finish and released its locks.
Getting details of sessions and transactions.
Getting the list of transaction queries and their status
Above example in : https://help.sap.com/viewer/bed8c14f9f024763b0777aa72b5436f6/2.0.02/en-US/9938ac9f1a0541a4b8d6db08c61a5c95.html
Alternatively use the mini check queries provided by SAP :
[ Administration console, System Information tab, Select "System", Double click "Blocked transactions" ]
How to check whether poor performance query is due to an internal sap hana system issue?
You may have noticed no specific waiting alert message at the transaction level. No particular session or transaction is waiting for any other transaction.
It is time to investigate to other areas where locks and waiting events can also interfere with user query performance.
Your system may be frozen because of :
1. A log backup FS full.
A Full FS means that your log backup procedure is not working. Full FS event may happened suddenly even if you have a well tuned FS monitoring system in place. For ex : Monitoring may be disrupted because of a very slow environment and events would not be recorded and reported on time. Log backup could be created so fast that you did not have time to react to avoid the log backup area to fill up.
See Also : How to resolve disk or log backup full FS in sap hana?
2. A disk or a FS full.
How to check sap hana disk usage at the operating system level?
Here are simple commands to check for disk space availability :
for XFS/NFS : linux$> df -k
for GPFS ( ibm ) linux$> mmfscheckquota
You can check that sap hana disk usage has been reached or not using sap hana studio. ( Administration, overview tab )
How to check disk usage with sap hana studio?
Open Hana studio Administration console, from the overview tab you will get a disk usage summary
3. A Disk quota limit reached.
How to check sap hana disk quota?
There are differents ways to inquire about FS or disk quota.
If the FS or the disk is not full it is possible that quota has been reached for a particular FS.
Disk or FS quota may have been set on your sap hana system. If it is the case, here are OS commands to check various thresholds. Keep in mind that "repquota" requires privileges that you may not have. Contact your system administrator in that case.
To check quota on a specific partition:
taxadm@linux6#> repquota /home
To check for all quotas for a user:
taxadm@linux6#> quota -u $USER
To check quotas for a group:
taxadm@linux6#> quota -g $GROUP
How to check whether a sap hana system is frozen because of a disk space issue?
Although sap hana is a in memory database, disk usage is also an important part of sap hana system.
When a tenant database is set in recovery mode all new log files are written to disk. The important hana Log backup procedure is saving those files before deleting them. Disk Space is then released by the procedure for the next log files.
If for whatever reasons, the log volume is full, everything will stop.
See also : How to find sap hana log backup directories?
See also : How to resolve disk or log backup full FS in sap hana?
How to check DISKFULL event in sap hana indexserver trace file?
Check backup.log or backint.log file for disk full event messages
grep -i full backup.log
grep -i DiskFullEvent backup.log
Logger notified of new DiskFull: DiskFullEvent[ id= XXX, path= /hana/log/SID/mntXXXXX/hdbXXXXX/, state= NEW ]
Execute "df -h" at the OS level to check FS space left.
taxadm@linux3:/hana/log/TAX/mnt00001> df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/linu_sap_05_linu_sapl_1 2.0T 90G 1.8T 4% /hana/log/TAX
How to deal with alert "disk quotas for that drive have been exceeded" in sap hana?
A message such as "Unable to write to filesystem /hana/log/TAX/* disk quotas for that drive have been exceeded" may have come up.
Normaly disk quota, FS size threshold should be monitored and warning should be coming up on time for you to take the necessary action.
If the unfortunate event occurs, it might be to late. Check your database is still up and running. Avoid any new sessions and transactions.
If the alert is related to log backup area and your database is still running, a solution would be to release space in the log backup area using the following command :
ALTER SYSTEM RECLAIM LOG;
See also : how to use sap hana hdbsql?
See also : how to use sap hana studio sql console?
Is the sap hana lock alert occuring during a particular time frame?
You arrived at work and you noticed locking alert messages that has been occuring during the night or during lunch time. The problem is over. The event has happened and is finished. However it is the same scenario, the next day and the day after.
The problem is occuring during a predefined time frame. You have to investigate and understand where it is coming from.
Find out how urgent the locking problem has to be sorted out. That situation might become more serious with time. Overall performance issue might come across backup schedule time frame and all backup might be compromised.
How to investigate inconsistant sap hana SQL query execution?
In some cases, a hana query may be fast one day and slow another day.
There are various investigations to perform when sql queries executions are inconsistent.
1. Check how long it take to load tables used within the query.
SQL : select LOADED from M_CS_TABLES;
2. Check how long it take to compile the actual query.
Monitor the SQL execution time after adding 'with hint (ignore_plan_cache)' at the end of SQL query.
If the query does not stay in the cache, increase the query cache size. ;
3. Check the merge column table status.
A large amount of data in the delta merge will slow down a query execution.
SQL : select * from M_DELTA_MERGE_STATISTICS;
Check when occured the last delta merge operations.
How to investigate why a sap hana query has lost performance after an upgrade?
Sap hana query execution plan may have changed after an upgrade.
if you can, run the same query on an older sap hana version and compare the two sap hana execution plans.
How to check how a sap hana locked event has been resolved?
You identified a time frame where a number of locking events have benn occuring. Performance was slightly impacted during that time.
Check whether the locks were released naturaly or whether transactions were killed via the grace time period defined in sap hana.
Using sap hana studio monitoring console, calculate the query duration.
Using sap hana studio, check the query execution time grace period.
====> How to configure sap hana to stop a long query after while?
If the query duration recorded in the hana studio monitoring console matches the sap hana query configuration duration limit, you know the long and blocking transaction was killed automatically and the transaction was rollback.
Above picture in : https://help.sap.com/viewer/bed8c14f9f024763b0777aa72b5436f6/2.0.02/en-US/9938ac9f1a0541a4b8d6db08c61a5c95.html
Has the sap hana waiting event been resolved quickly enough?
The locking event happened during the time frame and transactions execution took longer than normal. Is it acceptable or could it interfere with other scheduled transactions such as backup for example.
Back to top