2057046 - FAQ: SAP HANA Delta Merges

Symptom

You want to understand merges in SAP HANA environments.

Environment

SAP HANA

Cause

1. What are delta merges in SAP HANA environments?
2. Where can I find more detailed information related to delta merges?
3. Which indications exist for problems in the delta merge area?
4. Is the delta merge an online operation?
5. How is a merge triggered?
6. How can the history of temporal tables be merged manually?
7. How can a manual delta merge be triggered for individual partitions?
8. How can delta merges and the delta storage be monitored?
9. How can peaks of high load due to concurrent or parallelized delta merges be prevented?
10. How can the load, priority and decision functions be interpreted?
11. Can merge related parameters be adjusted?
12. Why can large delta storages be critical?
13. What should be done in case of large delta storages?
14. How can I trace the merge activities in order to analyze problems with merges?
15. Why is more memory and disk space required during the delta merge operation?
16. What can be done to limit the memory requirements during delta merges?
17. How can I check for which tables auto merges are disabled?
18. How can I check for which tables persistent merges are disabled?
19. In which situations are critical merges executed?
20. Why do I see auto merges on tables although auto merges are disabled on table level?
21. What is the meaning of the different TYPE values in table M_DELTA_MERGE_STATISTICS?
22. What do the shortcuts mean which are used in the decision and cost functions?
23. In which scenarios can it be useful to reduce or disable auto merges?
24. Is the default auto merge decision function already optimal?
25. Is the default critical merge decision function already optimal?
26. What needs to be done in case of merge related errors?
27. Is a restart required when changing delta merge related parameters?
28. Is the delta storage empty after a merge?
29. What are reasons why auto merges aren't executed as expected?
30. How can I temporarily disable merges for a critical table?
31. Does M_DELTA_MERGE_STATISTICS also contain merge information from the secondary site of a system replication scenario?
32. For which services is the mergedog required?

Resolution

1. What are delta merges in SAP HANA environments?

Changes (INSERT, UPDATE, DELETE) to SAP HANA column store tables are not directly updated in the main storage. Instead the change information is written to a dedicated delta storage:

From time to time the main storage needs to be updated with the content of the delta storage. This operation is called delta merge. The rough execution steps of a delta merge are illustrated in the following picture:

You can see that during a delta merge two separate main and delta storages exist.

2. Where can I find more detailed information related to delta merges?

More detailed information about the delta merge operation can be found in the SAP HANA Administration Guide.
The SAP HANA Troubleshooting and Performance Analysis Guide provides troubleshooting details for problems in the delta merge area.

3. Which indications exist for problems in the delta merge area?

The following SAP HANA alerts indicate problems in the delta merge area:

Alert	Name	Description
10	Delta merge (mergedog) configuration	Determines whether or not the 'active' parameter in the 'mergedog' section of system configuration file(s) is 'yes'. mergedog is the system process that periodically checks column tables to determine whether or not a delta merge operation needs to be executed.
19	Record count of delta storage of column-store tables	Determines the number of records in the delta storage of column-store tables.
29	Size of delta storage of column-store tables	Determines the size of the delta storage of column tables.
88	Auto merge for column-store tables	Determines if the delta merge of a table was executed successfully or not.

SQL: "HANA_Configuration_MiniChecks" (SAP Note 1969700, 1999993) returns a potentially critical issue (C = 'X') for one of the following individual checks:

Check ID	Details
538	Delta merges > 900 s (last day)
540	Failing delta merges (info messages, last day)
541	Failing delta merges (error messages, last day)
542	Auto merge tables with delta storage > 5 GB
543	Auto merge tables with many delta records
544	Non-auto merge tables with delta storage > 5 GB
545	Non-auto merge tables with many delta records
546	Delta size of small auto merge tables (GB)
547	Non BW tables with disabled auto merge
548	Tables with disabled persistent merge

4. Is the delta merge an online operation?

Most steps of the delta merge are performed online, so concurrent read and write accesses to the table aren't blocked. Only during the following typically short periods of time a lock is acquired blocking change operations:

• Beginning of merge: Move of uncommitted rows from the source delta storage (Delta1) to the target delta storage (Delta2); see SAP Note 2098801 for more information
• End of merge: Switch from source to target storages

5. How is a merge triggered?
Merges can be triggered in the following ways - automatically and manually, forced and conditioned. The following picture provides a rough overview:

Additionally there can be implicit merges - independent of mergedog and manual triggers - in the following contexts:

• Recovery
• Log replay

Automatic merges are performed by the mergedog which is configured with the following parameters:

Parameter	Value
indexserver.ini -> [mergedog] -> active	true: Activates mergedog (default) false: Deactivates mergedog
indexserver.ini -> [mergedog] -> check_interval	<frequency_ms>: Defines the interval (in ms) of mergedog invocations (default: 60000, i.e. 1 minute)

Further details for the merge types can be found in the table below:

Merge Type	Classification	Usage Scenarios	Details
Auto merge	automatic, conditioned	Normal system operation	Automatic merges are performed by the mergedog process when certain conditions are fulfilled. Detailed automatic merge conditions are maintained using the following parameter: indexserver.ini -> [mergedog] -> auto_merge_decision_func Per default the auto merge option is active for each table. If required it can be activated and deactivated on table level using the following SQL commands: ALTER TABLE "<table_name>" ENABLE AUTOMERGE ALTER TABLE "<table_name>" DISABLE AUTOMERGE
Critical merge	automatic, conditioned	During times of critical SAP HANA memory allocation	Critical merges are performed by the mergedog process in order to avoid system instabilities. The critical merge conditions are maintained using the following parameter: indexserver.ini -> [mergedog] -> critical_merge_decision_func
Hard merge	manual, unconditioned	Exceptional situations (e.g. problems with auto merge or specific tables)	Hard merges are unconditioned merges controlled by the application or administrator. They are either executed immediately or - in case of high concurrent merge activity - at a later point in time. Hard merges are triggered with the following SQL command: MERGE DELTA OF "<table_name>" [FORCE REBUILD] The FORCE REBUILD option doesn't indicate a forced merge, instead it makes sure that the delta log on disk is cleaned immediately (see SAP Note 2144274). Alternatively you can use SAP HANA Studio for that purpose: SAP HANA Studio -> <system> -> Catalog -> <schema> -> <table> -> "Perform Delta Merge..." In exceptional cases you can trigger a hard merge of all tables using the following Python script delivered as part of the SAP HANA installation: /usr/sap/<sid>/HDB<inst_id>/exe/python_support/mergeAllDeltaTables.py Be aware that this action can be responsible for CPU and memory bottlenecks and there is normally no need to execute it, because reasonable merge operations are performed by the mergedog or by smart merges controlled by the application.
Forced merge	manual, unconditioned, immediate	Exceptional situations (e.g. problems with auto merge or specific tables)	A forced merge is a variant of a hard merge which is generally executed immediately. Forced merges are triggered with the following command: MERGE DELTA OF "<table_name>" WITH PARAMETERS ('FORCED_MERGE' = 'ON')
Smart merge	manual, conditioned	Standard approach for merges controlled by application (e.g. in order to avoid interference of auto merges with data load activities)	Smart merges are conditioned merges controlled by the application. This approach can be useful during specific application scenarios like BW data loads. It makes sure that auto merge operations don't interfer negatively with the business activities. Smart merges can be controlled by setting the following parameter to 'true' (enabled, default) or 'false' (disabled): indexserver.ini -> [mergedog] -> smart_merge_enabled When a smart merge is triggered by the application, the following parameter controls if a merge is actually executed: indexserver.ini -> [mergedog] -> smart_merge_decision_func Smart merges are triggered with the following command: MERGE DELTA OF "<table_name>" WITH PARAMETERS ('SMART_MERGE' = 'ON') In SAP ABAP environments you can use the function module TREX_EXT_MERGE_DELTA_INDEX for triggering smart merges from application side.
Memory merge (SPS <= 08)	manual, unconditioned, not persisted	Exceptional situations (e.g. to avoid disk I/O bottlenecks)	Memory merges are only available up to SPS 08. Afterwards they are no longer possible - even if the configuration may still indicate that they should be done. A memory merge is a variant of a hard merge which is not persisted. The merge only happens in memory with the advantage of reduced disk I/O and the disadvantage of a longer delta log and longer recovery times. Memory merges are triggered with the following command: MERGE DELTA OF "<table_name>" WITH PARAMETERS ('MEMORY_MERGE' = 'ON') Per default the persistent merge option is active for each table. If required it can be activated and deactivated on table level using the following SQL commands: ALTER TABLE "<table_name>" ENABLE PERSISTENT MERGE ALTER TABLE "<table_name>" DISABLE PERSISTENT MERGE

6. How can the history of temporal tables be merged manually?

The history part of temporal tables can be merged by adding the key word HISTORY after MERGE:

MERGE HISTORY DELTA OF "<table_name>" ...

7. How can a manual delta merge be triggered for individual partitions?

