Did you know that global statement_memory_limit can be overridden by WORKLOAD CLASS HINT

ALTERING THE BEHAVIOR OF STATEMENT MEMORY LIMIT:

HANA has a parameter statement_memory_limit which is the maximum memory limit a statement can consume during its execution inside HANA. Any statements trying to consume beyond this limit will be failing with below error. In the below screen shot , the statement which requested more than 300GB (this is our statement memory limit in our system) had failed with “2048 Column Store Error: search table error: [9] Memory allocation failed” However the next statement completed successfully as it only consumed 103 GB and has ERROR_CODE=0.

However this behavior can be modified by below ways .

1.Increasing the statement memory limit at user level:

This can be done with the below SQL.

ALTER USER <username> SET PARAMETER STATEMENT MEMORY LIMIT = ‘0’ => This overrides the statement memory limit and this user can consume as much memory as required.

ALTER USER <username> SET PARAMETER STATEMENT MEMORY LIMIT = ‘500’ =>This user can consume upto 500 GB per statement over ridding global setting of 300GB

This behaviors can be reset by below query.

ALTER USER <user_name> CLEAR PARAMETER STATEMENT MEMORY LIMIT

2.Using WORKLOAD Classes:

A separate WORKLOAD class can be created with increased statement memory limit and the same can be tagged to the individual SQL statement like below.

CREATE WORKLOAD CLASS class1 SET ‘TOTAL STATEMENT MEMORY LIMIT’=’500’, ‘TOTAL STATEMENT THREAD LIMIT’=’50’, ‘PRIORITY’ = ‘5’;  =>Here this workload class is assigned with statement memory limit of 500 GB and any statements which is tagged with HINT pointing to this workload class will have the privilege of using 500 GB.

How to use it in the SQL:

SELECT * FROM T1 WITH HINT( WORKLOAD_CLASS(“class1”) );

How does an SQL determines the usage of WORKLOAD classes , if it both has a valid workload class mapping and also a WORKLOAD HINT which is tagged with this SQL? Can the corresponding behavior be altered ?

WORKLOAD HINT comes into picture only if it supplies more restriction that the already tagged WORKLOAD CLASS. Otherwise the HINT is ignored and the WORKLOAD CALSS mapping comes into picture .For example ,If WORKOAD HINT applies more restrictive limits than those already defined and by default workload class hints can only be used in this way; the hint is ignored if any of the new values weaken the restrictions or if any values are invalid. We can change this default behavior, however, by switching the following configuration parameter in the session_workload_management section of the indexserver.ini file: allow_more_resources_by_hint. If this parameter is set to True then any hint can be applied.

Thanks for reading!

Like and leave a comment if it was useful!