Teradata Workload Management Basics

Roland Wenzlofsky

August 17, 2015

minutes reading time


Everyone with experience with Teradata tuning will have heard the term “workload.” This article will deal with the definition of this term and explain in detail what it is and why it’s helpful to map each Request (query or load operation) to a workload.

What is Teradata Workload?

A workload is a group of requests with common characteristics. The grouping of requests into workloads has some advantages over handling single requests.
It’s more efficient to manage groups of requests than to handle each Request individually, as common request characteristics allow to apply standard settings and rules to the group at once.

Grouping requests into workloads gives us a better overview of who uses the system and how many resources are used. Armed with this knowledge, it is much easier to assign priorities to performance tuning tasks based on workloads (for example, setting the highest priority to tune activities for your critical, tactical workload).

Another essential concept behind workloads is the assignment of resources (CPU, IOs) and preferences (for example, the frequency and duration of CPU usage) on a group level. Business needs usually define how critical a joint group of requests are. Common priorities and resource shares can be assigned by putting these requests into a workload. The Teradata priority scheduler takes care of resources and priorities assigned to each workload.

Different releases of Teradata systems use other implementations of priority scheduling. While the Teradata 14 priority scheduler is closely connected with the SLES 11 Linux scheduler and its priority hierarchies, previous Teradata releases running on SLES 10 implemented a different priority scheduling method.
Nevertheless, these details are not essential to understanding the concept of workloads and workload classification. We will discuss the Teradata priority scheduler in one of our following articles related to workload management.

Whenever a request matches all the characteristics defined for an individual workload, it will be assigned to this workload. Teradata will apply the workload-specific rules and settings to the Request. This process is called workload classification and the central concept of adding resources and limits to requests.

All workloads are managed in the ViewPoint “Workload Designer.”

Workload Characteristics are used to assign each Request to a Workload

Workload characteristics can be any of the following types below (We can use each aspect on its own or combined with other features to classify requests into workloads), but be aware that the lists below may not be complete as each release of Teradata adds new features.

  1. Request source:

The request source defines where a request was initiated. The request source(s) can be any of the following:  

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

A query will be classified into a workload if a request source matches or does not match the request sources defined for this workload. We can set several request sources for a workload:

Username = ‘ETLUSER1’
Client IP address = 192.168.0.54

If several request sources are defined for a workload, they are AND combined (in the above example, this means that the login user has to be ‘ETLUSER1’ and must connect from the client with IP address 192.168.0.54) to be classified into the considered workload. We assume both request sources are used to include them in the workload.

As a general rule: Up to Teradata 13.00, several characteristics have been combined by OR. Since Teradata 13.10, characteristics are coupled with the “AND.” This rule is valid for all features (not only for request sources).

Hint: All of these request sources can be found in the DBC.DBQLOGTBL table.

2. Request target:

Teradata will execute each Request against one or several database objects, such as databases, tables, views, macros, stored procedures, and UDFs.

By defining request targets in your workload characteristics, you can assign queries to a workload if particular database objects are accessed.

Since Teradata 14, it’s possible to define additional subcriteria for each request target, considering how the objects are used. But subcriteria are not allowed for UDFs.

Some examples of subcriteria are:

– Add a query or exclude a query from 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 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 detailed workload classification is possible by analyzing each Request. These are the query attributes that are used to classify a query to a workload:

– 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

The workload management can classify load tasks (Fastload, Multiload, FastExport) and backup/restore tasks into separate workloads.

5. The Queryband as Classification Criteria

Adding a queryband to your session or transaction, this queryband can assign or exclude queries with this queryband to/from a workload.
You will need these classification criteria if requests come from a single or common request source, but you need to classify them into different workloads.

Basic Workload Definition Rules

Currently (Teradata 14.10), up to 250 workloads can be defined. Requests that do not match any workload will be assigned to the WD-Default workload, which is available on each Teradata system and cannot be deleted (which counts against the 250 workload limit).

Start your classification on the request source level. If your users log on with a standard user, consider adding queryband classification. Querybands will help you to classify your workload on a very detailed level. If needed, you should only add additional classification criteria (request-target, query characteristics).

Remember that many classification rules (including lists, exclude lists) make maintenance difficult. Furthermore, a lot of standards will be hard to understand and confusing.

Limit as well the number of workloads. All workloads interact with each other. A huge number of workloads make it difficult to understand each workload’s overall impact on the whole system’s performance.

  • Avatar
    Manish Khobragade says:

    Hi Roland,

    Thanks for this crucial and extremely important article.

    Can you advise on techniques on understranding already established TASM system on existing datawarehouse.(I am working on same DWh but not on performance tuning and want to understand existing workload management system)

    Regards,
    Manish

  • Abdul Wahab
    Abdul Wahab says:

    This is a very helpful article to understand the basics of workload, scheduling, resource management in Teradata.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like

    >