## 3.7.2.1 Analyze Large Delta Storage of Column Store Tables
Analyze and interpret issues related to delta storage with help from alerts in SAP HANA cockpit.
## Procedure
1. If an alert was raised, go to the Alerts tile in the SAP HANA cockpit, click Show all and filter for "delta storage".
Check if the alert is raised for a small number of tables or for many tables. Focus on tables where the alert has high priority. Alerts raised with low or medium priority usually don't need immediate action, but should be taken as one indicator for checking the sizing. Also these alerts should be taken into account when specific performance issues with end-user operations on these tables are reported, since read-access on delta storage may be one reason for slow performance.
2. Click on an alert and check the alert details about its previous occurrences visible in the graph.
- a. If the alert occurred several times, check since when this started. To monitor a longer period of time, use the drop-down menu on the right.
- b. Check whether it occurs regularly at a certain time.
This may indicate a specific usage pattern from application side that might have room for optimization. For example, when many inserts and deletes are performed during a load process, it might be possible to replace these operations with a suitable filter in the source system. You can employ the data in the Expensive Statement Trace and the Performance Monitor to determine the usage of the table by applications.
3. Check the timestamp of the alert if it is current, then start with checking current attributes of this table. Information regarding the delta merge operation on specific tables can be obtained from the system view m_CS_TABLES.
SELECT * FROM SYS.m_CS_TABLES where table_name='mytable' and schema_name='myschema';
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;
4. Check the following attributes:
- LAST_MERGE_TIME
- MERGE_COUNT
- READ_COUNT, WRITE_COUNT
- RECORD_COUNT
- RAW_RECORD_COUNT_IN_MAIN
- RAW_RECORD_COUNT_IN_DELTA
- MEMORY_SIZE_IN_MAIN
- MEMORY_SIZE_IN_DELTA
- a. If MERGE_COUNT is high then this is an indicator that the delta merge works properly, while a low MERGE_COUNT suggests a need for corrective action.
A large difference between RAW_RECORD_COUNT_IN_MAIN and RECORD_COUNT suggests that the table has not been compressed properly. Note that compression is not triggered when a merge is triggered from an SQLScript, but only in case of Auto, Smart or Critical Merge.
A high WRITE_COUNT suggests that many insert, update and delete operations occur. If the occurrence of the delta merge problem is rare, then it usually will be sufficient to trigger the merge for this table manually. See Perform a Manual Delta Merge Operation in the SAP HANA Administration Guide .
- b. If there are many deleted records, you can trigger a compress of the table with the following command:
- c. Confirm the delta merge operation has succeeded in the following ways:
- LAST_MERGE_TIME
- MERGE_COUNT
- RAW_RECORD_COUNT_IN_DELTA
- LAST_COMPRESSED_RECORD_COUNT
5. If problems with the delta storage re-occur frequently for a specific table, check the merge statistics for this table. Y ou can do this in the SAP HANA database explorer by executing the Merge Statistics statement in the Statement Library . Y ou can filter by table name and schema name.
```
UPDATE mytable WITH PARAMETERS('OPTIMIZE_COMPRESSION'='YES');
```
Alternatively you can run the following SQL statement and perform the following checks:
SELECT * FROM SYS.m_DELTA_MERGE_STATISTICS where table_name='mytable' and schema_name='myschema';
- a. Check column SUCCESS for records with value other than TRUE.
- b. Check the column LAST_ERROR for records with value other than 0. A typical error is 2048 and ERROR_DESCRIPTION shows error 2484 which indicates that there was not enough memory to compress the table after the merge.
For other error codes please refer to the SAP HANA Administration Guide .
- c. Check the columns START_TIME, EXECUTION_TIME, MOTIVATION and MERGED_DELTA_RECORDS.
2. For cases where MERGED_DELTA_RECORDS becomes excessively large the trigger function for the MOTIVATION type should be reviewed and the LOAD should be analyzed for that time frame ( Performance Monitor ). A value of MERGED_DELTA_RECORDS = -1 suggests that no records were merged but that a compression optimization was performed.
6. If you need to analyze the delta merge statistics for a longer period, use the equivalent select on table HOST_DELTA_MERGE_STATISTICS of the statistics server:
```
SELECT * FROM _SYS_STATISTICS.HOST_DELTA_MERGE_STATISTICS where table_name='mytable' and schema_name='myschema';
```
You can check the delta merge configuration in SAP HANA cockpit by opening Configuration of System Properties indexserver.ini mergedog .
Since the default value for the frequency of delta merges is already 1 minute ( check_interval = 60.000 ms), optimization with regards to memory consumption can only be done by adjusting the decision function of the corresponding merge type and the corresponding priority function. However, changes
should be done very carefully and always with involvement of experts from SAP. Parameters of the functions are documented in the SAP HANA Administration Guide .
## Related Information
m_CS_TABLES System View (SAP HANA SQL Reference)
## Alerts
Inactive Delta Merge [page 130]
Use the Statement Library to Administer Your Database
Memory Information from Logs and Traces [page 66]