Teradata Integrated Workload Management: Features, Classification Criteria, Filters, Throttles, and Workload Priorities

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.

We could 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:

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

The classification criteria can be succinctly summarized in a single sentence.

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.

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 surpassed. In Teradata 14.10 and prior versions, the delay queue was strictly First In, First Out (FIFO). Still, 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.

Classification identifies queries with similar characteristics, while throttles restrict the simultaneous execution of such queries. If the limit is exceeded, new queries enter a delay queue. Previously, the delay queue only operated as a FIFO queue. However, with Teradata 15.10, delay queue entries can prioritize by timeshare levels (Low, Medium, High, Top, Tactical), which we will discuss in the next section.

New queries are placed in a delay queue if the limit is surpassed. Previously, in Teradata versions before 14.10, the delay queue operated solely on a FIFO basis. However, in Teradata 15.10 and beyond, entries in the delay queue may be prioritized by timeshare levels such as Low, Medium, High, Top, and Tactical, which we will discuss further 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.