FAQ on SAP HANA Workload Management
1. What is SAP HANA workload management?
SAP HANA can be used for various types of workloads, such as:
- OLAP workload, for example reporting in BW systems
- OLTP workload, for example transactions in an ERP system
- Mixed workload, meaning both OLAP and OLTP, for example modern ERP systems with transactions and analytical reporting
- Internal workload in SAP HANA, for example merges, backups and savepoints
Workload management in SAP HANA allows you to balance and manage all workload types, OLAP, OLTP, mixed, and internal for optimal throughput and response times.
2. What kind of resources can become scarce in SAP HANA environments?
The following resources can become a bottleneck in SAP HANA environments:
| Resource | SAP | Limited by |
| Memory | 1999997 | Physical memory, SAP HANA global allocation limit |
| CPU and threads | 2100040 | Number of physical / logical CPUs SAP HANA parameters like max_concurrency or max_sql_executors |
| Network | 2222200 | Network bandwidth and latency |
3. How can workload management be configured for memory?
The following options exist to configure workload management for memory:
| Memory limit for | Available as of | Details | ||||||||||
| SQL statements | SPS 08 | Starting with SPS 08 you can limit the memory consumption of single SQL statements. As a prerequisite you need to have the statement memory tracking feature enabled (see SAP doc1999997 -> "Is it possible to monitor the memory consumption of SQL statements?"). Additionally you have to set the following parameter in order to define the maximum permitted memory allocation per SQL statement and host: global.ini -> [memorymanager] -> statement_memory_limit =Changes to the parameter take effect immediately, no restart is required. Be aware that the limit is host specific. If you e.g. set the limit to 150 GB and you use a scale-out scenario, one SQL statement can allocate up to 150 GB per host. So the overall memory size (as e.g. displayed in M_EXPENSIVE_STATEMENTS) can significantly exceed the configured limit. You should test the effects of these settings carefully in order to avoid unexpected results (e.g. termination of backups or critical business queries). In general it is useful to start with a rather high memory limit. You can e.g. take 30 % of the first 500 GB of the global allocation limit and 15 % of the remaining memory as a starting point:
A statement that exceeds the configured statement memory limit will terminate with an OOM dump ("compositelimit_oom"). See 2122650 for more information. As of SPS 09 the following parameter is available that can be used to make sure that the statement_memory_limit only takes effect if the overall SAP HANA memory allocation exceeds a defined percentage of the global allocation limit: global.ini -> [memorymanager] -> statement_memory_limit_threshold = | ||||||||||
| SQL statements of specific database user | SPS 09 | As of SPS 09 you can also specify a database user specific statement memory limit: ALTER USER SET PARAMETER STATEMENT MEMORY LIMIT = '' | ||||||||||
| SQL statements of specific workload class | SPS 10 | Starting with SPS 10 you can define a workload class with a specific statement memory limit setting: CREATE WORKLOAD CLASS "" SET 'STATEMENT MEMORY LIMIT' = ''ALTER WORKLOAD CLASS "" SET 'STATEMENT MEMORY LIMIT' = '' Attention: The value unit is MB while for the options further above it is GB. See "How can workload classes be mapped to users and applications?" below for further information about workload classes. |
4. How can workload management be configured for CPU and threads?
Controlling CPU consumption is closely linked to controlling the number of active SAP HANA threads, because in most cases an active thread consumes CPU (exception: passive lock waits).
If you face a very high SAP HANA CPU consumption, you should always check in the first place if it can be limited by performance tuning approaches. There were already cases when the creation of a single SAP HANA index reduced the CPU consumption from 100 % to less than 10 %. Only if you have made sure that the high CPU consumption is required and can't be reduced via technical tuning, you can control and limit it in the following ways. .ini is can be used to control the focus of the parameter change, e.g.:
- global.ini: Activates the limitation for all services
- indexserver.ini: Activates the limitation only for the indexserver process
| Action | Availability | Parameter | Default | Restart required | Details |
| Limit number of SQL executors | general | .ini -> [sql] -> sql_executors | 0 (-> Number of logical CPU cores) | no | SQL executors are threads, which are responsible for normal SQL request processing. The number of SQL executors can be configured with this parameter. This parameter is less strict than the max_sql_executors parameter described below, because it is possible that the actual number of used SQL executors exceeds the configured value. |
| Limit maximum number of SQL executors | general | .ini -> [sql] -> max_sql_executors | 0 (-> No limit) | no | SQL executors are threads, which are responsible for normal SQL request processing. The maximum number of SQL executors can be configured with this parameter. Setting max_sql_executors introduces a hard limit. If for some reasons the defined value needs to be exceeded, the transaction is terminated with an error like: exception 1: no.71000132 (ptime/session/tcp_receiver.cc:849) max number of SqlExecutor threads are exceeded: current=111, max=110Therefore this parameter should only be used in very rare cases. |
| Limit CPU consumption of job workers | general | .ini -> [execution] -> max_concurrency | 0 (-> Number of logical CPU cores) | no | Job workers are threads, which are responsible to process parallelized OLAP load and internal activities like savepoints or garbage collection. The maximum number of logical CPUs consumed by JobWorkers can be configured with this parameter. If each active job worker currently consumes less than 100 % CPU, more job workers than the max_concurrency value can be activated. The defined number of logical CPUs may be exceeded if the active job workers increase their CPU consumption over time. |
| Limit parallelism of CHECK_TABLE_CONSISTENCY (not started by ESS) | >= Rev. 97.01 | indexserver.ini -> [table_consistency_check] -> check_max_concurrency | 0 (-> Number of logical CPU cores) | no | This parameter controls the number tables analyzed in parallel using the consistency check CHECK_TABLE_CONSISTENCY (unless triggered by the statistics server). See SAP doc2116157 for more information. On top of this client side parallelism the analysis of a single table may be parallelized on SAP HANA server side (limited by the max_concurrency parameter). |
| Limit parallelism of CHECK_TABLE_CONSISTENCY ( started by ESS) | >= Rev. 97.01 | indexserver.ini -> [table_consistency_check] -> internal_check_max_concurrency | 0 (-> max(1, min (8, number of logical CPU cores / 10))) | no | This parameter controls the number tables analyzed in parallel using the consistency check CHECK_TABLE_CONSISTENCY when triggered by the statistics server. See SAP doc2116157 for more information. Per default a maximum of 8 CPUs is used - and even less if there are less than 80 CPU cores on the node. On top of this client side parallelism the analysis of a single table may be parallelized on SAP HANA server side (limited by the max_concurrency parameter). |
| Limit parallelism | general | .ini -> [execution] -> max_concurrency_hint .ini -> [parallel] -> num_cores | Number of logical CPU cores (SPS <= 08) max_concurrency (SPS >= 09) | no | Similar to max_concurrency these parameters help limiting the number of used job worker threads. It defines the number of jobs to create for an individual parallelized operation. For one statement, there can be several such operations, so the total number of jobs being created for a statement will typically exceed the value of this parameter, as well as the number of worker threads being used for these jobs. For historic reasons these two parameters exist, but they are used for the same purpose. You should generally use identical values for both parameters. |
| Limit garbage collection parallelism | general | .ini -> [persistence] -> max_gc_parallelity | 0 (-> Number of logical CPU cores) | yes | Garbage collectors clean up no longer needed information on a regular basis. The parallelism of them is controlled by the max_gc_parallelity parameter. Per default a rather high parallelism is used which can result in a high system CPU consumption. By reducing the parameter you can reduce this overhead. Be aware that a small value can result in garbage collection delays, a growing persistency and "database full" situations. See SAP doc2169283 for more information. |
| Limit parallelism of table redistributions | >= Rev. 73 | indexserver.ini -> [table_redist] -> num_exec_threads | 10 (SPS <= 10) | no | This parameter controls the number of threads that are started for a table redistribution (SAP doc 2081591). Larger values can reduce the overall table redistribution time, but at the same time it increases the resource consumption in terms of memory, CPU and I/O. |
| Assign CPUs to services | >= Rev. 90 | daemon.ini -> [] -> affinity | No assignment | yes | It is possible to limit the CPU consumption per service (e.g. statisticsserver, indexserver) by adjusting the affinity parameter in the related daemon.ini section. See the SAP HANA Troubleshooting and Performance Analysis Guide for more information related to CPU affinity adjustments. |
Additionally the following options exist:
| CPU and / or thread limit for | Available as of | Details |
| SQL statements of specific workload class | SPS 10 | Starting with SPS 10 you can define a workload class with a specific thread limit setting: CREATE WORKLOAD CLASS "" SET 'STATEMENT THREAD LIMIT' = ''ALTER WORKLOAD CLASS "" SET 'STATEMENT THREAD LIMIT' = '' The default value is 0 which means no limitation. See "How can workload classes be mapped to users and applications?" below for further information about workload classes. |
5. How can workload management be configured for network?
No SAP HANA network workload management exists. If network becomes a bottleneck due to bandwidth or latency limitations, it can usually be resolved by optimizing the network infrastructure and the application design. See 2222200 for more information regarding SAP HANA network topics.
6. How can database requests be prioritized?
Usually all database requests have the same priority. In order to prioritize requests, you can use the following approach:
| Prioritization of | Available as of | Details |
| SQL statements of specific database user | SPS 09 | As of SPS 09 you can specify a database user specific statement priority: ALTER USER SET PARAMETER PRIORITY = ''The priority can vary between 0 (lowest priority) and 9 (highest priority). The default value is 5. |
| SQL statements of specific workload class | SPS 10 | Starting with SPS 10 you can define a workload class with a specific priority: CREATE WORKLOAD CLASS "" SET 'PRIORITY' = ''ALTER WORKLOAD CLASS "" SET 'PRIORITY' = '' The priority can vary between 0 (lowest priority) and 9 (highest priority). The default value is 5. See "How can workload classes be mapped to users and applications?" below for further information about workload classes. |
7. How can workload classes be mapped to users and applications?
As described above, you can define workload classes with a defined priority and with limitations in terms of memory and threads as of SAP HANA SPS 10. It is possible to define multiple properties at once, separated by comma:
CREATE WORKLOAD CLASS "" SET 'PRIORITY' = '', 'STATEMENT THREAD LIMIT' = '', 'STATEMENT MEMORY LIMIT' = ''
In the second step the defined workload class can be mapped to specific contexts. The following workload mapping options exist:
| Context | Details |
| CLIENT | Client number (typically the SAP ABAP client) |
| APPLICATION NAME | Application name (e.g. 'ABAP:' for SAP ABAP or 'HDBStudio' for SAP HANA Studio) |
| APPLICATION USER NAME | Application user name (e.g. SAP ABAP end user) |
| USER NAME | Database user name (e.g. SAP for SAP ABAP) |
The following command can be used for the workload mapping. You can omit properties in the SET clause if you only want to specify a subset:
CREATE WORKLOAD MAPPING "" WORKLOAD CLASS ""
SET 'CLIENT' = '', 'APPLICATION NAME' = '', 'APPLICATION USER NAME' = '', 'USER NAME' = ''
Example: Limiting the maximum thread parallelism for SAP application users SAPBATCH1 and SAPBATCH2 to 30
CREATE WORKLOAD CLASS "WLC_30THREAD" SET 'STATEMENT THREAD LIMIT' = '30';
CREATE WORKLOAD MAPPING "WLM_SAPBATCH1" WORKLOAD CLASS "WLC_30THREAD" SET 'APPLICATION USER NAME' = 'SAPBATCH1';
CREATE WORKLOAD MAPPING "WLM_SAPBATCH2" WORKLOAD CLASS "WLC_30THREAD" SET 'APPLICATION USER NAME' = 'SAPBATCH2';
Changes to workload classes don't have immediate effects on existing workload mappings. You have to recreate the workload mappings to activate the new settings.
8. Where do I find information about configured workload classes and mappings?
Information about configured workload classes and mappings are available in views WORKLOAD_CLASSES and WORKLOAD_MAPPINGS. Alternatively you can use SQL: "HANA_Workload_WorkloadClasses" (SAP doc1969700).
Example output:
9. What happens if multiple workload classes match to a specific database context?
If multiple workload classes match a database context, the following rules apply:
- If one workload class matches more properties than the other, that workload class is used.
- If multiple workload classes matches the same number of client properties, the following prioritization is used:
- APPLICATION USER NAME
- CLIENT
- APPLICATION NAME
- USER NAME