HANA limitations

[former_member277556]

Hello,

We are running HANA 1.00.122.05 in multi tenant environment. One of few tenants ( biggest one ) is for ERP system.

Additional to that users can access HANA directly via Excel ( MDX selects ).

And here problems starts: Some users when trying to generate report manages to generate something big. So statement is running for ~2hours. In that time all free memory is allocated and stops only when reaches memory limit. At that time ERP system is going crazy as HANA DB is not capable to accept new data ( memory limit reached, roll-back in progress ).

So I was wondering, is it possible to limit such type of statements in HANA so it would kill it after reaching a set point of memory, instead of taking all.

Thanks!

[pfefferf · Accepted solution]

It would be possible to got the "hardcore" way by limiting the memory and parallel execution for all OLAP statements using following configuration options:

• Setting a Memory Limit for SQL Statements
• Controlling Parallel Execution of SQL Statements

But I would recommend to create Workload Classes which allows you to setup such configurations per client. For instance you could set up a default workload class, a workload class for ERP and a workload class for MDX queries. Details can be found in the official documentation: Managing Workload with Workload Classes

Regards,
Florian

[Former Member]

Hello Tomas -

Firstly I would suggest you setup auto alerts in the system to get to know about failures, who and when it happened.

We have had similar problems and we did setup regulation of data access via below means

- Statement Memory Limit (User Specific) -

- Statement Memory Limit (Statement Specific)

- Statement Memory Limit Threshold

Apart from this I would suggest you run these statements yourselves first via 'Plan Visualizer' and 'Explain Plan' if possible to understand how much memory is being consumed before they go out of control

Thanks

Abhishek Shanbhogue