HANA spikes in memory and CPU

[Former Member]

Hi All,

We have a strange issue, is there somewhere historical information is stored for HANA related to CPU and memory spikes which were caused.

Suppose yesterday night at 10 PM there was a spike in CPU and at 8 PM there was a spike in memory.

Are there any statistic server tables which hold the history for the same?

[Former Member]

If I want to understand the historic database load at a certain point in time, I usually evaluate the thread samples using the SQL: "HANA_Threads_ThreadSamples_*" commands available via SAP Note 1969700. In terms of CPU spikes the analysis is often quite straight-forward (many samples -> much CPU), in terms of memory you sometimes need to guess. For memory you should also use SQL: "HANA_Memory_TopConsumers_History" to understand which memory area was particularly large during the peak. And you can define the statement_memory_limit (SAP Note 1999997) so that greedy SQL statements are terminated and logged.

[Former Member]

All your statistics tables are here _SYS_STATISTICS scheme which can be accessed via Studio > Administration View > Catalog > _SYS_STATISTICS & SYS  > Views > M_* Views have the stats data

Here is one way to trace them if it occurs the next time

HANA Studio > Administration > Configuration > Filter "expensive" and make the following changes

enable = true, threshold_duration = 5000000( 5 seconds or higher)

Now filter  "memory" and enabl memory_tracking = on

After you make these changes from now on all the slow performing queries are here

HANA Studio > Administration > Performance > Expensive Statements

blog is a good starting point which is a quick reference guide.

http://scn.sap.com/community/hana-in-memory/blog/2013/05/27/andy-silvey--sap-hana-command-line-tools...

[Former Member]

Hi Krishna,

Thanks for your response.

I am aware of the views which you have stated. There are tons of data in that.

I had checked if we have something similar to ST03n, what we have for ABAP.

Probably somebody from the forums would have faced the same issue.

[Former Member]

Hi,

Are you able to use the HANA Studio-->PERFORMANCE-->LOAD to generate the CPU and Memory usage graph, it has enough history for you to be able to pull last night data

Sunil

[Former Member]

Sunil,

I was asking what caused and not how to check it .