## 3.4.2  Savepoint Performance
To perform a savepoint write operation SAP HANA needs to take a global database lock. Two phases of this operation, the 'critical phase' and the 'wait for lock' phase can be monitored and optimized if necessary.
## Global Database Lock: Critical Phase
Savepoints are used to implement backup and disaster recovery in SAP HANA. If the state of SAP HANA has to be recovered, the database log from the last savepoint will be replayed.
While SAP HANA is designed to keep the time period of the lock as short as possible, poor I/O performance can extend it to a length that causes a considerable performance impact. You can analyze the savepoint performance with this SQL statement which shows how long the last and the current savepoint writes took/are taking:
```
select start_time, volume_id, round(duration / 1000000) as "Duration in Seconds", round(critical_phase_duration / 1000000) as "Critical Phase Duration in Seconds", round(total_size / 1024 / 1024) as "Size in MB", round(total_size / duration) as "Appro. MB/sec", round (flushed_rowstore_size / 1024 / 1024) as "Row Store Part MB" from m_savepoints where volume_id in ( select volume_id from m_volumes where service_name = 'indexserver') ;
```
An example of the output is shown here. The critical phase duration, in which savepoints need to take a global database lock, must be observed carefully. The duration should not be longer than a second. In this example the times are significantly higher due to I/O problems.
|    | START_TIME                     | VOLUME_ID   | Duration in Seconds   | Critical Phase Duration in Seconds   | Size in MB   |   Appro. MB{sec | Row Store Part MB   |
|----|--------------------------------|-------------|-----------------------|--------------------------------------|--------------|-----------------|---------------------|
|    | Dec 16, 2013 9.28.38,469738 AM |             | 23                    |                                      | 3,822        |             177 | 874                 |
|    | Dec 16, 2013 9.23.17.984555 AM |             | 20                    |                                      | 3,845        |             197 | 796                 |
|    | Dec 16, 2013 9.17,49.383506 AM |             | 29                    |                                      | 3,910        |             143 | 1,065               |
|    | Dec 16, 2013 9.11.33.777138 AM |             | 76                    |                                      | 10,072       |             140 | 1,957               |
| 5  | Dec 16, 2013 9,05.57.694349 AM |             | 16                    |                                      | 3,879        |             113 | 1,362               |
|    | Dec 16, 2013 9,00.38.347428 AM |             |                       |                                      | 3,321        |             180 | 1,026               |
|    | Dec 16, 2013 8.56,23,056008 AM |             | 69                    |                                      | 4,226        |              64 | 1,324               |
|    | Dec 16, 2013 8,48.07.902735 AM |             | 154                   |                                      | 11,558       |              79 | 4,650               |
|    | Dec 16, 2013 8,09,16.575288 AM |             | 167                   |                                      | 20,150       |             127 | 5,032               |
| 10 | Dec 16, 2013 8,05.58.861928 AM |             | 49                    |                                      | 2,762        |              59 | 809                 |
|    | Dec 16. 2013 8,01.32.889947 AM |             |                       |                                      | 1253         |             201 | 161                 |
Savepoints
The following SQL generates a histogram on the critical phase duration, the output is shown below:
```
select to_char(SERVER_TIMESTAMP,'yyyy.mm.dd') as "time", sum(case when (critical_phase_duration <= 1000000) then 1 else 0 end) as "<= 1 s", sum(case when (critical_phase_duration > 1000000 and critical_phase_duration <=2000000) then 1 else 0 end) as "<= 2 s", sum(case when (critical_phase_duration > 2000000 and critical_phase_duration <=3000000) then 1 else 0 end) as "<= 3 s", sum(case when (critical_phase_duration > 3000000 and critical_phase_duration <=4000000) then 1 else 0 end) as "<= 4 s",
```
```
sum(case when (critical_phase_duration > 4000000 and critical_phase_duration <=5000000) then 1 else 0 end) as "<= 5 s", sum(case when (critical_phase_duration > 5000000 and critical_phase_duration <=10000000) then 1 else 0 end) as "<= 10 s", sum(case when (critical_phase_duration > 10000000 and critical_phase_duration <=20000000) then 1 else 0 end) as "<= 20 s", sum(case when (critical_phase_duration > 20000000 and critical_phase_duration <=40000000) then 1 else 0 end) as "<= 40 s", sum(case when (critical_phase_duration > 40000000 and critical_phase_duration <=60000000) then 1 else 0 end) as "<= 60 s", sum(case when (critical_phase_duration > 60000000 ) then 1 else 0 end) as "> 60 s", count(critical_phase_duration) as "ALL" from "_SYS_STATISTICS"."HOST_SAVEPOINTS" where volume_id in (select volume_id from m_volumes where service_name = 'indexserver') and weekday (server_timestamp) not in (5, 6) group by to_char(SERVER_TIMESTAMP,'yyyy.mm.dd') order by to_char(SERVER_TIMESTAMP,'yyyy.mm.dd') desc;
```
Savepoint Histogram
The performance of the backup can be analyzed with this statement:
```
select mbc.backup_id, SECONDS_BETWEEN (mbc.sys_start_time, mbc.sys_end_time) seconds, round(sum(backup_size) / 1024 / 1024 / 1024,2) size_gb, round(sum(backup_size) / SECONDS_BETWEEN (mbc.sys_start_time, mbc.sys_end_time) / 1024 / 1024, 2) speed_mbs from m_backup_catalog_files mbcf , m_backup_catalog mbc where mbc.entry_type_name = 'complete data backup' and mbc.state_name = 'successful' and mbcf.backup_id = mbc.backup_id
```
## Wait for Lock Phase
Apart from a long-running 'critical phase', the savepoint can also have long-running 'wait for lock' phases which can impact the performance of the system. In the waitForLock phase the savepoint must wait for exclusive access to the ConsistentChangeLock , if another database operation holds this lock for a longer time, delays are possible.
You can find further information on the waitForLock phase in the KBA 2100009 FAQ: SAP HANA Savepoints - refer to question #6 'How can typical savepoint issues be analyzed and resolved?'.
To check if there is a potential issue with the runtime of the waitForLock phase you can use the script HANA_Configuration_MiniChecks_* (* depending on your HANA database release and revision there are different versions of the script) from the SAP Note 1969700 SQL Statement Collection for SAP HANA . Where the script finds a potentially critical issue column C in the output is marked with a 'X' .
| CHID   | DESCRIPTION                                  | VALUE   | EXPECTED VALUE   | C   |   SAP NOTE |
|--------|----------------------------------------------|---------|------------------|-----|------------|
| ****   | SAP HANAMINI CHECKS                          |         |                  |     |    1999993 |
| M0346  | Long waitForLock savepoint phases (mid-term) | 16      | 0                | X   |    2100009 |
In many cases long waitForLock phases are caused by HANA internal lock contention. We can use the script HANA_Configuration_MiniChecks_* to troubleshoot the issue further. In this example from the script in the 'LOCKS' section we get an indication that there is an IO issue on the system and an issue with FileIDMapping contention:
| CHID   | DESCRIPTION                                   | VALUE                                         | EXPECTED VALUE   | C   |   SAP NOTE |
|--------|-----------------------------------------------|-----------------------------------------------|------------------|-----|------------|
| M1052  | Significant internal Lock waits (short- term) | PageIO::SyncCallb ackSemaphore (1.59 threads) | none             | X   |    1999998 |
| M1055  | Locks with repeated wait spikes (long-term)   | FileIDMap (48 x 27)                           | none             | X   |    1999998 |
|        |                                               | PageIO::SyncCallb ackSemaphore (428 x 28)     | none             | X   |    1999998 |
## IO Bottlenecks
More information about locks is available in KBA 1999998 - FAQ: SAP HANA Lock Analysis ; there, the meaning of lock PageIO::SyncCallbackSemaphore, is described as follows:
Starting with SAP HANA 2.0 synchronous I/O requests are implicitly executed as asynchronous I/O requests. The PageIO::SyncCallbackSemaphore indicates that the thread is currently waiting for the asynchronous I/O request to finish properly. See SAP Note 1999930 for tuning SAP HANA I/O performance.
This indicates that there is an IO bottleneck on the system and the IO throughput is too slow at times.
You can confirm this by executing the script HANA_IO_Savepoints_* (*depending on your HANA Database release and revision there are different versions of the script) from the SQL Statement Collection for SAP HANA. In this case you can make a change in the modification section to only collect information where MIN_WAIT_FOR_LOCK_PHASE_DURATION_S runs for 10 seconds or greater:
```
( SELECT                           /* Modification section */ '1000/10/18 07:58:00' BEGIN_TIME, '9999/10/18 08:05:00' END_TIME, 'SERVER' TIMEZONE, '%' HOST, '%' PORT, -1 VERSION, -1 MIN_BLOCKING_PHASE_DURATION_S, 10 MIN_WAIT_FOR_LOCK_PHASE_DURATION_S, -1 MIN_CRITICAL_PHASE_DURATION_S,
```
The script has the following output parameters; the columns 'LOCK_S' and 'MB_PER_S' are particularly significant here:
```
[OUTPUT PARAMETERS] - START_TIME:            Start time of considered time interval - BLK_PHASE_START_TIME:  Start time of blocking phase - CRIT_PHASE_START_TIME: Start time of critical phase - END_TIME:              End time of savepoint - START_TIME:            Start time of considered time interval - HOST:                  Host - PORT:                  Port - VERSION:               Savepoint version - COUNT:                 Number of savepoints - P:                     Savepoint purpose ... - I:                     Savepoint initiation (E -> EXECUTED_EXPLICITLY, T -> TRIGGERED_TIMEBASED) - AGG:                   Aggregation type used for the subsequent output columns (MAX -> maximum, AVG -> average, SUM -> total) - RETRIES:               Number of flush retries in the non-critical phase - TOTAL_S:               Savepoint duration (s) - BLK_S:                 Blocking phase (s), i.e. time when DML operations can be blocked -LOCK_S:                waitForLock phase duration (s) - CRIT_S:                Critical phase duration (s) - SIZE_MB:               Savepoint write size (MB) -MB_PER_S:              Write throughput (MB / s) - RS_SIZE_PCT:           Flushed data related to rowstore compared to overall flushed data (%)
```
In the example output shown here we can see that the throughput (MB_PER_S) is very low at the times of the long running waitForLock phases (LOCK_S):
In this case your Storage/IO Team need to do a deeper analysis of the IO layers and optimize the IO to meet the workload requirements of the system. KBA 1999930 - FAQ: SAP HANA I/O Analysis can be used as a starting point for the IO analysis and optimization.
## Lock Contention
Regarding the issue related to 'FileIDMapping' contention, the ConsistentChangeLock can be held by threads waiting for FileIDMapping locks, so ConsistentChangeLock waits can be a secondary symptom of FileIDMapping contention. In that case you must analyze and resolve the FileIDMapping contention, then the ConsistentChangeLock contention will improve.
For more information and potential root causes of this issue see question #15 'What are common reasons for ConsistentChangeLock contention?' in the KBA 1999998 - FAQ: SAP HANA Lock Analysis .
## Run-time Dump
If the above does not allow you to resolve the issue, as per the information on the waitForLock phase in the section 'Long waitForLock phase' under Header 'Symptom' from question 6. 'How can typical savepoint issues be analyzed and resolved?' of the KBA 2100009 - FAQ: SAP HANA Savepoints, you can generate a runtime dump to collect more detailed information on the root cause of the long runtime of the waitForLock phase. The KBA 3014528 - How-To: Analyzing blocked savepoint runtime dumps gives specific information about analyzing runtime dumps in relation to locks and savepoints.
Automated analysis of blocked save point runtime dumps is possible using the SAP HANA Dump Analyzer Supportability Tool, see the section 'Savepoint Analyzer' from the KBA 2498739 - How-To: Analyzing Runtime Dumps with SAP HANA Dump Analyzer , for further information.
## Related Information
Using the SQL Statement Collection for Analysis and Health Checks [page 8]
SAP Note 1969700
SAP Note 2100009
SAP Note 1999930
SAP Note 1999998
SAP Note 3014528
SAP Note 2498739