An individual table partition can be merged by including the PART<part_id> clause in the MERGE command:

MERGE DELTA OF "<table_name>" PART <part_id> ...

8. How can delta merges and the delta storage be monitored?
Delta merge activities are tracked in table M_DELTA_MERGE_STATISTICS and its history in HOST_DELTA_MERGE_STATISTICS.
See SAP Note 2088971 for details how to configure the amount of data available in monitoring view M_DELTA_MERGE_STATISTICS.
You can use SQL: "HANA_Tables_ColumnStore_Merges" (SAP Note 1969700) to retrieve information. It provides information like execution time, merge type, table name, merge duration, number of rows merged and merge errors.
Example output:

The current utilization of the delta storage can be analyzed using SQL: "HANA_Tables_ColumnStore_DeltaStorage" (SAP Note 1969700).
Example output:

Furthermore merge activities can be monitored in the following ways:

Tables	SQL statement (SAP Note 1969700)	Details
M_SERVICE_THREADS M_SERVICE_THREAD_SAMPLES HOST_SERVICE_THREAD_SAMPLES	SQL: "HANA_Threads_CurrentThreads" SQL: "HANA_Threads_ThreadSamples_FilterAndAggregation" SQL: "HANA_Threads_ThreadSamples_AggregationPerTimeSlice"	Merge related thread information can provide insight which tables are currently merged and what kind of detailed activity is executed. See SAP Note 2114710 for more information.
M_JOB_PROGRESS	SQL: "HANA_Jobs_JobProgress"	Starting with SAP HANA SPS 10 the view M_JOB_PROGRESS contains information for the current M_DELTA_MERGE_STATISTICS related activities (JOB_NAME = 'BW F Fact Table Compression', 'Delta Merge', 'Optimize Compression', 'Reclaim Delta').

9. How can peaks of high load due to concurrent or parallelized delta merges be prevented?
All merge operations apart from forced merges can only start if a so-called merge token is assigned. If no merge token is available, the merges have to wait. Typical symptoms are "Semaphore Wait" locks on TRexAPI::TableMergeRequest::TableMergeRequest as described in SAP Note 1999998.
The total number of available merge tokens is calculated via the following parameter:

indexserver.ini -> [mergedog] -> load_balancing_func

The value calculation includes information like the number of available CPUs, the CPU utilization and the heap memory situation.
When a merge is triggered, SAP HANA calculates the number of required merge tokens. If this calculation is not possible, the value defined by the following parameter is used (default: 2):

indexserver.ini -> [mergedog] -> token_per_table

Furthermore there are priority parameters that are used to calculate priorities of merge requests so that they can be processed in an optimized order:

indexserver.ini -> [mergedog] -> auto_merge_priority_func
indexserver.ini -> [mergedog] -> hard_merge_priority_func
indexserver.ini -> [mergedog] -> smart_merge_priority_func

10. How can the load, priority and decision functions be interpreted?
The load, priority and decision function parameters contain quite complex logical expressions with several 3 character shortcuts. It is not trivial to understand their meaning. The SAP HANA Administration Guide describes the shortcuts. With this information it is possible to understand better how the execution of merges depend on factors like CPU, heap memory or the delta memory size. In the following a typical auto merge decision function (SPS <= 08) is illustrated:

( ( THM >= 256000 and                     At least 256 GB of heap
    ( ( ( DMS > 100 or                    Delta memory size > 100 MB
          DCC > 100 or                    Delta cell count > 100 million (rows * columns)
          DLS > 1000                      Delta log size > 1 GB
        ) and
        DRC > MRC / 100                   Delta row count more than 1 % of main row count
      ) or
      ( DMR > 0.2 * MRC and               Deleted main rows > 20 % of main rows and
        DMR > 0.001                       Deleted main rows > 1000
      )
    )
  ) or
  ( THM < 256000 and                      Less than 256 GB of heap
    ( DMS > 50 or                         Delta memory size > 50 MB
      DCC > 8 or                          Delta cell count > 8 million (rows * columns)
      DLS > 100                           Delta log size > 100 MB
    )
  )
) and
( DUC < 0.1 or                            Uncommitted delta row count < 100000
  0.05 * DRC >= DUC                       Uncommitted delta row count <= 5 % of delta row count
)　

With SPS 09 a different default for the auto merge decision function is in place:

( ( ( DMS>PAL/2000 or                             Delta memory size > 0.05 % of process allocation limit
      DCC>100                                     Delta cell count > 100 million
    ) and 
      DRC > MRC/100                               Delta row count more than 1 % of main row count
  ) or

  ( DMR>0.2*MRC and                               Deleted main rows > 20 % of main rows and 
    DMR > 0.001                                   Deleted main rows > 1000
  )
) and 
( DUC<0.1 or                                      Uncommitted delta row count < 100000
  0.05*DRC>=DUC                                   Uncommitted delta row count <= 5 % of delta row count
)

It is possible to include further general conditions into these functions, e.g. to change the behavior during specific times or for specific tables.
Example: (decision function including time dependent rules)

DMS > 1000 or DMS > 42 and weekday(now())=6 and secondtime(now())>secondtime('01:00') and secondtime(now())<secondtime('02:00')

11. Can merge related parameters be adjusted?
Due to the complexity of the merge operation changes to the parameters can lead to unforeseen effects like increased system load, higher memory consumption or performance degradations. Therefore it is recommended to adjust merge related parameters only in exceptional situations. If parameters are adjusted for a specific task (e.g. as part of a migration activity) it is important to undo the changes after the activity has finished.

12. Why can large delta storages be critical?

Large delta storages can be critical for the following reasons:

Problem	Details
Increased memory consumption	Large delta storages increase the memory requirements because: They are compressed less efficiently than the main storages Not only UPDATE and INSERT, but also DELETE operations result in a growing delta storage. Memory can only be reclaimed with a delta merge (see SAP Note 2088183)
Longer recovery times	Large delta storages result in large delta logs which can increase the recovery times.
Performance degradations	Database requests can take longer because the delta storage is not read-optimized.
Increased disk size	Not only the memory size, but also the disk size interferes with delta merges. See SAP Note 2014987 which describes a situation where a large disk size can be reduced by performing a delta merge operation.

13. What should be done in case of large delta storages?
At first you should check if the large delta storage really has to be treated as a problem. For example it is normal that there are large delta storages during data loads in BW, because BW often suppresses auto merges and uses smart merges at the end. See SAP Note 2043152 for more information.
If you suffer from large delta storages you can proceed according to SAP Note 1977314 in order to understand and resolve the root cause. SAP Note 2039810 describes a scenario where auto merges are no longer executed after a table was renamed.

14. How can I trace the merge activities in order to analyze problems with merges?

To find out details about merge activities you can increase the level of the database trace for "mergedog" and "mergemonitor" to INFO. Results will be written to the standard service trace file. See SAP Note 2119087 for more details.

15. Why is more memory and disk space required during the delta merge operation?

A delta merge needs about twice the size of the table (partition) in memory and on disk to perform the merge. More precisely: size of source main + size of target main + size of source delta. Additionally a temporary working space is needed which is about the size of the largest column of the table (partition).
In case you have a table of 100 GB with 40 GB being the size of largest column, you would need about 100 GB * 2 + 40 GB = 240 GB while the merge is executed.

16. What can be done to limit the memory requirements during delta merges?

The memory requirements during a merge depend both on the overall table size and the delta storage size. The following approaches exist to reduce the memory requirements during delta merges:

Area	Details
Partitioning	Partition large tables so that smaller delta storages on partition level are used instead of a large table-wide delta storage.
Reduction of memory footprint	Reduce the memory requirements of the table by actions like cleanup of basis tables, archiving, reduction of indexes, reduction of CONCAT attributes, data aging and extended storage. See SAP Note 1999997 ("Which options exist to reduce the risk of SAP HANA memory issues?") for more information
Reduction of changes	Check from application side if you can reduce the amount of INSERT / UPDATE / DELETE operations.
Merge parameters	Make sure that the merge related parameters are set to reasonable values (optimally default values) and avoid settings resulting in merges of large delta storages. In individual cases you can adjust merge parameters so that merges happen before the delta storage has grown to critical sizes.
Manual merges	In individual cases you can execute manual merges before the delta storage has grown to critical sizes.

17. How can I check for which tables auto merges are disabled?

You can use SQL: "HANA_Tables_ColumnStore_AutoMergeDisabled" (SAP Note 1969700) in order to check for tables with disabled auto merges.
Disabled auto merges on table level can be a consequence of table moves from row store to column store using SAP ABAP functionality. See SAP Note 2196615 for more information.

18. How can I check for which tables persistent merges are disabled?

You can use SQL: "HANA_Tables_ColumnStore_PersistentMergeDisabled" (SAP Note 1969700) in order to check for tables with disabled persistent merges.

19. In which situations are critical merges executed?

Critical merges are controlled by the critical_merge_decision_func parameter. A typical default value is:

UPT > 43200 and                           Indexserver uptime at least 43200 seconds (12 hours)
( ( MMS < 10000 and                       Main memory size < 10 GB
    DMS > 1000 and                        Delta memory size > 1 GB
    TMD > 86400                           Table merge delay (time since last merge, in seconds)
  ) or 
  TMD>604800                              Table merge delay (time since last merge, in seconds)
)

This means that critical merges are only executed when the indexserver runs already for at least 12 hour and additionally at least one of the following conditions is fulfilled:

• The table main size is smaller than 10 GB, the table delta size is greater than 1 GB and the table wasn't merged since at least one day
• The table wasn't merged since around 7 days

 So we can see that - among others - an unconditioned merge of tables is performed when the last merge happened more than 7 days ago.

20. Why do I see auto merges on tables although auto merges are disabled on table level?

Up to SAP HANA Rev. 121 critical merges are registered with MOTIVATION = 'AUTO' in views like M_DELTA_MERGE_STATISTICS and HOST_DELTA_MERGE_STATISTICS. Therefore you can see a merge categorized as 'AUTO' when a critical merge was executed (e.g. because no successful merge happened within the last 7 days).
Starting with SAP HANA Rev. 122 critical merges are recorded with MOTIVATION = 'CRITICAL' in M_DELTA_MERGE_STATISTICS.

21. What is the meaning of the different TYPE values in table M_DELTA_MERGE_STATISTICS?

In fact not all entries in M_DELTA_MERGE_STATISTICS actually reflect merge operations, it depends on the TYPE. The following TYPE values exist in M_DELTA_MERGE_STATISTICS:

Type	Merge	Details
FACT	No	BW fact table compression (BW_F_FACT_TABLE_COMPRESSION)
HINT	No	Smart merge request triggered by application, smart_merge_decision_func is evaluated and - if merge is indicated - a subsequent MERGE step is performed
MERGE	Yes	Delta merge
RECLAIM	No	Garbage collection in delta storage
SPARSE	No	Compression optimization (see SAP Note 2112604) Typically executed as subsequent step of an auto merge, smart merge or critical merge, can also be triggered manually Not restricted to SPARSE compression, so 'COMPRESS' would be a clearer name for this type

22. What do the shortcuts mean which are used in the decision and cost functions?

The most important shortcuts used in decision and cost functions are:

Shortcut	Unit	Details
AHM	MB	Available heap memory
BASENAME		Table name (SAP HANA SPS >= 12)
CLA	%	CPU load average
CRCSOC	million	Changed row count since optimize compression
DCC	million	Delta cell count (number of rows in delta multiplied with number of table columns)
DLS	MB	Delta log size
DMR	million	Deleted main rows
DMS	MB	Delta memory size
DRC	million	Delta row count (uncommitted rows are not always taken into account)
DUC	million	Uncommitted delta row count
FULLNAME		Internal table name (SAP HANA SPS >= 12): Table partitioned: _SYS_SPLIT/<table_name>~<part_id> Table not partitioned: <table_name>
GAL	MB	Global allocation limit
IF(<arg1>, <arg2>, <arg3>)		Returns <arg2> if <arg1> is fullfilled (i.e. true or not 0), otherwise <arg3>
INSTR(<full_string>, <sub_string>)		Returns 0 if <sub_string> isn't contained in <full_string>, otherwise the position of the first occurrence of <sub_string> in <full_string> is returned
LCC		Logical CPU count
LOADED		TRUE if table is loaded, otherwise FALSE
MMS	MB	Main memory size
MMU	million	Main max UDIV
MRC	million	Main row count
NAME		Internal table name: Table partitioned: _SYS_SPLIT/<table_name>~<part_id> Table not partitioned: <table_name> Due to the special naming convention for partitioned tables you have to use BASENAME (SAP HANA SPS >= 12) or the following approach if you want to refer to a partitioned table: INSTR(NAME, '<table_name>') > 0
NMU	million	Main max UDIV (>= SPS 09)
NOW()		Current date and time (localtime of the server timezone)
OCRC	million	(Last) optimize compression row count
PAL	MB	Process allocation limit
QDW	s	Queuing delay wait (wait time for assignment of token)
RHM	MB	Estimated required heap memory to complete table optimization
RP	boolean	TRUE if table is range partitioned, otherwise false
SCHEMA		Schema name
THM	MB	Total heap memory
TMD	s	Time since last merge of table
UPT	s	Uptime of indexserver

23. In which scenarios can it be useful to reduce or disable auto merges?

