Teradata Utility Workload – Overview
Our previous articles about Teradata workload management covered how SQL requests are classified into workloads. We talked about throttles, dynamic workload management, and the priority scheduler.
Part of the Teradata Internals and Operations guide.
This article discusses utility workloads, or user requests that do not implement the SQL protocol. The current utility jobs include:
- Loads (Fastload, Multiload)
- Unloads (FastExport)
- Backup & Restore (ARCMAIN, BAR)
The aforementioned tasks execute their respective protocols. The utility jobs are distinct from SQL workloads due to their specialized and optimized nature. As a result, they do not require advanced workload classification criteria like “query characteristics” (e.g. product joins, ALL-AMP). Nevertheless, workload management systems (TASM, TIWM) provide unique additional features specific to this workload.
Teradata implements the below protocols to cover utility workload:
- The FastLoad Protocol
- The MultiLoad Protocol
- The FastExport Protocol
- The Backup & Restore Protocol
Multiple implementations exist for each utility protocol, such as the FastLoad protocol, which supports the Fastload Utility, TPT Load operator, and JDBC Fastload.
Third-party utilities can utilize these protocols through TPT, although certain workload management capabilities may not exist.
During execution, all utilities initiate at least one SQL session and several utility sessions, except BAR, which does not require any utility sessions. The control session, which is compulsory, executes all SQL statements pertaining to the utility.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookLoad and unload utilities may use a second SQL session (the auxiliary session) to maintain a restart log.
Managing utility resource usage involves:
- Concurrency Levels: Defines the maximum number of utilities that can be executed simultaneously.
If the limit is exceeded, arriving utility jobs will be delayed or rejected. - Utility Session Limit: Defines the minimum and maximum number of sessions each utility job uses.
Teradata Utility Workload – Concurrency Management
Throttles enable the enforcement of concurrency limits and can be established at both the system and workload levels.
Teradata workload management enforces hard limits that cannot be surpassed by the user-defined utility or workload throttles.
The 30/60/350 utility counts and 60% AWT allocation belong to historical configurations. Current utility concurrency, worker reservations and session limits must be checked against the deployed system and rules.
If no user-defined utility or workload throttle with a reject option is configured, jobs will be delayed by the hard limits instead of being rejected.
AWT limits may delay or reject utility jobs, even if concurrency limits are unmet. It’s important to note that AWT limits are solely checked for FastLoad, Multiload, and FastExport without Spool.
Utility throttles, specified by the user, regulate resource allocation at the system level. Limits, such as “permitting a maximum of 15 Fastloads simultaneously,” can be established. It is imperative that these throttles fall within the range of the aforementioned strict limits.
You can define utility workloads and their throttles to organize your utility workload in greater detail.
To achieve this, include “utility” as a classification criterion in your workload. Further details about the workload can be provided by employing additional measures such as “request source,” “target,” and “query band.”
As previously stated, SQL protocol-specific criteria, such as “query characteristics,” cannot be used for classification.
Teradata Utility Workload – Session Management
Restricting the quantity of utility sessions is an additional technique for managing system resources.
Teradata workload management automatically manages sessions unless user-defined session limits override default concurrency limits set for utilities.
Effective workload management categorizes data as small, medium, or large. The default expectation is for data of medium size.
Adjust the default limits for loading data, whether it’s a small or large amount, using the query band below:
SET QUERY_BAND = 'UtilityDataSize=MEDIUM;' FOR SESSION;
Here are the standard guidelines for utility session limits:
Fastload and Multiload:
Medium Sized Data:
IF (“Number of AMPs” <= 20) THEN “Number of AMPs.”
ELSE MIN(20 + “Number of AMPs” / 20), 100)
Small Sized Data:
“Medium Sized Data” * 0.5
Large Sized Data:
MIN(“Medium-Sized Data” * 1.5, “Number of AMPs”)
FastExport:
Medium Sized Data:
IF (“Number of AMPs” <= 4 THEN “Number of AMPs” ELSE 4
Small Sized Data:
“Medium Sized Data” * 0.5
Large Sized Data:
MIN(“Medium Sized Data” * 1.5, 2 * “Number of AMPs”)
To customize session limits, users can define their own workload parameters. The criteria for classifying this workload include:
- Utility Name
- Request Source
- Query Band
- Data Size (via the “UtilityDataSize” query band)
To ensure the proper functionality of your utility jobs, it is recommended that you begin by designing your utility rules at the system level. Only specific workloads that cater to unique situations and requirements should be taken into consideration.
A valid single-value example is SET QUERY_BAND = ‘UtilityDataSize=MEDIUM;’ FOR SESSION;. This statement executed in isolation; no utility workload settings or jobs were tested.
Need help with query performance or platform costs?
Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book