Quick Tip: SAP S/4HANA Database Tuning – Limiting Memory Consumption of SQL Statements

Hopefully I can describe simply and visually how important setting a couple of parameters is, for the health of your SAP HANA Database, after we found out the hard way, following reports from users that the S/4 system was unusable/they were unable to log in!

Let me explain how two parameters work together, to protect you HANA Memory usage from maxing out, or worse, breaching your licensed limit.

Parameter 1: statement_memory_limit

The simple explanation for this parameter is that it defines the maximum amount of memory per single SQL statement.

Example – If you set the statement_memory_limit to 100GB, then any SQL statement that exceeds this limit, will terminate.

One thing the SAP Documentation doesn’t tell you, is that the S/4 system doesn’t interpret this parameter, so will Short Dump wherever in the ABAP code it was executing at the time.

Parameter 2: statement_memory_limit_threshold

If you want to be a little more flexible than a ‘hard’ limit of 100GB per SQL statement, you can set a percentage value of your Global Allocation Limit, that allows the statement limit to be ignored, provided there is enough memory left in the system. But first….

You need to know your HANA Global Allocation Limit (GAL). The GAL is the maximum amount of memory you want the entire HANA system to consume at any one time. Essentially, it should be less than your licensed amount, so you don’t run into costly license breaches…

Let’s work an example:

I’ve set my GAL to 500GB

If I set my statement_memory_limit_threshold to 50% (250GB), this means that as long as my ‘already consumed’ total memory is less than 250GB, I can allow a statement to be executed that is more than the statement_memory_limit (set to 100GB).

The below screenshot highlights the extreme memory peaks and troughs, before we applied the parameter changes. It also shows that we still have peaks and troughs, but the HANA system memory is being managed much more effectively.

We are now focusing on the culprit SQL statements, that trigger the threshold limit. We can then provide analysis to the Dev and Functional Teams, to tune their programs.

If you are involved in a HANA or S/4 implementation, check with your Basis Team that you have these parameters set.

Feel free to message me if you have any questions.