16 Mar SAP HANA User Based Memory Limits
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”
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.
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.
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.
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.
Kyle McAdam
Posted at 19:02h, 29 JuneHi 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 FebruaryThanks 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 JanuaryMike, 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 FebruaryThanks, great catch. I have updated to GB.
mp3juice
Posted at 22:37h, 04 JuneAn 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 JuneHey Mike. We meet again. A very useful read and exactly the kind of explanation that I was looking for.
Shayna
Posted at 07:52h, 07 MarchI have read so many articles or reviews on the topic of the blogger lovers however this paragraph is actually a good paragraph,
keep it up.