---
title: Workload Management Walkthrough
slug: R76QxEWYg3QLQPZdkQPId
description: Learn how to manage workloads within Ocient, balancing resources for optimized query performance and efficiency.
docTags: 
createdAt: Thu Jan 05 2023 22:13:24 GMT+0000 (Coordinated Universal Time)
updatedAt: Mon Mar 30 2026 18:52:29 GMT+0000 (Coordinated Universal Time)
---

Workload Management (WLM) in the Ocient Data Intelligence Platform™ allows a Database Administrator to set restrictions and priorities for groups of users. The main use case for this is to effectively control multiple workflows across the system, prioritizing or limiting resources to groups when needed. Query restrictions are set and assigned using a service class that can be used for multiple user groups. For a full list of restrictions and their definitions, see the documentation.

This example shows how to set up three service classes with differing priorities, explaining the tradeoffs and design decisions one should make when setting up these restrictions.

## Create Service Classes

Before creating service classes, the System Administrator or Database Administrator first determines the logical grouping of users on their system and their relative priority. In addition, the response time and concurrency requirements of different user groups are understood. Using these inputs, the administrator can design the service classes that allow their users to receive the proper resource allocations that deliver query requirements.

In this example, define the priority at the service level by creating three service classes to service High, Medium, and Low priority users. Different settings are configured to provide an illustration of the types of configuration that you can use to control features such as concurrency, result sizing, or scheduling priority.

### Prerequisites

1. An Ocient® System has been set up with an operational SQL Node.
2. A user with the system administrator role has access to the system

### Step 1: Login as a System Administrator User

In order to create service classes, the user signed in must be assigned the **System Administrator** role. For a full list of user permissions and their definitions, see the [Data Control Language (DCL) Statement Reference](<./././SQL Reference/Data Control Language _DCL_ Statement Reference.md>). &#x20;

First, connect to the system database with the command.

```shell
connect to jdbc:ocient://my_SQL_ip_address:4050/system;user=admin@system;password=securePassword;
```

### Step 2: Set up the High Priority User Class

User groups that need fast performance and need access to the most system resources should fall under this service class. Their queries should see minimal impact from outside workloads.

:::hint{type="info"}
When defining a service class, any service class field that is omitted will be set to the maximum/least restrictive value.
:::

**Example Service Class**

```sql
CREATE SERVICE CLASS high_priority_sc
  scheduling_priority = 100.0,
  max_concurrent_queries = 10,
  max_elapsed_time = 3600;
```

**Example System Catalog View**

```sql
SELECT * FROM sys.service_classes;

id                                   database_id                          name                                         max_temp_disk_usage max_elapsed_time    max_concurrent_queries max_rows_returned   scheduling_priority   cache_max_bytes     cache_max_time      max_elapsed_time_for_caching max_columns_in_result_set priority_adjustment_factor priority_adjustment_time min_priority          max_priority
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
42649e8e-ae7f-4de5-bb2b-846d158fcb91 23291ad0-ce94-4e1a-bdc9-178463ef6ec4 Default                                      100                 -1                  -1                     -1                  1.0                   -1                  -1                  -1                           -1                        0.0                        0                        0.0                   -1.0
06b92c4c-b0b1-4a14-880c-7d257af3b2b5 23291ad0-ce94-4e1a-bdc9-178463ef6ec4 high_priority_sc                             100                 3600                10                     -1                  100.0                 -1                  -1                  -1                           -1                        0.0                        0                        0.0                   -1.0
```

`scheduling_priority` — A numeric value that defines the query’s priority in the database. A default query being run with no service class has a priority set to 1.0. This priority number is used as an ordered list for scheduling with higher values receiving priority. It is not a weighted value, so only the ordering, not the relative sizes of the priority levels matter. When setting the priority for a high-priority service class, set this value to be greater than 1.0.

`max_concurrent_queries` — The number of concurrent queries that can be run at a given time for the entire service class. If the max concurrency is hit, queries will enter a queue. A value of -1 indicates unlimited queries, however it is a best practice to set this value so no individual workloads can monopolize the system.

`max_elapsed_time` — The maximum amount of time, in seconds, a query can run. This restriction helps reduce the length of queries holding concurrency slots and allows concurrent workloads using the same service class to all make progress. It is recommended to set `max_elapsed_time` if `max_concurrent_queries` is set to prevent extremely long-running queries from blocking other concurrent queries. If a service class needs to run sets of queries with very different expected elapsed times, it might be an indication that a separate service class is warranted.

