Setting up SAP HANA Workload Management

Objective

After completing this lesson, you will be able to set up SAP HANA workload management.

Introduction to SAP HANA Workload Management

Workload Management

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.

On an SAP HANA system, thanks to the capabilities of the platform, there are many different types of workload, from simple or complex statements to potentially long-running data loading jobs. These workload types must be balanced with the system resources that are available to handle concurrent work. For simplicity, we classify workload queries as transactional (OLTP) or analytic (OLAP). With a transactional query the typical response time is measured in milliseconds and these queries are normally executed in a single thread. However, analytic queries normally feature more complex operations using multiple threads during execution: this can lead to higher CPU usage and memory consumption compared with transactional queries.

To manage the workload of your system aim to ensure that the database management system is running in an optimal way given the available resources. The goal is to maximize the overall system performance by balancing the demand for resources between the various workloads: not just to optimize for one particular type of operation. If you achieve this, requests will be carried out in a way that meets your performance expectations and you will be able to adapt to changing workloads over time. Besides optimizing for performance, you can also optimize for robustness so that statement response times are more predictable.

Workload in the Context of SAP HANA

Workload in the context of SAP HANA can be described as a set of requests with common characteristics.

We can look at the details of a particular workload in a number of ways. We can look at the source of requests and determine if particular applications or application users generate a high workload for the system. We can examine what kinds of SQL statements are generated: are they simple or complex? Is there a prioritization of work done based on business importance, for example, does one part of the business need to have more access at peak times? We can also look at what kind of service level objectives the business has in terms of response times and throughput.

Diagram showing the types of workload in the context of SAP HANA: 1. Analyze System Performance. 2. Understand your Workload. 3. Map your Workload to System Resources, as described in the preceding and following text.

The figure shows different types of workload such as Extract Transform and Load operations (used in data warehouses to load new data in batches from source system) as well as analytic and transactional operations.

When we discuss workload management we are really talking about stressing the system in terms of its resource utilization. The main resources we look at (shown in the previous figure) are CPU, memory, disk I/O, and network. In the context of SAP HANA, disk I/O comes into play for logging, for example, in an OLTP scenario many small transactions result in a high level of logging compared to analytic workloads (although SAP HANA tries to minimize this). With SAP HANA, network connections between nodes in a scale out system can also be optimized, for example, statement routing is used to minimize network overheads.

However, when we try to influence workload in a system, the main focus is on the available CPUs and memory being allocated and utilized. Mixed transactional and analytic workloads can, for example, compete for resources and at times require more resources than are readily available. If one request dominates, there may be a queuing effect, so the next request may have to wait until the previous one is ready. Such situations need to be managed to minimize the impact on overall performance.

Options for Managing Workload

All of these options have default settings which are applied during the SAP HANA installation. These general-purpose settings may provide you with a perfectly acceptable performance, in which case the workload management features described in this chapter may be unnecessary. Before you begin workload management, ensure that the system generally is well configured: that SQL statements are tuned, that in a distributed environment tables are optimally distributed, and that indexes have been defined as needed.

If you have specific workload management requirements, the following table outlines a process of looking at ever more fine-grained controls that can be applied with regard to CPU, memory and execution priority.

AreaPossible Actions
CPUSettings related to affinity are available to bind server processes to specific CPU cores. Processes must be restarted before these changes become effective.
CPU Thread PoolsGlobal execution settings are available to manage CPU thread pools and manage parallel execution (concurrency).
MemoryGlobal memory manager settings are available to apply limits to the resources allocated to expensive SQL statements.
Admission ControlGlobal admission control settings can be used to apply system capacity thresholds above which SQL statements can be either rejected or queued.
Workload Class MappingA more targeted approach to workload management is possible by setting up preconfigured classes which can be mapped to individual user sessions. You can, for example, map an application name or an application user to a specific workload class. Classes include the option to apply a workload priority value.

Understand your Workload

Managing workloads can be viewed as an iterative three-part process: analyze the current system performance, understand the nature of your workload, and map your workload to the system resources.

There is no one single workload management configuration that fits all scenarios. Because workload management settings are highly workload dependent you must first understand your workload. The following figure shows an iterative process that you can use to understand and optimize how the system handles the workload.

  1. First, look at how the system is currently performing in terms of CPU usage and memory consumption. What kinds of workloads are running on the system? Are there complex, long running queries that require lots of memory?
  2. When you have a broad understanding of the activity in the system you can drill down to the details, such as business importance. Are statements being run that are strategic or analytic in nature, compared to standard reporting that may not be so time-critical? Can those statements be optimized to run more efficiently?
  3. When you have a deeper understanding of the system, you have a number of ways to influence how it handles the workload. You can map the operations to available resources, such as CPU and memory, and determine the priority that requests get by, for example, using workload classes.

Analyzing System Performance

You can use system views to analyze how effectively your system is handling the current workload.

This section lists some of the most useful views available which you can use to analyze your workload and gives suggestions for how to improve performance. Refer also to the scenarios section for more details of how these analysis results can help you to decide which workload management options to apply.

Analyzing SQL Statements

Use these views to analyze the performance of SQL statements:

  • M_ACTIVE_STATEMENTS
  • M_PREPARED_STATEMENTS
  • M_EXPENSIVE_STATEMENTS

If these views indicate problems with statements you can use workload classes to tune the statements by limiting memory or parallelism.

Consider also the setting of any session variables (in M_SESSION_CONTEXT) which might have a negative impact on these statements. The following references provide more detailed information on this:

  • SAP Note 2215929: Using Client Info to set Session Variables and Workload Class settings describes how client applications set session variables for dispatching workload classes.
  • SAP HANA Developer Guide (Setting Session-Specific Client Information)

Analyzing CPU Activity

