SAP HANA – Managing Workload with Workload Classes
The load on an SAP HANA system can be managed by selectively applying limitations and priorities to how resources (such as the CPU, the number of active threads and memory) are used. Settings can be applied globally or at the level of individual user sessions by using workload classes.
In this article I try shortly show You how to use workload classes and workload class mappings. Let’s start.
How to create Workload Class
At the beginning important information for You. Workload class settings override other configuration settings, e.g. user parameter settings and other configuration settings (ini file values). Appropriate workload parameters (attributes) are then dynamically applied to each client session. What attributes do I mean, for example:
- priority;
- statement thread limit;
- statement memory limit;
- total statement thread limit;
- total statement memory limit;
- statement timeout;
- write transaction timelife;
- idle cursor lifetime.
How to use it:
CREATE WORKLOAD CLASS "MyMainClass_DevADM" SET 'STATEMENT MEMORY LIMIT' = '200' , 'STATEMENT THREAD LIMIT' = '20';
The above example says that single statement execution is limited to 200GB memory consuming and 20 of parallel JobWorker threads per statement and process.
Now we can create workload mapping. Mappings link workload classes to client sessions depending on the value of a specific client information property. What we can mapping:
- object name;
- schema name;
- xs user application name;
- application user name;
- client;
- application component name;
- application component type;
- application name;
All above properties we can check, for example in HANA Studio, tab: performance -> threads
Below You can see few examples:
CREATE WORKLOAD MAPPING "MyMainMapping_DevADM_1" WORKLOAD CLASS "MyMainClass_DevADM" SET 'USER NAME' = 'USERNAME’ CREATE WORKLOAD MAPPING "MyMainMapping_DevADM_2" WORKLOAD CLASS "MyMainClass_DevADM" SET 'APPLICATION USER NAME' = 'UI%’ WITH WILDCARD; CREATE WORKLOAD MAPPING "MyMainMapping_DevADM_3" WORKLOAD CLASS "MyMainClass_DevADM" SET 'APPLICATION NAME' = 'HDBStudio';
Others examples
-- change workload mapping ALTER WORKLOAD MAPPING "MyMainMapping_DevADM_2" WORKLOAD CLASS "MyMainClass_DevADM" SET 'APPLICATION NAME' = 'AMI%' WITH WILDCARD; -- delete workload mapping DROP WORKLOAD MAPPING "InnogyMainMapping_Others" ;
The following system views allow you to monitor workload classes and workload mappings:
WORKLOAD_CLASSES
WORKLOAD_MAPPINGS