### Step 3: Set up the Medium Priority User Class

User groups that do not necessarily need the fastest performance fall under the medium priority service class. Higher-priority queries impact the performance of queries in this service class, but these queries receive priority over lower-priority ad hoc workloads.

**Example Service Class**

```sql
CREATE SERVICE CLASS medium_priority_sc scheduling_priority = 10.0, max_concurrent_queries = 2, max_temp_disk_usage = 50, max_elapsed_time = 600;
```

**Example System Catalog View**

```sql
SELECT * FROM sys.service_classes;

id                                   database_id                          name                                         max_temp_disk_usage max_elapsed_time    max_concurrent_queries max_rows_returned   scheduling_priority   cache_max_bytes     cache_max_time      max_elapsed_time_for_caching max_columns_in_result_set priority_adjustment_factor priority_adjustment_time min_priority          max_priority
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
42649e8e-ae7f-4de5-bb2b-846d158fcb91 23291ad0-ce94-4e1a-bdc9-178463ef6ec4 Default                                      100                 -1                  -1                     -1                  1.0                   -1                  -1                  -1                           -1                        0.0                        0                        0.0                   -1.0
06b92c4c-b0b1-4a14-880c-7d257af3b2b5 23291ad0-ce94-4e1a-bdc9-178463ef6ec4 high_priority_sc                             100                 3600                10                     -1                  100.0                 -1                  -1                  -1                           -1                        0.0                        0                        0.0                   -1.0
bf3a939d-d773-4c7c-9c30-4bc14e7b9422 23291ad0-ce94-4e1a-bdc9-178463ef6ec4 medium_priority_sc                           50                  600                 2                      -1                  10.0                  -1                  -1                  -1                           -1                        0.0                        0                        0.0                   -1.0
```

`scheduling_priority` — For a "medium" priority, this value should be set to a number in-between the highest priority service class and the default service class.

`max_temp_disk_usage` — From 0-100, this field represents the maximum percentage of unused disk space that a query can see before failing. When setting up the cluster, it is important to keep some disk space unused on a Foundation Node so queries can spill to disk. Note that this percentage is tracked on a system-wide basis per node, not within a service class. As one service class uses its allocation of temporary disk space to fulfill a query, it also reduces the total temporary disk available to all other service classes. Even if those service classes have a max\_temp\_disk\_usage set, if the total available temp disk space is below the amount needed to run a query, it can fail. For example, if a higher-priority service class is using over 50% of the unused disk space during a medium-priority query that needs to spill to disk, the medium-priority query fails during its first attempt to spill without using any disk space. Spill to disk scheduling uses the same priority used for normal execution.

### Step 4: Set up the Low Priority Service Class

User groups that are less performance sensitive fall under the low-priority service class. The design goal is for these queries to have minimal effect on other workloads. Examples of users in this service class are analysts who want to run ad hoc queries or processes that run time-insensitive background reports.

**Example Service Class**

```sql
CREATE SERVICE CLASS low_priority_sc
  scheduling_priority = 1.0,
  max_concurrent_queries = 1,
  max_elapsed_time = 60,
  max_rows_returned = 100,
  max_temp_disk_usage = 0,
  cache_max_bytes = 0,
  cache_max_time = 0,
  max_elapsed_time_for_caching = 0;
```

**Example System Catalog View**

```sql
SELECT * FROM sys.service_classes;

id                                   database_id                          name                                         max_temp_disk_usage max_elapsed_time    max_concurrent_queries max_rows_returned   scheduling_priority   cache_max_bytes     cache_max_time      max_elapsed_time_for_caching max_columns_in_result_set priority_adjustment_factor priority_adjustment_time min_priority          max_priority
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
42649e8e-ae7f-4de5-bb2b-846d158fcb91 23291ad0-ce94-4e1a-bdc9-178463ef6ec4 Default                                      100                 -1                  -1                     -1                  1.0                   -1                  -1                  -1                           -1                        0.0                        0                        0.0                   -1.0
06b92c4c-b0b1-4a14-880c-7d257af3b2b5 23291ad0-ce94-4e1a-bdc9-178463ef6ec4 high_priority_sc                             100                 3600                10                     -1                  100.0                 -1                  -1                  -1                           -1                        0.0                        0                        0.0                   -1.0
bf3a939d-d773-4c7c-9c30-4bc14e7b9422 23291ad0-ce94-4e1a-bdc9-178463ef6ec4 medium_priority_sc                           50                  600                 2                      -1                  10.0                  -1                  -1                  -1                           -1                        0.0                        0                        0.0                   -1.0
dd1f9f03-edff-4c7e-acd7-32997b6fceaa 23291ad0-ce94-4e1a-bdc9-178463ef6ec4 low_priority_sc                              0                   60                  1                      100                 1.0                   0                   0                   0                            -1                        0.0                        0                        0.0                   -1.0
```