Use these views to analyze CPU activity:

  • M_SERVICE_THREADS
  • M_SERVICE_THREAD_SAMPLES
  • M_EXPENSIVE_STATEMENTS.CPU_TIME (column)
  • M_SERVICE_THREAD_CALLBACKS (stack frame information for service threads)
  • M_JOBEXECUTORS (job executor statistics)

These views provide detailed information on the threads that are active in the context of a particular service and information about locks held by threads.

If these views show many threads for a single statement, and the general system load is high you can adjust the settings for the set of 'execution' ini-parameters as described in the topic Controlling Parallel Execution.

Controlling CPU Consumption

Controlling CPU Consumption

If the physical hardware on a host is shared between several processes, you can use CPU affinity settings to assign a set of logical cores to a specific SAP HANA process. These settings are coarse-grained and apply on the OS and process levels.

You can use the affinity configuration parameter to restrict CPU usage of SAP HANA server processes to certain CPUs or ranges of CPUs.

Diagram of SAP HANA CPU Affinity settings for assigning cores to a specific SAP HANA process, as described in the preceding and following text. For example, set indexserver.HB1 affinity to core 1-4, and set indexserver.HB2 affinity to core 5-8, as described in the preceding and following text.

Using the configuration option, we first analyze how the system CPUs are configured Then, based on the information returned, apply affinity settings in the daemon.ini file to bind specific processes to logical CPU cores. Processes must be restarted before the changes become effective. This approach applies primarily to the use cases of SAP HANA tenant databases and multiple SAP HANA instances on one server. You can use this, for example, to partition the CPU resources of the system by tenant database.

Note

As an alternative to applying CPU affinity settings you can achieve similar performance gains by changing the parameter max_concurrency in the section [execution] of the global.ini configuration file. This may be more convenient and can be done while the system is online.

To make the changes described here, you require access to the operating system of the SAP HANA instance to run the Linux lscpu command and you require the privilege INIFILE ADMIN.

Information about the SAP HANA system topology is also available from SAP HANA monitoring views as described in a later subsection, SAP HANA Monitoring Views for CPU Topology Details.

Hint

For more information, see SAP Note 2470289: FAQ: SAP HANA Non-Uniform Memory Access (NUMA).

For Xen and VMware, the users in the VM guest system see what is configured in the VM host. So the quality of the reported information depends on the configuration of the VM guest. Therefore, SAP cannot give any performance guarantees in this case.

Configuration Steps

To confirm the physical and logical details of your CPU architecture, analyze the system using the lscpu command. This command returns a list of details of the system architecture.

The following table gives an overview of the most useful values, based on a sample system with 2 physical chips (sockets), each containing 8 physical cores. These are hyper-threaded to give a total of 32 logical cores.

Line NumberFeatureExample Value

1

Architecture

x86_64

2

CPU op-mode(s)

32-bit, 64-bit

3

Byte Order

LittleEndian

4

CPUs

32

5

On-line CPU(s) list

0-31

6

Thread(s) per core

2

7

Core(s) per socket

8

8

Socket(s)

2

9

NUMA node(s)

2

21

NUMA node0 CPU(s)

0-7,16-23

22

NUMA node1 CPU(s)

8-15,24-31

  • Item 4-5: This sample server has 32 logical cores, numbered 0 to 31.

  • Item 6-8: Logical cores (threads) are assigned to physical cores. Assigning multiple threads to a single physical core is referred to as hyper-threading.

    In this example, there are two sockets, each socket contains eight physical cores (total 16). Two logical cores are assigned to each physical core, thus, each core exposes two execution contexts for the independent and concurrent execution of two threads.

  • Item 9: In this example there are two Non-uniform Memory Access (NUMA) nodes, one for each socket. Other systems may have multiple NUMA nodes per socket.

  • 21-22: The system numbers and assigns 32 logical cores to one of the two NUMA nodes.

Note

Even on a system with 32 logical cores and two sockets, the assignment of logical cores to physical CPUs and sockets can be different. It is important to collect the assignment in advance before making changes.

You can perform a more detailed analysis by using the system commands described in the next step. These commands provide detailed information for each core, including how CPU cores are grouped as siblings.

In addition to the lscpu command, you can use the set of system commands in the /sys/devices/system/cpu/ directory tree. For each logical core, there is a numbered subdirectory beneath the node (/cpu12/ in the following examples).

The following examples show how to retrieve this information. The following table provides details of some of the more useful commands:

Code Snippet
12
cat /sys/devices/system/cpu/present
cat /sys/devices/system/cpu/cpu12/topology/thread_siblings_list
CommandExample OutputCommentary
present0-15

The number of logical cores available for scheduling.

cpu12/topology/core_siblings_list4-7, 12-15

The cores on the same socket.

cpu12/topology/thread_siblings_list4,12

The logical cores assigned to the same physical core (hyper-threading).

cpu12/topology/physical_package_id1

The socket of the current core, in this case cpu12.

Other Linux commands which are relevant here are sched_setaffinity and numactl. The command sched_setaffinity limits the set of CPU cores available (by applying a CPU affinity mask) for execution of a specific process (this could be used, for example, to isolate tenants) and numactl controls NUMA policy for processes or shared memory.

Based on the results returned you can use the affinity setting to restrict CPU usage of SAP HANA server processes to certain CPUs or ranges of CPUs. You can do this for the following servers: nameserver, indexserver, compileserver, preprocessor, and xsengine. Each server has a section in the daemon.ini file.

The affinity setting is applied by the TrexDaemon when it starts the other SAP HANA processes using the command sched_setaffinity. Changes to the affinity settings take effect only after restarting the SAP HANA process.

The following examples show the syntax for the ALTER SYSTEM CONFIGURATION commands required.

Example 1

To restrict the nameserver to two logical cores of the first CPU of socket 0 (see line 21 in the example), use the following affinity setting:

