Teradata Integrated Workload Management: Features, Classification Criteria, Filters, Throttles, and Workload Priorities
Learn how Teradata Integrated Workload Management assigns workloads to queries and offers features like filters and throttles to improve system performance.
The complete Teradata workload management, known as TASM, requires a TASM license and is not accessible for appliance systems or Active EDW Systems.
The Teradata Integrated Workload Management
However, the Teradata System lacking TASM still incorporates the "Teradata Integrated Workload Management" feature, which provides numerous essential TASM functions.
Teradata's Integrated Workload Management assigns various workloads on the platform to specific tasks.
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Early subscribers keep launch access before the paid plan launches.
We can define workloads for Single AMP operations, Load tasks, Adhoc Queries, and Strategic Queries.
The Parsing Engine handles all incoming queries, performing various parsing tasks, including syntax verification and permission checking.
The query will be optimized, and the AMPs will receive a sequence of query steps as the resulting execution plan.
Upon receipt of the query steps, the AMPs are equipped with the technical characteristics and relevant data, including the user identification and login account, through the Integrated Workload Management system. Using this information, the query is mapped to an existing workload.
Classification Criteria
Classification criteria map combined information to a workload.
Query characteristics can be classified into three main categories:
Get the next issue by email.
The Source of the Query:
- User
- Account
- Application
- ClientID
- Profile
- Queryband
- IP Address
The Target of the Query:
- Database
- Table
- View
- Function
- Stored Procedure
The technical characteristic of the query:
- Join Type
- Estimated runtime
- Statement Type
- Estimated Final Row Count
- Percentage of rows accessed
- Load Utility Type (Fastload, Multiload, etc.)
- Number of AMPs involved
In short, the classification criteria answer: which users are accessing specific database elements, and what are the technical parameters of the query?
Classification can serve multiple purposes beyond mapping queries to established workloads. Teradata Integrated Workload Management provides two supplementary features at the system and workload levels:
Filters and Throttles
Filters prevent unauthorized queries from executing and can issue a warning to identify them.
For example, implement a filter to restrict reporting user logins from midnight to 6 am. This filter will deny any reporting user queries and display an error message.
Throttles enable concurrency control by identifying queries with similar characteristics and limiting the number of simultaneous executions. New queries are placed in a delay queue if the limit is exceeded. Previously, the delay queue operated strictly on a First In, First Out (FIFO) basis. However, since Teradata 15.10, delay queue entries can be prioritized based on timeshare priority (Low, Medium, High, Top, Tactical), which we will discuss in the following section.
Throttles can be implemented at both the system and workload levels. System throttles limit the total number of queries executing in parallel, while workload throttles restrict the number of parallel queries per workload definition. Load utilities can also be subject to system throttling, which controls the number of fast parallel loads, multi-loads, and fast exports.
Workload Priorities
Teradata Integrated Workload Management provides the following priorities:
- Tactical Tier
- Top
- High
- Medium
- Low
The tactical tier has complete access to system resources and should be cautiously utilized, restricted solely to single AMP Index accesses. To minimize system risk, an exception handler will automatically reassign any query that consumes excessive resources to a lower priority in the event that long-running queries are erroneously assigned to the tactical tier.
The Timeshare Tiers have four levels: Top, High, Medium, and Low. These levels are unalterable. The Top level is allocated eight times the number of resources as the Low level, four times the number of resources as the Medium level, and twice the amount of resources as the High level.
Beware that the Timeshare Tier only receives unused resources from the Tactical Tier.
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe before the paid plan launches and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.