Disabling auto merges is mainly useful in scenarios of mass changes, e.g. data loads (many INSERTs) or archiving (many DELETEs). Several standard functionalities already work in this way:

• In BW environments auto merges are explicitly disabled for certain table types in order to speed up operations like data loads. BW relies on smart merges triggered on application side.
• Migration tools disable auto merges or set the auto_merge_decision_func to values that result in less auto merges.

The effect of reducing or disabling auto merges depends on several factors like the COMMIT frequency and the actual DML activities. Furthermore it is a trade-off between performance and memory consumption. The fewer merges are done, the larger is the memory requirement. Therefore a general rule of thumb is not available and you have to test the different options if you perform time-critical mass changes.

24. Is the default auto merge decision function already optimal?

The default auto merge decision function has changed a few times. Regardless of these changes it tends to merge small tables with large delta storages too rarely with SPS 09 and below. For example, an auto merge is typically not triggered for a table with 20 MB main storage and 90 MB delta storage, because the threshold for delta storage sizes is 100 MB or higher. As a consequence the memory allocation is higher than necessary and the performance can suffer.
In order to eliminate this issue, you can adjust the auto_merge_decision_func parameter by adding the following condition after the first bracket in its value:

(DRC * TMD > 3600 * ( MRC + 0.0001 )) or

This formula compares the delta row count with the main row count and additionally takes the time since the last merge into account, so that e.g. a small table with the same number of records in main and delta storage will typically be merged after 1 hour.
Example (Rev. 82):

ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('mergedog','auto_merge_decision_func')=
'((DRC*TMD>3600*(MRC+0.0001)) or (THM>=256000 and (((DMS>100 or DCC>100 or DLS>1000) and DRC>MRC/100) or (DMR>0.2*MRC and DMR>0.001))) or 
(THM<256000 and (DMS>50 or DCC>8 or DLS>100))) and (DUC<0.1 or 0.05*DRC>=DUC)' WITH RECONFIGURE

Be aware that you should always take the default value for the auto merge decision function in the system as a basis and only add the additional term at the beginning. If you upgrade to a later SAP HANA revision, you should revisit this parameter setting and adjust it if the default has changed. With SPS 10 and higher the default parameter value has been adjusted so that also small tables are properly considered. Thus, you have to remove this special configuration when you uses SPS 10 or higher.

25. Is the default critical merge decision function already optimal?

Up to SAP HANA SPS 10 the default value of the critical_merge_decision_func parameter is:

UPT>43200 and ((MMS<10000 and DMS>1000 and TMD>86400) or TMD>604800)

When a table wasn't merged for more than one day and a high amount of changes are executed without COMMIT, so that the delta storage size exceeds 1 GB, the critical merge will kick in. When moving the uncommitted changes to the new delta storage, DML operations on the table are blocked (SAP Note 2098801). This can take significant time in case of a high amount of uncommitted changes. Therefore it is useful to adjust the critical_merge_decision_func parameter so that the critical merge is only executed when the amount of uncommitted changes is on a reasonably low level:

UPT>43200 and ((MMS<10000 and DMS>1000 and TMD>86400) or TMD>604800) and DUC<0.1

Starting with SAP HANA SPS 11 this change is incorporated per default.

26. What needs to be done in case of merge related errors?

Delta merges can fail with various errors, and the details are written to columns LAST_ERROR and ERROR_DESCRIPTION in monitoring view M_DELTA_MERGE_STATISTICS and its history HOST_DELTA_MERGE_STATISTICS. You can check for the top error messages using SQL: "HANA_Tables_ColumnStore_Merges" (SAP Note 1969700).
Example:

Be aware that error details aren't available in earlier revisions. For some time M_DELTA_MERGE_STATISTICS contained the ERROR_DESCRIPTION, but HOST_DELTA_MERGE_STATISTICS not.
Typical errors and required actions are described below. The "Description" of an error code depends on the SAP HANA patch level and so you find more than one description for some of the error codes. The underlying meaning is nevertheless the same. The term "table optimization" in some of the error messages covers merges and compression optimizations.