Code Snippet
1
ALTER SYSTEM ALTER CONFIGURATION ('daemon.ini', 'SYSTEM') SET ('nameserver', 'affinity') = '0,16'

Example 2

To restrict the preprocessor and the compileserver to all remaining cores (that is, all except 0 and 16) on socket 0 (see line 21 in the example), use the following affinity settings:

Code Snippet
1
ALTER SYSTEM ALTER CONFIGURATION ('daemon.ini', 'SYSTEM') SET ('preprocessor', 'affinity') = '1-7,17-23'
Code Snippet
1
ALTER SYSTEM ALTER CONFIGURATION ('daemon.ini', 'SYSTEM') SET ('compileserver', 'affinity') = '1-7,17-23'

Example 3

To restrict the indexserver to all cores on socket 1 (see line 22 in the example), use the following affinity settings:

Code Snippet
1
ALTER SYSTEM ALTER CONFIGURATION ('daemon.ini', 'SYSTEM') SET ('indexserver', 'affinity') = '8-15,24-31'

Example 4

To set the affinity for two tenant databases, called DB1 and DB2 respectively, in a tenant database setup, use the following affinity settings:

Code Snippet
1
ALTER SYSTEM ALTER CONFIGURATION ('daemon.ini', 'SYSTEM') SET ('indexserver.DB1', 'affinity') = '1-7,17-23';
Code Snippet
1
ALTER SYSTEM ALTER CONFIGURATION ('daemon.ini', 'SYSTEM') SET ('indexserver.DB2', 'affinity') = '9-15,25-31';

CPU Affinity at Tenant Level

You can assign affinities to different tenants of a multi-tenant database on the same host, as shown here. Run these SQL statements on the SYSTEMDB.

Diagram of CPU Affinity at Tenant Level. There are two figures showing SAP HANA Servers with DB1 and DB2 indexservers pointing down to CPU #1 and CPU #2, as described in the preceding and following text.

Example 5

In this scenario tenant BD1 already exists. Here, we add another tenant DB2:

Code Snippet
1
CREATE DATABASE NM2 ADD AT LOCATION 'host:30040' SYSTEM USER PASSWORD Manager1;

Set the configuration parameter to bind CPUs to specific NUMA nodes on each tenant. You can use the following notation with a dot to identify the specific tenant:

Code Snippet
1
ALTER SYSTEM ALTER CONFIGURATION ('daemon.ini','SYSTEM') SET ('indexserver.NM1', 'affinity') ='0-7,16-23';
Code Snippet
1
ALTER SYSTEM ALTER CONFIGURATION ('daemon.ini','SYSTEM') SET ('indexserver.NM2', 'affinity') ='8-15,24-31';

To assign affinities to multiple indexservers of the same tenant on the same host execute the following SQL statements on the SYSTEMDB to apply the instance_affinity[port] configuration parameter:

Example 6

In this scenario an indexserver is already running on tenant NM1 on port 30003. Here, we add another indexserver on a different port:

Code Snippet
1
ALTER DATABASE NM1 ADD 'indexserver' AT LOCATION 'host:30040';

Set the different instances of the instance_affinity[port] configuration parameter to bind CPUs to specific NUMA nodes on each indexserver. The configuration parameter has a 1-2 digit suffix to identify the final significant digits of the port number: in this example, 30003 and 30040:

Code Snippet
1
ALTER SYSTEM ALTER CONFIGURATION ('daemon.ini','SYSTEM') SET ('indexserver.NM1', 'instance_affinity[3]')='0-7,16-23';
Code Snippet
1
ALTER SYSTEM ALTER CONFIGURATION ('daemon.ini','SYSTEM') SET ('indexserver.NM1', 'instance_affinity[40]')='8-15,24-31';

Restart the indexserver processes to make the affinity settings effective.

You can test the settings in SQL, or by using hdbcons. Run this query on the tenant or SystemDB as shown here:

Code Snippet
1
select * from M_NUMA_NODES;

Using hdbcons the process ID of the indexserver process is required as a parameter:

Code Snippet
1
hdbcons -p <PID> "jexec info"

SQL Statements to Apply NUMA Location Preferences

You can specify NUMA node location preferences for specific database objects in SQL using either the CREATE TABLE or ALTER TABLE statements.

NUMA Node Preferences

To apply NUMA node location preferences in SQL for tables, columns, or partitions, you can use the NUMA NODE clause followed by a list of one or more preferred node locations. Refer to the previous subsection for how to use the lscpu command to understand the system topology. For more information on this feature, see SAP HANA SQL and System Views Reference.

You can specify either individual nodes or a range of nodes, as shown in the following example:

Code Snippet
1
CREATE COLUMN TABLE T1(A int, B varchar(10)) NUMA NODE (‘1’, ‘3 TO 5’)

In this example, table T1 will be processed by NUMA node 1 if possible, and otherwise by any of NUMA nodes 3-5. Preferences are saved in the system table NUMA_NODE_PREFERENCE_.

Use the following statement to remove any preferences for an object:

Code Snippet
1
ALTER TABLE T1 NUMA NODE NULL

By default, preferences are only applied the next time the table is loaded. You can use the ALTER TABLE statement with the keyword IMMEDIATE to apply the preference immediately (the default value is DEFERRED):

Code Snippet
1
ALTER TABLE T1 NUMA NODE (‘3’) IMMEDIATE

Granularity

NUMA node location preferences can be applied at any of the following levels:

  • Table (column store only)
  • Table partition (range partitioning only)
  • Column

If multiple preferences for a column or partition have been defined, the column preference is applied first, then the partition preference, then the table.

The following example shows the statement being used to apply a preference for column A in table T1:

Code Snippet
1
CREATE COLUMN TABLE T1(A int NUMA NODE (‘2’), B varchar(10))

