to understand the memory allocation and its usage it’s important to know how and when HANA decides to deal with the different memory areas.
Did you ever see a huge memory consumption and analyze that the root cause is not the amount of your actual CS/RS data? The root cause may be more located in the area of the heap memory. But how HANA handles this area and when it will be freed? Is the behaviour and the size normal or too big?
For better reading of the blog I have splitted it into three more parts:
For now only the “used memory” part is interesting. May be you heard about “resident memory” (note 2081473) => this is the OS view which is a not up-to-date value in cause of the deferred garbage collection / release of memory and can’t be taken as indicator.
“used memory” consists of:
- Table Data (Row Store + Column Store + System Tables)
- Code and Stack
- Database Management / Working Space (heap and shared memory)
Some more details to what is behind the more interesting parts shared and heap memory:
Changes in SPS12
Besides the parameter “unload_upper_bound” there are some more things which have been changed.
Why I go deeper into this topic? For the most companies SPS12 with its 3 years support is the main release for the next 1-2 years.
Go next to Part 3: tech. changes in SPS12
So it’s important to know a bit more what was changed. All this informations are already released, but still a check for the revision is missing, which issues/bugs are affecting your DB revision. That is the reason for this list (no guarantee for completion) refering to SPS12:
|Description||Allocator||Note||fixed in SPS11 Rev.
||fixed in SPS12 Rev.
|NUMA binding issues||2358255 and 2391198||–||122.02+|
|memory leak in the context of calculation scenario||Pool/malloc/libhdbcalcengine*||2374935||112.07||122.04+|
|Row table memory leak on SPS11 and SPS12||2362759||112.06+||122.02+|
|Metadata cache memory leak in SPS12||
|1999997 / 2402318||–||122.04+|
|PlanViz and the plan trace memory leak||Pool/planviz*||2405237||112.07||122.03+|
|Row store lock and version information in SPS11 and SPS12||Pool/RowStoreTables/LockTable||2391552||–||122.05+|
|Temporary row store tables||Pool/RowEngine/RSTempPage||2368929||112.07||120+|
|Spatial data memory leak||Pool/spatialcs||1999997||–||122.03+|
|High “Used memory” (see Part Heap Memory)||Pool/PersistenceManager/PersistentSpace/DefaultLPA/Page||2301382||–||122.05+|
|Join Engine Query Returns Unexpected Results in Aggregation of Large Intermediate Result Sets||2403088||–||122.05+|
After you know all the details, you still want to stay at a revision < 122.05? I think now you have enough reasons for an upgrade/update to get your needed downtimes (may be not, if you use DBSL suspend feature and zero downtime maintenance) from your business. Take care to deactivate all workarounds which you may have activated during the time and don’t forget the workarounds for the issues which are currently not fixed in 122.05.
At the end of the day it is not a wrong sizing or the huge growth of the DB which leads to a bottleneck.
Check out the main reason (follow note 2222718) and compare the affected allocators with note 1999997 for known bugs.
Appendix for more details
1999997 – FAQ: SAP HANA Memory
2301382 – High “Used memory” in Pool/PersistenceManager/PersistentSpace/DefaultLPA/Page after upgrade to HANA SPS11 or a higher SPS
2222218 – FAQ: SAP HANA Database Server Management Console (hdbcons)
SAP HANA Memory Usage Explained
2222718 – HANA Troubleshooting Tree – High Memory Consumption
2127458 – FAQ: SAP HANA Loads and Unloads
2395937 – SAP HANA SPS 12 Database Maintenance Revision 122.05
2325090 – SAP HANA DB: Known issues detected in SPS12
HANA Academy Youtube Channel
whats new with sap hana sps12 by sap hana academy
V1.1 Repaired destroyed pictures