`scheduling_priority` — For a low priority, this value should be set to a low number like 1.0. If a service class wants to be lower priority than default queries, it can schedule a value lower than 1.0.

`max_temp_disk_usage` — Setting this limitation to 0 means that the system does not allow any queries in the low-priority service class to spill to disk. In this example, the goal of the designer is to prevent the low-priority service class from impacting more important workloads that might need the entire temp disk space. In other cases, it could be suitable to allow low priority service class queries to use temp disk.

`cache_max_bytes`, `cache_max_time`, `max_elapsed_time_for_caching` — Result set caching is a feature that attempts to cache result sets for commonly used queries. This result set cache is shared across service classes. For a lower-priority user, it makes sense to limit the time allowed in the cache or cap the maximum number of bytes used in the cache so a higher-priority service class can take advantage of it. In this example, the result set cache is explicitly disabled for low priority users by setting these values to zero. For details, see the [Result Set Caching](<./Result Set Caching.md>).&#x20;

### Step 5: Assign Service Classes to User Groups

After setting up the service classes, you need to assign them to user groups. When a service class is assigned to user groups, users in each user group can run queries under the restrictions set for the given service class. This process can be repeated for all service classes and users.

**Examples**

```sql
/* Create a user group. */
CREATE GROUP low_priority_group;

/* Assign the service class to the user group. */
ALTER GROUP low_priority_group SET service class = low_priority_sc;

/* Add a user to a user group. */
ALTER GROUP low_priority_group ADD USER analyst1;
```

## Dynamic Workload Management Priority Adjustment

When a query executes, you can set the priority of a query at the service class level, database session level, or the query level. The system chooses the maximum value from these three priority levels.&#x20;

While the engine executes a query, the engine can dynamically adjust the priority using a specified adjustment interval. You can change the percentage amount to dynamically adjust query priority using the `priority_adjustment_factor` (or `ADJUSTFACTOR`) and the time period that indicates the frequency for the adjustment of the query priority during query execution using the `priority_adjustment_time` (or `ADJUSTTIME`). You can override this limit at the query level or the session level.

Define the priority at the service level by creating service classes. You can override the priority at the service level by using the priority at the session or query level. For details, see [Users, Groups, and Service Classes](<./././SQL Reference/Data Definition Language _DDL_ Statement Reference/Users_ Groups_ and Service Classes.md>).&#x20;

Define the priority at the session level using the command line. This code instructs the engine to dynamically adjust the priority every 15 seconds and decrease the query priority by 25%.

```sql
SET PRIORITY "5.0";
SET ADJUSTTIME 15;
SET ADJUSTFACTOR "0.75";
```

To reset the priority adjustment factor, enter this SQL statement.

```sql
SET ADJUSTFACTOR RESET;
```

Or, define the priority at the query level in the SQL statement, such as this statement.

```sql
SELECT * FROM table1 USING
  SCHEDULING_PRIORITY = 10.0,
  PRIORITY_ADJUSTMENT_FACTOR = 1.75,
  PRIORITY_ADJUSTMENT_TIME = 120;
```

Alternatively, you can also specify a service class to use at the query level. This example creates the service class `lower_priority` and assigns it to the query.&#x20;

```sql
CREATE SERVICE CLASS lower_priority
  SCHEDULING_PRIORITY = 10.0,
  PRIORITY_ADJUSTMENT_FACTOR = 0.75,
  PRIORITY_ADJUSTMENT_TIME = 120;
  
SELECT * FROM table1 USING SERVICE CLASS lower_priority;
```

:::hint{type="warning"}
If you do not identify a specific service class for a query with the `USING SERVICE CLASS` clause and the query does not match any service classes that use the `STATEMENT_TEXT` parameter, the query uses a composite of least restrictive parameters allowed by the service classes eligible for the query.
:::