The following examples show statements to apply a preference for partition A in table T1:

Code Snippet
1
CREATE COLUMN TABLE T1(A int , B varchar(10)) PARTITION BY RANGE(A) (PARTITION VALUE = 2 NUMA NODE (‘4’))
Code Snippet
1
ALTER TABLE T1 ADD PARTITION (A) VALUE = 3 NUMA NODE (‘1’) IMMEDIATE

You can also identify a partition by its logical partition ID number and set a preference by using ALTER TABLE as shown here:

Code Snippet
1
ALTER TABLE T1 ALTER PARTITION 2 NUMA NODE ('3')

Transferring Preferences

Using the CREATE TABLE LIKE statement, the new table can be created with or without the NUMA preference. In the following example any preference which has been applied to T2 will (if possible) apply on new table T1. The system checks the topology of the target system to confirm if it has the required number of nodes. If not, the preference is ignored:

Code Snippet
1
CREATE TABLE T1 LIKE T2

The keyword WITHOUT can be used as shown in the following example to ignore any preference which has been applied to T2 when creating the new table T1:

Code Snippet
1
CREATE TABLE T1 LIKE T2 WITHOUT NUMA NODE

A similar approach is used with the IMPORT and EXPORT statements: any preferences are saved in the exported table definition and applied, if possible, in the target environment when the table is imported. In this case you can use the IGNORE keyword to import a table and ignore any node preferences:

Code Snippet
1
IMPORT SYSTEM."T14" FROM '/tmp/test/' WITH REPLACE THREADS 40 IGNORE NUMA NODE

SAP HANA Monitoring Views for CPU Topology Details

A number of system views are available that you can use to retrieve details of the CPU configuration.

You can get a general overview of the system topology by using the Linux lscpu command described earlier. Information about the system topology is also available in the following system views:

M_HOST_INFORMATION provides host information such as machine and operating system configuration. Data in this view is stored in key-value pair format and the values are updated once per minute. For most keys, you require the INIFILE ADMIN privilege to view the values. Select one or more key names for a specific host to retrieve the corresponding values:

Code Snippet
1
select * from SYS.M_HOST_INFORMATION where key in ('cpu_sockets','cpu_cores','cpu_threads');

M_NUMA_RESOURCES provides information on overall resource availability for the system:

Code Snippet
1
select HOST, NUMA_NODE_ID, NUMA_NODE_DISTANCES, MEMORY_SIZE from SYS.M_NUMA_NODES;

M_NUMA_NODES provides resource availability information on each NUMA node in the hardware topology, including inter-node distances and neighbor information.

Code Snippet
1
select MAX_NUMA_NODE_COUNT, MAX_LOGICAL_CORE_COUNT from SYS.M_NUMA_RESOURCES;

Controlling Parallel Execution of SQL Statements

Controlling Parallel Execution of SQL Statements

You can apply INI file settings to control the two thread pools, SqlExecutor and JobExecutor, that control the parallelism of statement execution.

Caution

The settings described here should only be modified when other tuning techniques like remodeling, repartitioning, and query tuning have been applied. Modifying the parallelism settings requires a thorough understanding of the actual workload because they have an impact on the overall system behavior. Modify the settings iteratively by testing each adjustment.

On systems with highly concurrent workloads, too much parallelism of single statements may lead to poor performance. Note also that partitioning tables influences the degree of parallelism for statement execution. In general, adding partitions tends to increase parallelism. You can use the parameters described in this section to adjust the CPU utilization in the system.

Diagram showing the Parallel Execution of SQL Statements. There are two oval figures: 1. SQL Executor Pool. 2. Job Executor Pool, with # Parallel executions and task types displayed to the right of both oval figures. This is described in more detail in the following text.

Two thread pools control the parallelism of the statement execution. Generally, target thread numbers applied to these pools are soft limits, meaning that additional available threads can be used if necessary and deleted when no longer required:

  • SqlExecutor

    This thread pool handles incoming client requests and executes simple statements. For each statement execution, an SqlExecutor thread from a thread pool processes the statement. For simple OLTP-like statements against column store, as well as for most statements against row store, this will be the only type of thread involved. With OLTP, we mean short running statements that consume relatively little resources. However, even OLTP-systems like SAP Business Suite may generate complex statements.

  • JobExecutor

    The JobExecutor is a job dispatching subsystem. Almost all remaining parallel tasks are dispatched to the JobExecutor and its associated JobWorker threads.

    In addition to OLAP workload, the JobExecutor also executes operations like table updates, backups, memory garbage collection, and savepoint writes.

You can set a limit for both SqlExecutor and JobExecutor to define the maximum number of threads. You can use this, for example, on a system where OLAP workload would normally consume too many CPU resources to apply a maximum value to the JobExecutor to reserve resources for OLTP workload.

Caution

Lowering the value of these parameters can have a negative effect on the parallel processing of the servers, and reduce the performance of the overall system. Adapt and test these values iteratively.

For more information, see Understand your Workload, and SAP Note 2222250: FAQ SAP HANA Workload Management.

A further option to manage statement execution is to apply a limit to an individual user profile for all statements in the current connection using the THREADLIMIT parameter.

Parameters for SQL Executor

Diagram showing the Settings at the SAP HANA System Level, as described in the preceding and following text.

The following SqlExecutor parameters are in the sql section of the indexserver.ini file:

sql_executors sets a soft limit on the target number of logical cores for the SqlExecutor pool.

  • This parameter sets the target number of threads that are immediately available to accept incoming requests. Additional threads will be created if needed, and deleted if no longer needed.
  • The parameter is initially not set (0) - the default value is the number of logical cores in a system. As each thread allocates a particular amount of main memory for the stack, reducing the value of this parameter can help to avoid memory footprint.

