SAP HANA User Based Memory Limits

16 Mar SAP HANA User Based Memory Limits

Mike Pink Mike Pink

SAP HANA User Based Memory Limits

SAP HANA SP9  allows for User Based Memory Limits to prevent system Out of Memory (OOM) situations. The ability to set user based memory limits presents another level of protection to customers running a SAP HANA database.  It is fairly basic, if a user executes a statement which requires more memory than they are allocated no memory is allocated and their statement is cancelled.

The parameter is called “Statement_memory_limit”. This parameter will help ensure a single user’s statement does not cause “Out of Memory” scenarios on the HANA appliance. A system wide “Statement_memory_limit” was introduced with HANA SP8. The system wide parameter proved not to be granular enough to set due to the different requirements between batch, developer, and end user statements.

Out of Memory situations can occur for a variety of reasons.  For example a developer can execute a Data Preview within Studio on a model with a very large data set or on a untested model which utilizes a huge amount of memory.  A batch process within SAP BW can also be very memory intensive.  Having the ability to enable user based memory limits is a great option to have available to protect your HANA environment.

Steps to Enable SAP HANA User Based Memory Limit:

1. Enable Resource Tracking

a. Login into HANA Studio and navigate to the “Configuration” tab.
b. Open “Global.ini–>resource_tracking”
c. Set parameter “enable Tracking” and “memory_tracking” to “on”

User_Limit_Image_2

2. Set system level Statement memory limit

a. Login into HANA Studio and navigate to the “Configuration” tab.
b. Open “Global.ini–>memorymanager”
c. Set “statement_memory_limit” = desired limit in MB’s

i. This limit will be system specific depending on the Global Allocation Limit, if the system limit has never been set and the goal is to only enable user specific limits set the limit to a very high percentage of the Global allocation limit. For example if global allocation limit is set to 234496 MB, set the Statement_memory_limit to 200000 MB. The system limit will never be met and basically ignored.

User_Limit_image_1

ii. The system limit HAS to be set to enable USER limits.

iii. If the desire is to only have the User Memory Limit applied when the system’s memory consumption exceeds a certain percentage of the Global Allocation Limit the “Statement_memory_limit_threshold” can be set.  For example if the Global Allocation limit is 512GB and the “Statement_memory_limit_threshold” is set to 80% the user limits will not be enforced until the system’s memory consumption is above 410GB.  If set to 0 the user limits will always be applied.

3. Enable user level Limit:

a. Login into HANA studio.
b. Navigate to Security–>Users and select the user the limit is required for.
c. Double click the user and go to the “User Parameter” tab.
d. Click the “+” button to add a new parameter.
e. Select the “Statement Memory Limit” parameter.
f. Enter a the desired CAP in GB and save user. Below example is set to 5 GB.

User_Limit_Set_user

4. Example of memory cap encountered:

a. A SQL statement was executed which required more the 5 GIGs of working memory which was the CAP set for the test user. The below error message is given to the user.

User_Limit_Memory_Exceeded

b. An addition a OOM file diagnosis file is generated at the time the memory limit is encountered.

More information on this setting and others can be found in the SAP HANA Admin Guide which can be found HERE

 

Mike Pink is a SAP Business Intelligence Architect. Over the years, he has helped numerous companies build robust and efficient reporting solutions.

 

6 Comments
  • Kyle McAdam
    Posted at 19:02h, 29 June Reply

    Hi Mike. Great article! This should be very useful for many SAP HANA environments. I get the question almost everywhere I go. Just one point to add for the statement_memory_limit_threshold.. >> This was in fact by design. We noticed, at SAP, that many customers were concerned with a gap… after the statement limitation was allowed with SP8, they had a lot of cases where, for example at night, memory usage was low, and they wanted a particular user to be able to use more memory than allowed by the set limit. So, with this new setting, the threshold will allow a system to have limits when memory is above the set threshold (as in SP8)and when more memory is available(below the threshold), the user(s) will not be limited.

    So, simply put, it is a switch, to turn the limits on and off, dynamically taking action, based on current memory usage..

    • Nate Crawford
      Posted at 22:28h, 02 February Reply

      Thanks for the clarification. I have since tested and updated the blog to correctly reflect the importance of the “Statement_memory_limit_threshold”.

  • Murali
    Posted at 20:33h, 22 January Reply

    Mike, looks like you mentioned that MB is parameter’s default setting in section 3. It would be GB. Please verify.

    • Nate Crawford
      Posted at 19:04h, 01 February Reply

      Thanks, great catch. I have updated to GB.

  • mp3juice
    Posted at 22:37h, 04 June Reply

    An impressive share! I’ve just forwarded this onto a coworker who has been doing a little homework on this.
    And he actually bought me lunch because I discovered it
    for him… lol. So let me reword this…. Thank YOU for the
    meal!! But yeah, thanx for spending some time to talk about this matter here on your web site.

  • Kanishka
    Posted at 08:08h, 06 June Reply

    Hey Mike. We meet again. A very useful read and exactly the kind of explanation that I was looking for.

Post A Comment

Get In Touch With Us



Fill out the form below, or give us a call at 612-251-9112. We would love to partner with you.

Become A Partner

If you have a strong SAP background and a
“Whatever it takes” attitude , submit your resume.