last updated: 2023-04-14 16:00 CEST

After we identified our bottlenecks, understood the workload and configured the right parameters for our instance, we have to check if we should go for a new HANA workload class or use the possibility for user parameters. At first you should know that your first choice should always be to use workload classes. You are more flexible and it is sure that there will be future improvements. This means user parameters are the old school way to limit resources, but however to check the impact it is a fast method before creating a workload class.

By default SPS06 is using workload classes for the ESS. To control the resources you should use workload classes for every important application which is needed for your core business. This means to take care that the important SQLs have enough resources and the correct priority, you should know your workload and adjust the restrictions to your needs.

How does it work? Are there limitations and disadvantages using workload classes? We will see:

1. Workload mapping of sessions
2. User parameters
3. Workload Classes
   1. Hierarchies
   2. Admission control
   3. Maintenance
   4. Disadvantages of workload classes

Workload mapping of sessions

If you are just doing some special tasks on the DB which are needing a lot of resources, just set the user parameter for memory or threads (CPU) and afterwards unset them. This means for temporary usage.

For permanent and flexible usage, you definitely should use a workload class.

How does it work? At every connect – this means for every session – the limitation will be mapped to each thread. If it cannot be mapped it is assigned to the default workload class. The configuration parameters associated with the workload class are read and this sets the resource variable in the session or statement context. Ok, but what means now connection, session and thread in detail.

This is a simplified illustrated picture:

WP0 is the connection from work process 0 of a SAP application server. All IDs are fictional. They are unique.

There are multiple attributes to incluence this limitations.  We already know some of them, but we do not know the order and priority of them. Here we go:

Source: © 2023 SAP SE or an SAP affiliate company. All rights reserved.

This means that the parameters are globally valid for all sessions, due to the default class mapping (_SYS_DEFAULT_). The user parameter will overrule the HDB parameters (*.ini). The workload classes will overrule all parameters (HDB + user). At the end the statement hints will overrule all others.

User Parameter

Are there currently any user parameters active in the system?

SELECT * FROM USER_PARAMETERS WHERE USER_NAME = 'SYSTEM';

Set user parameter for memory limitation for temp. usage:

ALTER USER <username> SET PARAMETER STATEMENT MEMORY LIMIT = '100';

This means the user can use for each session 100GB. This can result with 10 Threads or 10 different connections in 1000GB of memory allocation. If some of the threads is reaching the limit it will dump with a composite limit OOM dump.

Set user parameter for thread limitation for temp. usage:

ALTER USER <username> SET PARAMETER STATEMENT THREAD LIMIT = '40';

This means the user can use for each session 40 threads, which results in 40 logical CPUs. This can result again with different connects or more sessions e.g. 4 connects in sum in one point of time in 160 log. CPU.

Set user parameter for a different priority for temp. usage:

ALTER USER <username> SET PARAMETER PRIORITY = '9';

The default priority is 5. The possible values are from 0 (lowest) – 9 (highest).

Sources and further details

• Setting User Parameters for Workload
• Create User-Specific Parameters

Workload Classes

You must know that with every HANA SPS there are different possibilities. Currently the differences between SPS05 , SPS06 and SPS07 are the following:

• Hierarchy mapping with memory limits [since SPS06]
• admission control (queueing and rejecting) [since SPS06]
• total statement limits (memory + threads) can be defined individually in a workload class [since SPS06]
• total statement limit and single statement limit of different properties can be mixed in a workload class [since SPS06]
• Hierarchy mapping with thread limits [since SPS07]

I will not go deep into the difference because they are already available via the documentation. Attributes with “*” can be used with wildcards.

It is really important to know that SAP means with “mapping an object” not a table or a view. They mean procedure, package or area!

If you are not able not map the workload with the mentioned attributes, you have still the possibility to map it via statement hint to a workload class. Currently it is quite hard to map dynamically app sources as e.g. AMDPs. If a root statement starts own child processes with different SQL hashes it can happen that the limits are not inherited. This was observed with SPS05.

It is important to know that if a workload is not mapped the general valid parameters of the HANA system are active. If there is no filled session variable, the mapping can not be applied. All SAP applications (means BSoH, S/4HANA, BW, BW/4HANA etc. are included) have filled session variables. It can happen that third party tools are not following the SAP recommendation. This makes it difficult to map this workload. Use a special DB user for the connect or use a statement hint for the mapping.