max_sql_executors sets a hard limit on the maximum number of logical cores that can be used.

  • In normal operation new threads are created to handle incoming requests. If a limit is applied here, SAP HANA will reject new incoming requests with an error message if the limit is exceeded.
  • The parameter is initially not set (0) so no limit is applied.

Caution

SAP HANA will not accept new incoming requests if the limit is exceeded. Use this parameter with extreme care.

Parameters for JobExecutor

The following JobExecutor parameters are in the execution section of global.ini or indexserver.ini:

max_concurrency sets the target number of logical cores for the JobExecutor pool.

  • This parameter sets the size of the thread pool used by the JobExecutor used to parallelize execution of database operations. Additional threads will be created if needed and deleted if no longer needed. You can use this to limit resources available for JobExecutor threads, thereby saving capacity for SqlExecutors.
  • The parameter is initially not set (0) - the default value is the number of logical cores in a system. Especially on systems with at least 8 sockets, consider setting this parameter to a reasonable value between the number of logical cores per CPU, up to the overall number of logical cores in the system. In a system that supports tenant databases, a reasonable value is the number of cores divided by the number of tenant databases.

max_concurrency_hint limits the number of logical cores for job workers, even if more active job workers are available.

  • This parameter defines the number of jobs to create for an individual parallelized operation. The JobExecutor proposes the number of jobs to create for parallel processing, based on the recent load on the system. Multiple parallelization steps may result in far more jobs being created for a statement (and hence higher concurrency) than this parameter.
  • The default is 0 (no limit is applied but the hint value is never greater than the value for max_concurrency). On large systems (systems with more than 4 sockets) setting this parameter to the number of logical cores of one socket may result in better performance, but testing is necessary to confirm this.

default_statement_concurrency_limit restricts the actual degree of parallel execution per connection within a statement.

  • This parameter controls the maximum overall parallelism for a single database request. Set this to a reasonable value (a number of logical cores) between 1 and max_concurrency, but greater or equal to the value set for max_concurrency_hint.
  • The default setting is 0 - no limit is applied.

Setting a Memory Limit for SQL Statements

Setting a Memory Limit for SQL Statements

You can protect an SAP HANA system from uncontrolled queries that consume excessive memory, by limiting the amount of memory used by single statement executions per host. By default, there is no limit set on statement memory usage. However, if a limit is applied, statement executions that require more memory will be aborted when they reach the limit. To avoid canceling statements unnecessarily, you can also apply a percentage threshold value which considers the current statement allocation as a proportion of the global memory currently available. Using this parameter, statements which have exceeded the hard-coded limit may still be executed if the memory allocated for the statement is within the percentage threshold. The percentage threshold setting is also effective for workload classes where a statement memory limit can also be defined.

You can also create exceptions to these limits for individual users (for example, to ensure an administrator is not prevented from doing a backup) by setting a different statement memory limit for each individual.

These limits only apply to single SQL statements, not the system as a whole. Tables which require much more memory than the limit applied here may be loaded into memory. The parameter global_allocation_limit limits the maximum memory allocation limit for the system as a whole.

You can view the (peak) memory consumption of a statement in M_EXPENSIVE_STATEMENTS.MEMORY_SIZE.

Diagram showing how to Set Memory Limits for SQL Statements, as described in the preceding and following text.

To be able to set memory limits for SQL statements, enable the following parameters:

  • In the global.ini file, in the resource_tracking section:
    • enable_tracking = on
    • memory_tracking = on
  • statement_memory_limit defines the maximum memory allocation per statement in GB. The default value is 0 (no limit).
    • In the global.ini file, expand the memorymanager section and locate the parameter. Set an integer value in GB between 0 (no limit) and the value of the global allocation limit. Values that are too small can block the system from performing critical tasks.
    • When the statement memory limit is reached, a dump file is created with 'compositelimit_oom' in the name. The statement is aborted, but otherwise the system is not affected. By default, only one dump file is written every 24 hours. If a second limit is hit in that interval, no dump file is written. The interval can be configured in the memorymanager section of the global.ini file, by using the oom_dump_time_delta parameter, which sets the minimum time difference (in seconds) between two dumps of the same kind (and the same process).
    • The value defined for this parameter can be overridden by the corresponding workload class property STATEMENT_MEMORY_LIMIT.

    After setting this parameter, statements that exceed the limit you have set on a host are stopped by running out of memory.

  • statement_memory_limit_threshold defines the maximum memory allocation per statement as a percentage of the global allocation limit. The default value is 0% (statement_memory_limit is always respected).
    • In the global.ini file, expand the memorymanager section and set the parameter as a percentage of the global allocation limit.
    • This parameter provides a means of controlling when statement_memory_limit is applied. If this parameter is set, when a statement is issued the system will determine if the amount of memory it consumes exceeds the defined percentage value of the overall global_allocation_limit parameter setting. The statement memory limit is only applied if the current SAP HANA memory consumption exceeds this statement memory limit threshold as a percentage of the global allocation limit.
    • This is a way of determining if a particular statement consumes a large amount of memory compared to the overall system memory available. In this case, to preserve memory for other tasks, the statement memory limit is applied and the statement fails with an exception.
    • Note that the value defined for this parameter also applies to the workload class property STATEMENT_MEMORY_LIMIT.
  • total_statement_memory_limit limits the memory available to all statements running on the system.
    • This limit does not apply to users with the administrator role SESSION ADMIN or WORKLOAD ADMIN who need unrestricted access to the system.
    • The value defined for this parameter cannot be overridden by the corresponding workload class property TOTAL_STATEMENT_MEMORY_LIMIT.
    • There is a corresponding parameter for use with system replication on an Active/Active (read enabled) secondary server. This is required to ensure that enough memory is always available for essential log shipping activity.

Managing Peak Load

SAP HANA Admission Control

Use the admission control feature to apply processing limits and to decide how to handle new requests if the system is close to the point of saturation.

