How to reset the counter for expensive SQL statements in the Monitoring Dashboard

[Former Member]

Hi experts,

For some unknown reason, my team mate has left the Expensive SQL Trace 'on' in our production system a few months ago. By the time I noticed this and turned off the trace, there are already some 2582 detected SQL statements that we see whenever we open the monitoring dashboard.

My questions is, is there anyway we can reset this counter? Please see screenshot below for reference.

We have a chance to restart the system just a few weeks ago but this did not reset the counter. I know these statements can also be viewed via the "Data Preview" option of the view M_EXPENSIVE_STATEMENTS from SYS. Can the entries of this view be safely deleted and will this reset the counter?

Please advise. Thank you.

Regards,

ANG

[lbreddemann · Accepted solution]

AFAIK the counter is really just a simple count of the number of entries in system view M_EXPENSIVE_STATEMENTS. I am not aware of any specific 'reset'-function for that.

However, for your production system it is not recommended to switch the expensive statements trace off.

See 's SAP note

2180165 - FAQ: SAP HANA Expensive Statements Trace, Pnt. 8

Concerning the removal of entries - that's only possible indirectly as there is no option to DELETE FROM or TRUNCATE the view.

As laid out in

2088971 - How-to: Control the Amount of Records in SAP HANA Monitoring Views

the expensive statements view collects data into memory and also into trace files.

You can delete those trace files (expensive_statement...) and restart the instance to release the file handles and to clear the data collected to memory.

[lbreddemann · Accepted solution]

Arrgghh... forget what I wrote!

As of SPS 7 there is a convenient command available for that:

alter system clear traces ('EXPENSIVESTATEMENT');

That clears the content of the expensive statements trace w/o any fuzz.

[Former Member · Accepted solution]

Hi Lars,

Thank you very much for that! I tried it in our sandbox and it worked!

I also found the main article for this command, in case anyone is interested:

ALTER SYSTEM CLEAR TRACES - SAP HANA SQL and System Views Reference - SAP Library

I am also reading the SAP note you provided regarding just allowing the expensive sql statement trace to 'ON'. By default, the threshold duration is 1000000 micro seconds (1 sec). Do you have any recommendation for the threshold or is 1 sec enough?

Regards,

ANG

[lbreddemann · Accepted solution]

I'd say it's a good starting point.

Depending on the "normal" level of statement run time in your system, you might want to use a shorter or longer time to capture all the "interesting" but not too many of the "uninteresting" statements.

One approach I used for some time was to check the run times of the statements in the SQL cache and look for the value that still includes say, the top 5 percentile of total run time.

But this is really fine tuning. Using one second as a start threshold will likely be fine for most of the cases.