Error	Description	Recommendations
1527	exception during deltalog replay. See trace file for more information	This error typically indicates an inconsistency. Check the trace file for further details and proceed according to SAP Note 2116157 in order to analyze and resolve the corruption. If the error is reported for merges on secondary sites of system replication scenarios (SAP Note 1999880), the bug described in SAP Note 2348480 (Rev. 110 - 112.02, Rev. 120) can be responsible.
1999	General error (no further information available)	Check the indexserver trace file for more details.
2009	Memory allocation failed	This error is issued when no more memory is available. See SAP Note 1999997 and make sure that sufficient memory is available.
2450	Error during merge of delta index occurred	Check the indexserver trace file for more details, e.g. OOM situations. In case of memory problems, see SAP Note 1999997 for more details. Don't execute memory merges on tables with LOB columns ("main memory merge not supported on tables containing disk lobs!").
2454	general error during historisation of data	This error can happen when merges are executed on temporal tables. For SAP HANA Revisions <= 61 see SAP Note 1895207. In case the history table is a SAP HANA-optimized DSO you can convert this DSO to a optimized standard DSO (see SAP Note 1849497 and 1849498) without keeping change log. If the problem happens on newer Revisions, check the trace files for content. Open a SAP incident if you are not able to resolve the issue.
2458	aborted as cancel requested Table delta merge aborted as cancel was manually requested by a kill session call	If it happens repeatedly: Check why sessions are cancelled manually.
2461	error during optimizeCompression occurred	Check the trace file for more details about the underlying error.
2465	not enough merge tokens for delta merge	This error is issued if during recovery a merge isn't able to proceed due to a lack of merge tokens. This typically happens in system replication environments (SAP Note 1999880) with a configured logreplay replication mode. SAP Note 2351926 describes a bug that can result in these errors with SAP HANA Rev. 110 to 112.04 and 120 to 122.00. Also with newer Revisions this error is still possible, but typically harmless. If you nevertheless want to reduce it, you can increase the number of merge tokens as a workaround by adjusting indexserver.ini -> [mergedog] -> load_balancing_func and reconfiguring the secondary site as described in SAP Note 2351926. You can use SQL: "HANA_Tables_ColumnStore_Merges_TokenOwners" (SAP Note 1969700) to understand which activities occupy how many merge tokens. Normally this error isn't critical because a merge will be repeated at a later time when the current number of tokens is not sufficient.
2480	same table optimization is ongoing The table in question is already being merged.	If it happens repeatedly: Check why concurrent merge requests are issued against the same table.
2481	same table optimization requested multiple times There are already other smart merge requests for this table in the queue.	If it happens repeatedly: Check why concurrent smart merge requests are issued against the same table. The following application SAP Notes can help to reduce the number of unnecessary smart merge request in specific scenarios: SAP Note Details 2243673 Less smart merges for classic BW DSO tables with RFC calls 2245078 Reduction of smart merges in Bank Analyzer scenarios
2482	table optimization was not indicated The delta storage is empty or the evaluation of the smart merge cost function indicated that a merge is not necessary.	Usually this error is harmless and can be ignored (SAP Note 2250715). The following application SAP Notes can help to reduce the number of unnecessary smart merge request in specific scenarios: SAP Note Details 2243673 Less smart merges for classic BW DSO tables with RFC calls 2245078 Reduction of smart merges in Bank Analyzer scenarios In case of doubts you can check if the parameter smart_merge_decision_func is set properly (optimally: default value) and if smart merge logic on application side works correctly.
2483	table optimization was disabled Smart merge is not active (parameter smart_merge_enabled=no)	Check why smart merges are disabled globally and activate them if possible by unsetting parameter indexserver.ini -> [mergedog] -> smart_merge_enabled.
2484	not enough memory for table optimization Memory required to optimize table exceeds heap limit	Analyze and resolve memory shortages or misconfigurations (see SAP Note 1999997 for more details).
2485	table optimization was rolled back	Check the indexserver trace file for more details.
2486	table optimization did not have an effect because all rows in delta were not optimizable	If it happens repeatedly: Check why merge operations are started although no row can be processed. This situation can happen if all records in the delta storage have to be moved to the new delta storage because they are still uncommitted.
2487	table optimization was not possible due to open metadata changes	Check the indexserver trace file for more details. Perform a consistency check using CHECK_TABLE_CONSISTENCY in order to identify possible underlying corruptions (see SAP Note 1977584). Open a SAP incident on component HAN-DB if your require assistance from SAP.
2594	General partitioning error	Check the indexserver trace file for more details. Among others, SAP HANA bugs in the area of paged attributes (SAP Note 1871386) can be responsible for this error.
2953	BW F Fact Table Compression failed	Check the indexserver trace file for more details.
6900	Internal error Attribute engine failed	Check the indexserver trace file for more details.
6923	Attribute load failed	Check the indexserver trace file for more details, e.g. OOM situations. In case of memory problems, see SAP Note 1999997 for more details.
6924	Attribute save failed	Check the indexserver trace file for more details, e.g. OOM situations. In case of memory problems, see SAP Note 1999997 for more details.
6939	AttributeEngine: commit() or rollback() was called without calling prepare() first	This error can be the consequence of a SAP HANA bug that can result in an inconsistency between existing columns and metadata information. Upgrade to SAP HANA SPS 09 or higher in order to fix this issue. Open a SAP incident on component HAN-DB if your require assistance from SAP.
6952	AttributeEngine: not enough memory	This error is issued when no more memory is available. See SAP Note 1999997 and make sure that sufficient memory is available.
29020	ltt::exception caught while operating on $STORAGEOBJECT$	This error is related to the persistence layer. Check the indexserver trace file for more details.
999999	message not found	This error can be returned if the underlying table is corrupted or has reached the 2 billion record limit. Check the indexserver trace file for more details. Perform a consistency check using CHECK_TABLE_CONSISTENCY in order to identify possible underlying corruptions (see SAP Note 1977584). Open a SAP incident on component HAN-DB if your require assistance from SAP.