You can apply thresholds by using configuration parameters to define an acceptable limit of activity in terms of the percentage of memory usage or percentage of CPU capacity.

Limits can be applied at two levels so that new requests will be queued first, until adequate processing capacity is available or a timeout is reached. A higher threshold can then be defined to determine the maximum workload level above which new requests will be rejected. If requests have been queued, items in the queue are processed when the load on the system reduces below the threshold levels. If the queue exceeds a specified size or if items are queued for longer than a specified period of time, they are rejected.

In the case of rejected requests an error message that the server is temporarily overloaded is returned to the client: 1038,'ERR_SES_SERVER_BUSY','rejected as server is temporarily overloaded'.

The load on the system is measured by background processes which gather a set of performance statistics covering available capacity for memory and CPU usage. The statistics are moderated by a configurable averaging factor (exponentially weighted moving average) to minimize volatility, and the moderated value is used in comparison with the threshold settings.

The admission control filtering process does not apply to all requests. In particular, requests that release resources will always be executed, for example, commit, rollback, and disconnect. The filtering also depends on user privileges: administration requests from SESSION_ADMIN and WORKLOAD_ADMIN are always executed.

There are some situations where it is not recommended to enable admission control, for example, during planned maintenance events such as an upgrade or the migration of an application. In these cases it is expected that the load level is likely to be saturated for a long time and admission control could therefore result in the failure of important query executions.

Managing Peak Load

Use the admission control feature to apply processing limits and to decide how to handle new requests if the system is close to the point of saturation.

You can apply thresholds using configuration parameters to define an acceptable limit of activity in terms of the percentage of memory usage or percentage of CPU capacity.

A chart showing Resource Utilization vertically, and Time horizontally. There are two horizontal lines within the chart detailing Reject and Queue information, as described in the preceding and following text.

Limits can be applied at two levels so that firstly, new requests are queued until adequate processing capacity is available or a timeout is reached, and secondly, a higher threshold can be defined to determine the maximum workload level above which new requests are rejected. If requests have been queued, items in the queue are processed when the load on the system reduces below the threshold levels. If the queue exceeds a specified size or if items are queued for longer than a specified period of time, they are rejected.

In the case of rejected requests, an error message that the server is temporarily overloaded, is returned to the client: 1038,'ERR_SES_SERVER_BUSY','rejected as server is temporarily overloaded'.

The load on the system is measured by background processes that gather a set of performance statistics covering available capacity for memory and CPU usage. The statistics are moderated by a configurable averaging factor (exponentially weighted moving average) to minimize volatility, and the moderated value is used in comparison with the threshold settings.

The admission control filtering process does not apply to all requests. In particular, requests that release resources are always executed, for example, commit, rollback, and disconnect. The filtering also depends on user privileges: administration requests from SESSION_ADMIN and WORKLOAD_ADMIN are always executed.

A diagram of the Dynamic Admission Control Architecture.

To monitor the admission control feature, you can use the SAP HANA cockpit or use the following public monitoring views that are available:

  • M_ADMISSION_CONTROL_STATISTICS

  • M_ADMISSION_CONTROL_QUEUES

  • Extended M_CONNECTIONS.CONNECTION_STATUS for queueing status

Workload Management Integration into the SAP HANA Cockpit

Screenshot of the Workload Admission Control Setting application, highlighting the buttons, Monitor and Settings, as described in the preceding and following text.

In the Workload Admission Control Setting application, you can configure the threshold values for admission control to determine when requests are queued or rejected, which are defined as configuration parameters.

The admission control feature is enabled by default and the related threshold values and configurable parameters are available in the indexserver.ini file. A pair of settings is available for both memory and CPU that define firstly the queuing level (the default value is 90%) and secondly, the rejection level (the default is not active). Two parameters are available to manage the statistics collection process by defining how frequently statistics are collected and setting the averaging factor that is used to moderate volatility. These parameters, available in the Workload Admission Control Setting application and in the admission_control section of the INI file, are summarized in the following table.

ParameterDefault ValueDescription
enableTrueEnables or disables the admission control feature.
queue_cpu_threshold90The percentage of CPU usage above which requests are queued. Queue details are available in the view M_ADMISSION_CONTROL_QUEUES.
queue_memory_threshold90The percentage of memory usage above which requests are queued.
reject_cpu_threshold0The percentage of CPU usage above which requests are rejected. The default value 0 means that no requests are rejected, but may be queued.
reject_memory_threshold0The percentage of memory usage above which requests are rejected. The default value 0 means that no requests are rejected, but may be queued.
averaging_factor70This percentage value gives a weighting to the statistic averaging process. A low value has a strong moderating effect (but may not adequately reflect real CPU usage). A value of 100% means that no averaging is performed, that is, only the current value for memory and CPU consumption is considered.
statistics_collection_interval1000The unit is milliseconds. The statistics collection interval is set by default to 1000 ms (1 second), which has a negligible effect on performance. Values from 100 ms are supported. Statistics details are visible in the view M_ADMISSION_CONTROL_STATISTICS.

Events and Rejection Reasons

If statements are being rejected, you may need to investigate why this is happening. Events related to admission control are logged and can be reviewed in the M_ADMISSION_CONTROL_EVENTS view. The key information items here are the event type (such as a statement was rejected, a statement was queued, or a configuration parameter was changed) and the event reason, which gives explanatory text related to the type. Other details in this view include the length of time the statement was queued and the measured values for memory and CPU usage.

Two parameters are available to manage the event log in the admission_control_events section of the INI file:

ParameterDefault ValueDescription
queue_wait_time_threshold100000The length of time (measured in microseconds) for which a request must be queued before it is included in the event log (the default is one tenth of a second). If the parameter is set to 0, then events are not logged.
record_limit1000000The maximum record count permitted in the monitor of historical events.