Source: © 2023 SAP SE or an SAP affiliate company. All rights reserved.

The following parameters are available for limitations:

Details:

SAP Note 2331857 – SAP HANA workload class support for SAP client applications 

SAP Note 2215929 – Using Client Info to set Session Variables and Workload Class settings 

Hierarchies

So, with SPS06 there some new features. Hierachies are used to combine functions which could not be combined before. Total statement limit and single statement limit of the same property cannot be mixed in a workload class. This means if you want limit an application by single memory limit AND total memory limit, you can not do this with SPS05 at all. With SPS06 only with use of hierarchies. The same is not possible with total/single thread limit (prior SPS07). With SPS07 this is now also possible!

Source: © 2023 SAP SE or an SAP affiliate company. All rights reserved.

All child processes will inherit the total memory limit. Each child can then have individual statement limits (memory + thread). The total limit is valid for each child in total. This means all child processes in sum have 40GB not each child has 40GB.

Child A scenario: If 2 threads are active with each 20GB memory and 10 Threads. There can not be another process active allocating more memory (even not threads in child B). Depending on the WL class settings it will be canceled or queued (admission control inside a workload class).

Starting with SPS07 the same is possible with the total thread limit:

The default parameters are overruled. Each BI application has other individual limits and one total thread limit for all child processes in sum.

For sure both total limits (memory+thread) can also be combined in the parent:

All child processes in sum can consume 100 threads and 1000GB RAM, but have also some individual limits within each child.

Sources:

Hierarchies of Workload Classes

Properties for Workload Classes and Mappings

Admission Control

Hold on – admission control in a workload class? Yes, you can handle different applications or workloads not only generally. You can also e.g. queue a BW application or a third party tool at 60 or 70% CPU load. The general admission control also will be active later at 90% (by default) on top. But it’s better to control less critical workloads sooner than general admission control kicks in and the entire system suffers.

Even if there is not enough memory, the query will not be canceled immediately, but can also be queued.

Maintenance

Export / Import Workload Classes

EXPORT SYS.WORKLOAD_CLASSES, SYS.WORKLOAD_MAPPINGS AS CSV INTO '<PATH>' WITH REPLACE

Import via python script importWorkloadClass.py:

python importWorkloadClass.py --host='<host>' --SID='<SID>' --user='<DB-user>' --password='<PW>' <PATH>

Enable / Disable Workload Classes

You can disable/enable specific or all workload classes for temp. tests.

ALTER WORKLOAD CLASS '<Class Name>' {enable | disable};
ALTER WORKLOAD CLASS all {enable | disable}

Details:

CREATE WORKLOAD CLASS Statement

ALTER WORKLOAD CLASS Statement

Hardware changes / resizing

If you change the hardware / resize your VM instance, you have to consider that you also have to adjust the limitations of the workload classes. Otherwise your system cannot suffer from the additional resources.

Changed Coding / Workload

It is a frequent task to monitor the system and identify new workload. Code will change after a while and this means that SQL hashes or even App sources / names will change or occur as brand new in the system. Unmapped workload will have the standard parameters and pritorities. This means 2-3 Health Checks in a year should be defined to identify such workloads.

Disadvantages of workload classes (status quo SPS07)

If all session will be started in a similar point in time they will receive the same limits. This means if you have a total limitations and 10 sessions of the same application will be started, they will receive the same limitation because during the time of session context mapping there was “no” load on the system. This means if the threads are started afterwards they have the same limitations as the root/father process. There is no further check if the thread is running.

• You can not use total / single thread limits with hierachies. (prior SPS07)
• Dynamically / generic changing attributes are difficult to map
• There is currently no possibility to map SQL hashes via the HANA cockpit as GUI. Only the SQL commands can be used.
• wild cards are not possible for all attributes (e.g. user name or object)
• workload which starts multiple tasks at one point in time will have all the same limitations inside a workload class

This blog should give you an overview of what can be done with workload classes. There are still some scenarios which can not be covered. Every complex system should use workload classes to prevent the systems from overloading and make it possible to prioritize the resources. As valid for every feature: play around and find more details in the documentation.