Understanding Teradata Workload: Definition, Advantages, and Characteristics

Those familiar with Teradata tuning are likely acquainted with the concept of “workload.” This article aims to define the term and elucidate the benefits of associating each Request (i.e. query or load operation) with a specific workload.

What is Teradata Workload?

Workloads are sets of requests that share similar traits. Managing workloads is more efficient than handling individual requests because standard settings and rules can be applied to the group as a whole.

Categorizing requests into workloads provides a comprehensive understanding of system users and resource consumption. This information facilitates the prioritization of performance-tuning tasks by workload. For instance, activities about your crucial and strategic workload should receive the highest priority for tuning.

Workloads involve allocating resources, such as CPU and IOs, and preferences, such as CPU usage frequency and duration, at a group level. Business requirements typically determine the significance of a group’s requests. Workloads can be used to assign shared priorities and resource allocations to these requests. The Teradata priority scheduler is responsible for managing the resources and priorities that are assigned to each workload.

Teradata systems employ varying implementations of priority scheduling across different releases. For instance, the Teradata 14 priority scheduler aligns with the SLES 11 Linux scheduler and its priority hierarchies. However, previous Teradata releases deployed on SLES 10 adopted a distinct priority scheduling approach. Nonetheless, comprehending the idea of workloads and workload classification does not hinge on these specifics. In a forthcoming article that touches on workload management, we will delve deeper into the Teradata priority scheduler.

When a request satisfies the defined characteristics of a workload, it will be allocated to that workload. Teradata will then apply the specific rules and settings of the workload to the request. This process, known as workload classification, is the fundamental concept behind resource allocation and request limitation.

Workloads are managed through the ViewPoint “Workload Designer.”

Workload Characteristics are used to assign each Request to a Workload

Workload characteristics can have various types (which can be used alone or in combination with other features for workload classification). However, note that the lists below may not be exhaustive as each Teradata release introduces new capabilities.

1. Request source:

The source of a request indicates its point of origin and may include any of the following:

• Username
• Account name
• Account string
• Application
• Client IP address
• Client ID

A query is classified into a workload based on whether its request source matches the defined sources for that workload. Multiple request sources can be assigned to a workload.

Username = ‘ETLUSER1’
Client IP address = 192.168.0.54

Multiple request sources are combined using the logical operator ‘AND’ when specified for a workload. For instance, in the given example, the login user must be ‘ETLUSER1’, and the connection must originate from the client with the IP address 192.168.0.54 to be included in the considered workload. It is assumed that both request sources are required for inclusion.

Before Teradata 13.00, multiple characteristics were grouped using the OR operator. However, starting from Teradata 13.10, these characteristics are now linked using the AND operator. It is important to note that this rule applies to all features and not solely to request sources.

These request sources are all available in the DBC.DBQLOGTBL table.

Teradata will execute each request against one or multiple database objects, including databases, tables, views, macros, stored procedures, and UDFs.

Assign queries to a workload by specifying request targets in your workload characteristics, which correspond to specific database objects being accessed.

Teradata 14 defines supplementary subcriteria for each request target based on how the objects are utilized. However, UDFs do not permit subcriteria.

Subcriteria include:

– Add a query or exclude a query from the workload if the database object is accessed with a full table scan (FTS).
– Add a query to the workload if the database object is accessed with a particular join type (all or no joins, all or no product joins, all or no unconstrained product
joins)
– Add a query to the workload if a minimum or a maximum number of rows for each step is estimated.
Several request targets can be combined in a workload definition (AND combined). If subcriteria are used, they are all AND combined.

3. Query Characteristics:

A more comprehensive workload classification can be achieved through the analysis of individual requests. Query attributes are utilized to categorize a query into a workload. These attributes include:

– Join Behaviour: product join, unconstraint product join, no product join, no unconstraint product join, any join, no join.
– Type of Statement: DDL, DML, SQL Select, Collect Statistics.
– AMP Limits: Defines if All-AMP operations should be included or excluded/from this workload.
– Estimated Processing Time: Adds a query to the workload if the estimated processing time is between the minimum and maximum.
– Step Row Count: Adds a query to the workload if the result rows of each step are between the minimum and maximum values specified.
– Final Row Count: Adds a query to the workload if the result rows of the final result set are between the minimum and maximum values defined.
– Full Table Scan: Adds or excludes queries doing a full table scan (FTS).
– Estimated memory usage: Include or exclude Request if it exceeds a defined memory usage

4. Utility Usage as Classification Criteria

Workload management can categorize load tasks and backup/restore tasks into distinct workloads. Load tasks can include Fastload, Multiload, and FastExport.

5. The Queryband as Classification Criteria

Adding a query band to your session or transaction allows you to assign or exclude queries with this specific query band to or from a workload. If requests originate from a single or common source, you must utilize classification criteria to classify them into various workloads.

Basic Workload Definition Rules

Teradata 14.10 allows for the creation of up to 250 workloads. If a request does not correspond to any existing workload, it will be routed to the WD-Default workload. This workload is present on all Teradata systems and cannot be removed, thereby reducing the maximum number of workloads that can be defined to 249.

Begin your classification at the request source level. If users log in as standard users, you may want to include queryband classification to enable detailed workload classification. Additional classification criteria, such as request-target and query characteristics, can be added as necessary.

Remember that certain classification regulations (such as listings and exclusions) can impede upkeep. Additionally, numerous standards are complicated and perplexing.

Restrict the quantity of workloads to optimize system performance. Each workload affects the others and an excessive amount hampers comprehension of their collective impact.