Queue Management

If requests have been queued, items in the queue are processed when capacity becomes available. A background job continues to evaluate the load on the system in comparison to the thresholds. When the load is reduced enough, queued requests are submitted in batches on an oldest-first basis.

The queue status of a request is visible in the M_CONNECTIONS view. The connection status value is set to queuing in the column M_CONNECTIONS.CONNECTION_STATUS.

There are several configuration parameters (in the admission_control section of the INI file) to manage the queue and how the requests in the queue are released. You can apply a maximum queue size or a queue timeout value. If either of these limits are exceeded, requests which would otherwise be queued, are rejected. An interval parameter is available to determine how frequently to check the server load so that de-queueing can start, and a de-queue batch size setting is also available.

ParameterDefault ValueDescription
max_queue_size10000The maximum number of requests that can be queued. Requests above this number are rejected.
dequeue_interval1000 (ms)Use this parameter to set the frequency of the check to re-evaluate the load in comparison to the thresholds. The default is 1000 ms (1 second). This value is recommended to avoid overloading the system, though values above 100 ms are supported.
dequeue_size50Use this parameter to set the de-queue batch size, that is, the number of queued items that are released together once the load is sufficiently reduced. This value can be between 1 and 100 queued requests.
queue_timeout600 (s)Use this parameter to set the maximum length of time for which items can be queued. The default is 10 minutes. The minimum value that can be applied is 60 seconds and there is no maximum limit. Requests queued for this length of time are rejected. Note that the time-out value applies to all entries in the queue. Any changes made to this configuration value are applied to all entries in the existing queue.
queue_timeout_check_interval10000 (ms)Use this parameter to determine how frequently to check if items have exceeded the queue time-out limit. The default is 10 seconds. The minimum value that can be applied is 100 ms and there is no maximum limit.

Note

If Admission Control has been configured and is active, it takes precedence over any other time-out value which may have been applied. This means that other timeouts that apply to a query (such as a query timeout) will not be effective until the query has been de-queued or rejected by the queue timeout.

SAP HANA Workload Classes

You can manage workloads in SAP HANA by creating workload classes and workload class mappings. Appropriate workload parameters are then dynamically applied to each client session.

Workload class settings override other configuration settings (INI file values) which have been applied. Workload class settings also override user parameter settings which have been applied by the SQL command ALTER USER. However, workload class settings only apply for the duration of the current session, whereas changes applied to the user persist. More detailed examples of precedence are given in a separate section.

To apply workload class settings, client applications can submit client attribute values (session variables) in the interface connect string as one or more property-value pairs. The key values which can be used to work with workload classes are: database user, client, application name, application user, and application type.

A diagram showing an example of the workload class settings at the SAP HANA session level, as described in the preceding and following text.

Based on this information the client is classified and mapped to a workload class. 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.

The list of supported applications includes HANA WebIDE (XS Classic), HANA Studio, ABAP applications, Lumira, and Crystal Reports. Full details of the session variables available in each supported client interface which can be passed in the connect string are given in SAP Note 2331857 SAP HANA workload class support for SAP client applications.

Caution

Workload classes cannot be used on an Active/Active (read-only) secondary node.

Required Privilege

Managing workload classes requires the WORKLOAD ADMIN privilege. Changes of workload classes or mappings will only be applied when a (connected) database client reconnects. In terms of the privileges of the executing user (DEFINER or INVOKER), the workload mapping is always determined on the basis of invoking user, regardless of whether the user has definer or invoker privileges.

The ABAP server sets the client context information automatically for all ABAP applications.

Users, classes, and mappings are interrelated: if you drop a user in the SAP HANA database, all related workload classes are dropped and if you drop a workload class, the related mappings are also dropped

Note

In a scale-out environment workload classes are created for the complete SAP HANA database and do not have to be created for each single node. However, restrictions defined in these workload classes are applied to each single node and not to the complete SAP HANA database

Create Workload Classes Using SAP HANA Cockpit

Several configuration options are available so that you can tailor workload classes in the SAP HANA database to your needs.

You can manage workload in SAP HANA by creating workload classes and workload class mappings. Workload classes and mappings are SQL object for workload management in SAP HANA. The goal of workload classes and mappings is to provide an easy way for administrators to regulate applications based on predefined mapping rules, to avoid resource shortages with regard to CPU and memory consumption. Appropriate workload parameters are dynamically applied to each client session.

Screenshot showing how to create a workload class in SAP HANA. The following text appears in bullet points: 1. Define workload limits and priorities. 2. Define mapping details on: Database/application user, Application, and Client.

You can classify workloads based on user and application context information and apply configured resource limitations (for example, a statement memory limit). Workload classes allow SAP HANA to influence dynamic resource consumption on the session or statement level. When a request from an application arrives in SAP HANA, the corresponding workload class is determined based on the information given by the session context such as application name, application user name and database user name. Once the corresponding workload class is determined, the application request can have its resources limited according to the workload class definition.

Statement memory limits will not apply if memory tracking is inactive in SAP HANA cockpit. You can activate memory tracking in the Configuration settings.

You can use workload classes to set values for the properties listed here. Each property also has a default value, which is applied if no class can be mapped or if no other value is defined. For all of the following parameters, although you can enter values including decimal fractions (such as 1.5 GB) these numbers are rounded down and the whole number value is the effective value which is applied

