Delta Merge Issue in HANA

 Delta Merge Issue: (Merge failure analysis)

Here, We will disscuss about delta merge issue's in SAP HANA database and below are the some issue's related to delte merge in HANA database.

-Size of delta storage of column store tables are more compare to main storage for individual tables due to this query performance will affect.

-Auto merge was not triggered because it does not meet the condition that triggers merge, which is specify in threshold formula of auto merge configured in auto_merge_decision_fun under indexserver.ini

-An alert has been issued because a table is exceeding the threshold for the maximum size of delta storage.

-The delta storage does not compress data well and it may hold old versions of records that are no longer required for consistent reads.

Case Study on Delta Merge Issue :

HANA DB Version : 96

Symptoms : 

1> If the below error is showing after running the select query.

select * from m_delta_merge_statistics where schema_name = 'HANADBXY' and last_error <> 0 order by start_time desc

column store error: [2486] table optimization did not have an effect because all rows in delta were not optimzable

2> If we can see something related to 'TextAttribute' in indexserver alert trace file , after failing the delta merge .

Then DBA Finding's :

Below Finding from Index Server Trace file :

bsa0020:HDB:abcadm /usr/sap/ABC/HDB02/vadbabc/trace> tail -f indexserver_alert_vadbabc.trc
pop.setEstimatedResultSize(1)
[24497]{361811}[407/2076931479] SERVER_TRACE   TextAttribute(10163) : HANADBXY:.c017:1fs:jdx_targetgroup_memberinfo (11879878): rc=9

Reason for Error : Merge was done but during execution the Mergedog has recognized that the data in delta are still uncommitted and can’t be processed further.

Solution :

Above Error, will come due to long running transaction without commit from application or database side and if in between delta merge process will start for the same table then the Merge need exclusive lock on the same table but due to long running transaction on same table HANA will not allow to take exclusive lock so delta merge process will fail on the same table then will getting column store error: [2486].

this type situation we should identify long running transaction, running without commit and we should inform Application team .once long running transaction committed then HANA , will start delta merge process again and once it successfully  completed HANA automatically fix above error .

SQL Statement for checking Delta Merge Status:

---To check for the delta merge issues use the below Query in Studio-SQL console .SQL statement to get the top 100 largest delta storages in memory

SELECT TOP 100 * from M_CS_TABLES ORDER BY MEMORY_SIZE_IN_DELTA DESC

---Query to identify the tables where Delta Memory Size > Main Memory Size

select * from m_cs_tables where schema_name = 'HANADBXY' and raw_record_count_in_main < raw_record_count_in_delta  order by raw_record_count_in_delta desc;

---SQL statement to check when the last delta merges happened for problematic tables.

SELECT * FROM M_DELTA_MERGE_STATISTICS WHERE table_name = 'SFSG_CODELIST_XYZ_DATA'

select AUTO_MERGE_ON from TABLES where TABLE_NAME = 'ACCESS_LIST2_ENTRY_XYZ_S'

SELECT table_name, schema_name, MEMORY_SIZE_IN_DELTA, MEMORY_SIZE_IN_MAIN from SYS.M_CS_TABLES where table_name = 'ZADSD_AS_SS_EMAIL'

SELECT * FROM SYS.M_CS_TABLES where table_name='' and schema_name='';

---SQL statement for Disable & Enable Auto-merge for the Table. 

 alter table _SAPD041150.ZADSD_AS_EMAIL DISABLE AUTOMERGE

---If no alert was raised, you can check for the tables with the most records in the delta.

SELECT * FROM SYS.M_CS_TABLES where record_count>0 order by raw_record_count_in_delta desc;

If WRITE_COUNT is low, check the threshold value of "Check currently utilized percentage of main memory" in Configure Check Settings of the Alerts tab in SAP HANA studio.


Related Topic - Delta Merge in SAP HANA
Related Topic - Auto Merge threshold formula in HANA
Related Topic - Mergedog in SAP HANA