cancel
Showing results for 
Search instead for 
Did you mean: 

SAP HANA Used Memory Not released after a bad query

ashok_oleti
Participant
0 Kudos

Hi,

We are running HANA database (102.05) with almost half TB RAM and around 29GB of column store tables and 19MB of Row Store tables in our dev environment.

I frequently observed that the HANA Used Memory is not being released after completion of the query whenever any bad query/statement/Calculation view is executed. To further analyze the issue I have ran many reports and looked at different sources and that investigation led me into lot many other questions.

On the HANA Studio overview screen I can see that the used memory is at around 127 GB and the other report showed the memory usage breakup with the component "Statement Execution & Intermediate Results" taking the top spot with around 70 GB memory consumption and then the Column Store Tables at 53GB (But the actual Column store is only 29GB).

Image1: Screenshot from Studio - HANA Used Memory is 127 GB approx

Image2: Result screenshot of the Component wise memory usage for Index Server (Port 30003)

Image3: Column Store table size

Image4: Row Store table size

On the other hand the below query shows me that the total Memory consumption by HANA is at 134 GB but the studio overview shows only 127 GB (Imange1)

Image5: Total HANA Memory usage

Image6: Memory usage by individual Services

After looking at all these sources with different results, I have got below questions/doubts

1) Is the component "Statement Execution & Intermediate Results" (Image) the culprit for HANA Used memory not being released? Is this a bug or do I have to tune any parameter or run query to release the Used Memory?

2) Why is there difference between the Column Store (Image3) and Row Store (Image4) tables sizes shown with the individual queries and the result shown in the Component wise memory usage report ( Image2)

3) Why is there a difference between the HANA Used memory shown on studio (Image1) and the result obtained from the query (Image5)

4) Image 6 shows few services have Used Memory higher than Resident Memory. Is this possible? My understanding was that the Used memory and Resident memory could be equal or Resident memory could be higher than the Used Memory but looks like I am missing something.

5) In ABAP stack systems we have a parameter to let the work process to restart after it consumes certain amount of heap memory but I am not sure if any such parameter available in HANA to release the memory after memory usage reaches certain limit per query/statement or the whole database. I am sure HANA has its own memory management mechanism without the need for such parameters but just asking..

Regards,

Ashok Oleti

Accepted Solutions (1)

Accepted Solutions (1)

lucas_oliveira
Advisor
Advisor
0 Kudos

Hi Ashok,

I think you should take a deep look into the admin guide section 'SAP HANA Memory Usage and the Operating System' and SAP Note 1999997 - 'FAQ: SAP HANA Memory'. The later one will give you better queries to evaluate memory. These are the greatest source of explanation on HANA Memory I can think of right now.

Also, there are quite a few discussions regarding this topic here in SDN so please take a deeper look into other discussions before opening a new one. Please keep up with .

In short, memory used is generally what should be evaluated. There are different allocators that could be involved into the query from Image2 and that's probably why you see a difference on your pictures.

Finally, HANA has mechanisms to stop queries based on memory limits and flexible workload classes. SAP Note 1999997 and 2222250 should be sufficient to explain how they work and how to activate them.

I can't speak for the other points but I'm sure you can reach an explanation after reviewing what the query is doing (JDBC trace for the win ) and checking the documentation suggested.

I hope that helps.

BRs,

Lucas de Oliveira - moderator

ashok_oleti
Participant
0 Kudos

Hi Lucas,

Thanks for a quick response.

This looks like a bug in the databases version 100 to 102.05  and 110 to 112.01.

I found an SAP note 2312976 that explains this scenario and the blocked memory has got released after running the statement (ALTER SYSTEM CLEAR SQL PLAN CACHE;) recommended in the Note. We are currently on 102.05 and may have to upgraded to 102.06 for a permanent fix.

Regrads,

Ashok Oleti.

Former Member
0 Kudos

Hi Ashok,

  We are also experiencing the same issue.  We have tried with ALTER SYSTEM CLEAR SQL PLAN CACHE in our system, but no luck for us.  Does your issue resolved?  By the way we are on 102.06 version.


Thanks

Mohan

ashok_oleti
Participant
0 Kudos

Hi Mohan,

Yes the upgrade to 102.06 has fixed our Memory leak issue for now but you say you are facing the same issue on 102.06 then I am not sure if the issue will repeat in our landscape after sometime, we have completed our upgrade only a week back and were using the statement (ALTER SYSTEM CLEAR SQL PLAN CACHE;) until then and that worked well too for us to release the blocked cache memory .

The upgrade has also improved the memory consumption of the complex views we have and performance has improved a lot too.

Let me see if I can help you in this. Could you answer below questions

1) Which Service you found consuming/blocking huge memory? Is that Index Server?

2) Did you run the report to see component wise memory usage? Which component has Huge consumption apart from Column Store? In my case it was "Statement Execution & Intermediate Results" which was related to Index Server (30003)

3) What is the value configured for the parameter "plan_cache_size" under indexserver.ini-->[sql]

Regards,

Ashok Oelti.

Former Member
0 Kudos

Hi Ashok,

  Thanks for the response.  We have found an OSS note which belongs to BPC application bug for memory leaks, unfortunately that was not helpful.  I have created HIGH priority OSS message with SAP.  Please see my responses below:

1) Which Service you found consuming/blocking huge memory? Is that Index Server? - Yes it's in index server

2) Did you run the report to see component wise memory usage? Which component has Huge consumption apart from Column Store? In my case it was "Statement Execution & Intermediate Results" which was related to Index Server (30003)  - Yes we have ran the service memory consumption for "Statement Execution & Intermediate Results" went from 4.4GB to 147GB in 2 days.

3) What is the value configured for the parameter "plan_cache_size" under indexserver.ini-->[sql]  - Our configuration has value of '3221225472'.

Thanks

Mohan

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Mohan,

I faced similar issue, but along with high memory which did not get release we had the threads running under Performance tab and when we try to cancel it, they would not get cancelled ( we tried all options to cancel).They were in running state for 6 days. So we ended up creating oss and SAP mentioned that was a bug in the current version we have ( 102.05) and they will fix it in future release. Not sure which one as of now. So to solve this issue they asked us to take a restart of the system.

Regards,

Aravind.

ashok_oleti
Participant
0 Kudos

Hi Aravind,

Do you still have the issue? Would you be able to share what such threads show under the columns "Thread Type", "Thread Method", "Thread Detail", "Thread Status"

Regads,

Ashok O.

Former Member
0 Kudos

Hi Ashok,

No we did not encounter issue after that , so far yet..

For the first three days, thread was in running state.

After three days when we tried cancel, it still continued in the running(cancel requested) and not releasing the memory.

Thread type was SqlExecutor, thread method was ExecutePrepared.

After that we took restart ( again during restart we had to let garbage collection run for 2 hours) by default the threads were gone.

So SAP has mentioned that in future release they will fix the bug of thread cancellation.

Regards,

Aravind.