ParameterValue
Workload Class NameA name for the new workload class.
Execution PriorityTo support better job scheduling, this property prioritizes statements in the current execution. Priority values of 0 (lowest priority) to 9 (highest) are available. The default value is 5.
Limit TypeIndividual Statement Limit or Total Aggregate Statement Limit.
Statement Memory LimitDisplayed if Individual Statement Limit is the specified limit type. Maximum amount of memory the statement may use, as either an absolute or relative value.
Total Memory LimitDisplayed if Total Aggregate Statement Limit is the specified limit type. Maximum amount of memory all statements may use, as either an absolute or relative value.
Statement Thread LimitDisplayed if Individual Statement Limit is the specified limit type. Maximum number of parallel threads the statement may execute, as either an absolute or relative value.
Total Thread LimitDisplayed if Total Aggregate Statement Limit is the specified limit type. Maximum number of parallel threads all statements may execute, as either an absolute or relative value.
Query TimeoutThe amount of time in seconds before the query times out. (Available for databases running SAP HANA SPS 03 or higher).

Note

For thread and memory limits, workload classes can contain either the statement-level properties or the aggregated total properties, but not both. For the aggregated limits, the full set of three properties must be defined: TOTAL STATEMENT THREAD LIMIT, TOTAL STATEMENT MEMORY LIMIT, and PRIORITY.

Example

You can set values for one or more resource properties in a single SQL statement. The following example creates a workload class called MyWorkloadClass with values for all three properties:

Code Snippet
1
CREATE WORKLOAD CLASS "MyWorkloadClass" SET 'PRIORITY' = '3', 'STATEMENT MEMORY LIMIT' = '2' , 'STATEMENT THREAD LIMIT' = '20'

Examples of Precedence for Query Timeout

If multiple values have been defined using the different timeout methods available then precedence rules apply. Firstly, if a valid matching workload class value has been defined, this takes precedence over the INI file setting. Secondly, if a QueryTimeout value has been applied, then the smallest (strictest) valid value which has been defined applies. The following table shows some examples: in each case the values marked by an asterisk are the ones which apply.

QueryTimeout25252525*
statement_timeout (ini)1010*10*10 (ignored)
STATEMENT TIMEOUT (Workload class)20*no matchno value0 (disabled)

Creating a Workload Mapping

Mapping link workload classes to client sessions depends on the value of a specific client information property. The class with the most specific match is mapped to the database client.

The SAP HANA application sends client context information in the ClientInfo object. This is a list of property-value pairs that an application can set in the client interface. You can change the running session-context of a connected database client using the following SQL command:

Code Snippet
1
ALTER SYSTEM ALTER SESSION SET
For more information, see the Setting Session-Specific Client Information in the SAP HANA Developer Guide.

The properties supported are listed in the following table in order of importance. The workload class with the greatest number of properties matching the session variables passed from the client is applied. If two workload classes have the same number of matching properties, they are matched in the following order of importance.

Field NameDescription
OBJECT NAMEObject types PROCEDURE, PACKAGE and AREA are supported. This property only applies to procedures including AFLLANG procedure which is a standard execution method to execute the application function. Example: If a workload class is matched to an object with type AREA, then it will apply the workload class definition to all AFLLANG procedures which call application functions in the given AFL AREA. Object type PACKAGE works in a similar way. 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.
SCHEMA NAMESchema name of object defined in the OBJECT NAME property.
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.
APPLICATION USER NAME*The name of the application user, usually the user logged into the application.
CLIENT*The client number is usually applied by SAP ABAP applications, such as SAP Business Suite / Business Warehouse.
APPLICATION COMPONENT NAME*The name of the application component. This value is used to identify sub-components of an application, such as CRM inside the SAP Business Suite.
APPLICATION COMPONENT TYPE*This value is used to provide coarse-grained properties of the workload generated by application components. In the future, SAP may document well-defined application component types to identify, for example, batch processing or interactive processing.
APPLICATION NAME*The name of the application.
USER NAMEThe 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.

Properties marked with an * support the use of wildcard characters.

Example

This example creates a workload mapping called MyWorkloadMapping that applies the values of the MyWorkloadClass class to all sessions where the application name value is HDBStudio:

Code Snippet
1
CREATE WORKLOAD MAPPING "MyWorkloadMapping" WORKLOAD CLASS "MyWorkloadClass" SET 'APPLICATION NAME' = 'HDBStudio';

Hints for Workload Classes

To give control over workload classes at run-time, a WORKLOAD_CLASS hint is available. You can use this to apply more restrictive properties than the ones otherwise defined. For example, workload class YOUR_WORKLOAD_CLASS applies the values: PRIORITY 5, THREAD 5, MEMORY 50GB. This is then overridden by the values defined in a new class, as a hint, to apply a higher priority value, a lower thread limit, and a lower memory threshold:

Code Snippet
1
SELECT * FROM T1 WITH HINT( WORKLOAD_CLASS("MY_WORKLOAD_CLASS") );

This example 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. You can change this default behavior 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.

When are Restrictions Applied?

Generally, limitations applied by configuration settings, user profile parameters, and workload class settings are applied as queries are compiled. For workload classes, you can see this by querying the M_EXEPENSIVE_STATEMENTS monitoring view to see the operation value (COMPILE, SELECT, FETCH, and so on) when the workload class was applied to a statement string. This is not true, however, if the workload class mapping was overridden by a hint, and also does not apply to the OBJECT NAME and SCHEMA NAME workload class properties. In these cases, the statement can only be evaluated after compiling.

Monitoring Workload Classes

The following system views allow you to monitor workload classes and workload mappings:

  • WORKLOAD_CLASSES
  • WORKLOAD_MAPPINGS

In these system views the field WORKLOAD_CLASS_NAME shows the effective workload class used for the last execution of that statement:

  • M_ACTIVE_STATEMENTS
  • M_PREPARED_STATEMENTS
  • M_EXPENSIVE_STATEMENTS (enable_tracking and memory_tracking must first be enabled in the global.ini file for this view)
  • M_CONNECTIONS

If no workload class is applied, these views display the pseudo-workload class value _SYS_DEFAULT.

Log in to track your progress & complete quizzes

Learning

SAP FacebookSAP YoutubeSAP LinkedIn