Mechanism in HANA to kill runaway queries

[Former Member]

Hello Everyone,

Sometimes we face issues with bad sqls hitting the DB and taking pretty long time to execute. Most of the times, these sqls are result of bad coding or incorrect index.

My question is : If there is any mechanism in HANA which would automatically kill such runaway queries or bad sqls running without having to restart the system or the DB?

Any advice is appreciated

Regards,

Vinay

[lbreddemann]

Hey Vinay

searching this website with "kill session" brings up and amongst other threads.

Everything relevant to know about the topic can be found in there.

- Lars

[TomCenens]

Dear Vinay

There is a parameter that can help (since SPS8) which is designed to avoid having "wrong" queries consume massive amounts of memory. This could potentially help you also with the issue you are describing, if these statements do consume a lot of memory but I would assume they would.

"

Memory Tracking Parameters (New)

Statement memory tracking and statement memory limitation help you avoid uncontrolled statement memory

allocation

● The peak memory consumption of a statement can be tracked and displayed in Expensive Statements.

● Resource consumption collection has to be enabled manually:

Configuration file global.ini, parameters enable_tracking and memory_tracking

● Additionally the SAP HANA administrator is able to define a global statement memory limit in the

database:

Configuration file global.ini, parameter statement_memory_limit

● If a statement exceeds the memory limit, it will be canceled

"

Reference:

http://help.sap.com/hana/Whats_New_SAP_HANA_Platform_Release_Notes_en.pdf

Best regards

Tom

[patrickbachmann]

Hi Vinay,

When this happens I cancel operations on the calling thread in Administration console.  Once in a while I can not do this because the system is unresponsive but most of the time this works.

-Patrick