When an auto merge fails due to an error, the mergedog waits for some time (typically 1 hour) before trying the same auto merge again.

27. Is a restart required when changing delta merge related parameters?

Delta merge parameters can be changed online and immediately take effect. No restart is required.

28. Is the delta storage empty after a merge?

No, not necessarily:

• Only committed data is moved from delta storage to main storage. Uncommitted records remain in delta storage. It is a good practice (also considered by the auto merge decision function) to avoid merges if a high fraction of records in delta storage is not committed, yet.
• Changes performed during the delta merge will also appear in the new delta storage.

29. What are reasons why auto merges aren't executed as expected?

If tables aren't automatically merged although the delta storage is filled significantly, you can check the following potential reasons:

Reason	Details
Inadequate auto merge decision function	Make sure that the auto_merge_decision_func parameter is default or set to a reasonable value. Check if the conditions of the tables in question fulfill the auto_merge_decision_func.
Auto merges deactivated on table level	Check via SQL: "HANA_Tables_ColumnStore_AutoMergeDisabled" (SAP Note 1969700) if auto merges are disabled on table level and enable them if required.
Mergedog deactivated	Make sure that indexserver.ini -> [mergedog] -> active isn't explicitly switched off ('false'), because this would disable the mergedog and no more auto merges are performed.
Tables assigned to wrong service	The mergedog only considers tables related to the services with own persistent tables like indexserver and statisticsserver. If tables are assigned to other services (e.g scriptserver), no auto merge will happen.

30. How can I temporarily disable merges for a critical table?

In some situations (e.g. in case of a corrupted table) it can be useful to disable both auto and critical merge for a specific table on a temporary basis and enable it again once the problem is fixed. This can be achieved in the following way:

Merge type	Deactivation command	Activation command
AUTO	ALTER TABLE "<table_name>" DISABLE AUTOMERGE	ALTER TABLE "<table_name>" ENABLE AUTOMERGE
CRITICAL	ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') SET ('mergedog', 'critical_merge_decision_func') =   'INSTR(NAME, ''<table_name>'') = 0 AND <original_critical_merge_decision_func_value>' WITH RECONFIGURE	If DEFAULT value is used: ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') UNSET ('mergedog', 'critical_merge_decision_func') WITH RECONFIGURE If system specific value is used: ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') SET ('mergedog', 'critical_merge_decision_func') =   '<original_critical_merge_decision_func_value>' WITH RECONFIGURE

31. Does M_DELTA_MERGE_STATISTICS also contain merge information from the secondary site of a system replication scenario?

Starting with SAP HANA Rev. 111 the view M_DELTA_MERGE_STATISTICS can also contain merge information from the secondary site of a system replication scenario (SAP Note 1999880). Certain conditions like identical SAP HANA patch level needs to be fulfilled for this feature.
This new behavior can result in some confusion, e.g.:

• Merge errors 2465 ("not enough merge tokens for delta merge") reported: See "What needs to be done in case of merge related errors?" for more information.
• Long merge durations due to the fact that a merge on the secondary site needs to synchronize with the recovery queue and so it can't be committed immediately. This can in general be considered as harmless.

32. For which services is the mergedog required?

The mergedog is only activated for services with column store tables:

• indexserver
• (standalone) statistics server

For other services the mergedog is intentionally disabled. SAP Note 2240059 describes a statistics server bug that can result in false positive alerts if the mergedog is (correctly) disabled for the dpserver service and suggests to activate the mergedog for the dpserver as a temporary workaround.

Keywords

SAP HANA delta merge M_DELTA_MERGE_STATISTICS optimzable [sic]