:::hint{type="info"}
Using a service class at the query level requires a user to be assigned to a user group that is included in that specific service class.&#x20;
:::

## Low-Latency Settings

:::hint{type="warning"}
Low-latency service class settings are intended to reduce runtime overhead for classes of subsecond queries with minimal processing work. Classes of queries that are good candidates utilize indexes on their filters, have low cardinality, and perform minimal work, such as simple aggregations or scalar functions. Low-latency service class settings might hinder query performance when queries have high cardinality or perform computationally intense work. Workload management is key to isolating these simple, low-latency queries to specific service classes where you can apply these optimizations in isolation.
:::

Queries for latency-sensitive applications, such as populating live dashboards and key-value retrieval might benefit from service-class level settings that help the Ocient System to make runtime choices to reduce latency. These choices often help queries with low cardinality and minimal computational work, but they can be detrimental to queries that violate these assumptions. Database administrators should use service classes to isolate low-latency components of a system from computationally intensive components.&#x20;

A system administrator can turn on one simple or two advanced settings.

| **Setting**            | **Description**                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | **Value**                                                        |
| ---------------------- |   | ---------------------------------------------------------------- |
| `LOW_LATENCY`          | This optional service class setting enables you to control the `HALF_PARALLELISM` and `LOAD_BALANCE_SHUFFLE` settings. When you set this setting to `true`, it is equivalent to setting `HALF_PARALLELISM = true` and `LOAD_BALANCE_SHUFFLE = false`. When you do not set this setting or set it to `false`, the Ocient System clears the `HALF_PARALLELISM` and `LOAD_BALANCE_SHUFFLE` settings.                                                                                                                                                                                                                                                                                                                                                                                                                     | Optionally set to `true` or `false`. The default value is unset. |
| `HALF_PARALLELISM`     | This optional service class setting controls execution parallelism overrides. When you do not set this setting, the system uses cost-based heuristics to determine runtime parallelism. When you set this setting to `true`, the system always uses half parallelism for queries that match this service class. When you set this setting to `false`, the system always uses full parallelism for queries that match this service class. <br />Half parallelism can help latency by reducing the amount of administrative work the system needs to do to run a query, but it reduces the computational power allocated to the query.                                                                                                                                                                                  | Optionally set to `true` or `false`. The default value is unset. |
| `LOAD_BALANCE_SHUFFLE` | This optional service class setting controls load balancing shuffles above I/O operators. When you do not set this setting, the Ocient System uses statistics-based heuristics to determine whether to use load balance shuffles. When you set this setting to `true`, the system always performs a load balance shuffle for queries that match this service class. When you set this setting to `false`, the system never performs a load balance shuffle for queries that match this service class.<br />Load balance shuffles are useful for distributing uneven data distributions on disk, which can occur during live-loading of tables or small cardinality tables with compute-heavy computations. However, this load balancing incurs some system-coordination overhead, which hampers low-latency queries.  | Optionally set to `true` or `false`. The default value is unset. |

There is further convenience for organizing these parameters in a service class by enclosing `HALF_PARALLELISM` or `LOAD_BALANCE_SHUFFLE` settings in a `LOW_LATENCY` key-value setting map.

```sql
CREATE SERVICE CLASS ll_service_class
  LOW_LATENCY = {HALF_PARALLELISM = true, 
                 LOAD_BALANCE_SHUFFLE = true};
  
SELECT * FROM table1 USING SERVICE CLASS ll_service_class;
```

## Other Service Class Considerations

Service classes are a powerful tool to properly allocate resources in the database to the users that need them most. When deciding on the restrictions for your individual use case, consider the total number of service classes that the system creates. Some settings like `max_concurrent_queries` and result set caching values like `cache_max_bytes` and `cache_max_time` can have a cumulative effect on resource availability as the number of service classes increases. For details about result set caching, see [Result Set Caching](<./Result Set Caching.md>).&#x20;

For example, concurrency limitations are set on an individual service class level. While the intention might be to limit low priority service classes to only a couple of concurrent queries, the total effect of many service classes with a couple of concurrent queries each can start to affect high priority workloads due to the number of concurrent queries running across all lower priority service classes.

## Related Links

[Workload Management and Service Classes](<./Workload Management and Service Classes.md>)&#x20;

[Data Definition Language (DDL) Statement Reference](<./././SQL Reference/Data Definition Language _DDL_ Statement Reference.md>)

[SQL Reference](<./././SQL Reference.md>)

