Sap hana memory usage explained
Here is a brief description of memory usage. Explanations start from physical memory up to specific sap hana memory usage. More advance explanations, charts and SQL links can be found further down this page.
Host Server Physical Memory:
This is the total physical memory size actually installed on the machine. Free memory slots which might still be available on the server, are not counted as part of the physical memory.
This is the actual physical memory being used to run sap hana and the OS server.
Sap Hana Virtual Memory:
This is a size of memory that is dedicated to run sap hana. It is not real memory. It is reserved space from the physical memory for sap hana processes.
Sap Hana Pool Memory:
This is the global sap hana memory that is being allocated. Pool memory is filled with data, processes and queries, code and stack. Free memory is also part of the pool.
Sap Hana Used Memory:
This is the total sap hana memory usage. Database objects such as data table will be part of this memory usage. Queries and working operations come and go from the memory as needed. Those movements increase and shrink used memory size all the time.
Sap Hana Peak Used Memory:
This is the total sap hana memory usage that has reached an unusual high size in the total available memory pool. Peak Used Memory and Allocation limit are to be watch closely. One meeting the other is bad news.
Sap Hana Free Memory:
This is the memory available at the current time. The pool area size grows and shrinks depending on database activities.
3 elements in regards to memory areas.
1/ The physical memory,
2/ The Resident Memory,
3/ The Sap Hana used Memory
Server physical memory and Sap hana
Physical Memory: This is all memory available on the host server. It represents all memory bars which have been currently installed. It is up to you to check whether more memory bars could be added to the host in order to increase memory space. Keep in mind, at this level, memory has nothing to do with sap hana.
Sap hana and resident memory
Resident Memory: It represents the physical memory that is being used at the os level. All running processes, whether there are sap hana processes or OS program processes, are part of the resident memory.
Sap hana and virtual memory
Virtual Memory: This memory relies on existing physical memory. This specific sap hana RESERVED memory is not entirely allocated straight away. The memory manager requests memory from the OS using this reserved pool allocation, and does it until the memory allocation limit is reached. The total size and the growth depends on sap hana memory space allocation configurations and on sap hana running processes memory requirements. As it is being allocated it becomes part of the resident memory. The memory size goes up and down depending on data growth and on all temporary computational works.
Sap hana SQL management Tools
There are only few sap hana SQL tools on the market. Sap provides 2 types of SQL management tools. Sap hana Studio SQL console is a friendly client interface software which provides the most advanced SQL capability on sap hana databases. Hdbsql is a sap hana os command line sql tool which is mainly used and integrated within unix
Sap Hana and Used Memory
Used Memory: This is the total memory size that is currently used by sap hana. Within used memory you will find, data such as row and column tables, delta data storage, code and stack, all queries and temporary computational work. Used memory size is not static. It fluctuates up and down depending on temporary activities such as application query requests.
Sap Hana Peak Used Memory Investigation
Peak used memory is perfectly normal in most situations. However, it would be a good idea to check and follow the memory whenever a batch or a query is being run in an exceptional situation, especially if no particular validation has been performed up front.
The following example shows normal peak used memory ( in blue ) and peak memory reaching a zone where alert messages will be triggered. ( in Red ). In the current example more memory can be added via new physical memory and via new sap license. This will stop alert messages and possible problem can be avoided.
Sap Hana Peak Used Memory reaching deadly memory limit
Peak used memory reaching deadly threshold is very serious as the current database could become unstable and crash. Other databases running on the same SYSTEMDB could also be affected and follow the same fate.
In the current example, there is no way out to prevent a database crash unless the high memory usage reason is being sorted out. It is a critical situation especially because adding new physical memory is not possible. In this example, queries are likely to be the reason of high memory usage.
Sap hana and free memory availability
Free memory will always be available unless nothing is done to prevent memory shortage. Sap hana memory would have been sized initially according to data requirements and data growth estimation.
There should be plenty of free space in a sap hana healthy system.
However, memory can become suddenly short in the following situations:
A common and regular problem is a peak memory usage which takes most of the free memory in order to run a specific job requirement. It is happening in most cases because queries were not optimized and validations were not performed. Keep in mind, when too many bad queries are executed at the same time, the overall tuning may become impossible. Tuning single query is therefore very important.
Memory shortage could also be shown in an healthy system, where data is being added month after month, year after year and one day memory alerts start to show up. That situation can be as difficult to sort out. If hardware can accommodate more physical memory, there is no real issues. Just getting a new sap license and new physical memory. Otherwise, it become a database migration project from one host to another bigger one. That will not be possible in a short time scale.