last updated: 2023-04-16 11:40 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:
back to part I
back to part II
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.
Note User connect => connection ID will be mapped => every connection can create multiple threads Session defintion: combination of connection, thread (i.e. actual execution on SAP HANA side), SQL statement and transaction. Thread definition: worker process on HANA side which executes the work in SQL or job worker threads. |
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
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]
| Field Name | Description | SPS5 | SPS6 | SPS7 |
|---|---|---|---|---|
| OBJECT NAME | Object types PROCEDURE, PACKAGE and AREA are supported. If more than one workload class is matched by the OBJECT NAME then the more specific object type has the higher priority: PROCEDURE > PACKAGE > AREA. | yes | yes | yes |
| SCHEMA NAME | Schema name of object defined in the OBJECT NAME property. | yes | yes | yes |
| XS APPLICATION USER NAME* | Name of the XS application user. For XSA applications which use the session variable XS_APPLICATIONUSER for the business user value. | yes | yes | yes |
| APPLICATION USER NAME* | Name of the application user, usually the user logged into the application. | yes | yes | yes |
| CLIENT* | The client number is usually applied by SAP ABAP applications like SAP Business Suite / Business Warehouse. | yes | yes | yes |
| APPLICATION COMPONENT NAME* | Name of the application component. This value is used to identify sub-components of an application, such as CRM inside the SAP Business Suite. | yes | yes | yes |
| APPLICATION COMPONENT TYPE* | This value is used to provide coarse-grained properties of the workload generated by application components. | yes | yes | yes |
| APPLICATION NAME* | Name of the application (not application source!) | yes | yes | yes |
| USER NAME USERGROUP NAME | The name of the SAP HANA database user, that is, the 'CURRENT_USER' of the session of the database the application is connected to. Alternatively, you can use the name of a user group; if both user name and group are provided a validation error is triggered. The user name has a higher priority than the user group in cases where these properties are required to determine the best match. | yes | yes | yes |
| APPLICATION SOURCE* | For this property only the statement level variable is considered; the value for the variable must be set using the setCommandInfo() method. Session variable key name: M_PREPARED_STATEMENTS.APPLICATION_SOURCE. | no | no | yes |
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:
| Parameter | Value |
|---|---|
| STATEMENT THREAD LIMIT | To avoid excessive concurrent processing due to too many small jobs this property sets a limit on the number of parallel JobWorker threads per statement and process. |
| STATEMENT MEMORY LIMIT | To prevent a single statement execution from consuming too much memory this property sets a memory allocation limit in GB per statement. |
| TOTAL STATEMENT THREAD LIMIT | Similar to the STATEMENT THREAD LIMIT this property sets an aggregated thread limit which applies to all statements currently being executed within the workload class as a whole. |
| TOTAL STATEMENT MEMORY LIMIT | Similar to the STATEMENT MEMORY LIMIT this property sets an aggregated memory limit which applies to all statements currently being executed within the workload class as a whole. |
| STATEMENT TIMEOUT | This property applies a time limit after which any query which has not completed execution will time out generating the following error: ERR_API_TIMEOUT . |
| WRITE TRANSACTION LIFETIME | This property sets a time value to limit the lifetime of long-running uncommitted write transactions. |
| IDLE CURSOR LIFETIME | This property sets a time value to limit the lifetime of long-lived cursors. |
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 a 1000GB limit not each child has an aggregated 1000GB limit.
Example with the above WLC:
Important is the time line! The aggregated limit kick only in inside the grey time frame. Even if BI App3 can allocate 600GB for one SQL, it is not possible, due to the fact that all other processes also allocate so much memory. New session will be canceled or queued in this time. After the first BI App2 ends, new resources can be allocated by existing or new SQLs.
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.
Example with the above WLC:
Again - important is the time line! The aggregated limit kick only in inside the grey time frame. Even if BI App3 can allocate 60 Threads for one SQL, it is not possible, due to the fact that all other processes also allocate so many threads. New session will be canceled or queued in this time. After the first BI App2 ends, new resources can be allocated by existing or new SQLs.
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.
Note A child workload class can only have one single parent and cannot also be a parent. In a hierarchical relationship the aggregate properties (=total limits) are defined in the parent and cannot be defined in the child class. The corresponding individual limit must be defined in the child. This means:
A general limitation which applies to all workload classes is that you can combine the thread and memory properties in the same workload class, but you cannot combine the total properties and the single statement properties in the same class. (possible with parent + child) Scale-out: Restrictions defined in these workload classes are applied to each single node and not to the complete SAP HANA database. |
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 REPLACEImport 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.
- SAP Managed Tags:
6 Comments
