DWHPro Insights

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

By Roland Wenzlofsky ·

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

Workload-management feature availability depends on the actual platform and licensing. The former blanket exclusion of Active EDW and appliance systems is not established and should not guide deployment choices.

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 can define workloads for Single AMP operations, Load tasks, Ad hoc 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.

Classification uses request source, objects and plan characteristics before relevant admission and execution decisions; it is not simply a mapping first performed by AMPs after execution steps arrive.

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

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

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

Reserve the tactical tier for bounded low-resource requests. Single-AMP indexed requests are common candidates, but single-AMP access is not an exclusive requirement. Configure exceptions and protect other work from resource-intensive requests.

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.

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.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Follow DWHPro in Google to see our articles more often in Search.

1 thought on “Teradata Integrated Workload Management: Features, Classification Criteria, Filters, Throttles, and Workload Priorities”

  1. Hi Roland,
    First of all , Thanks for the awesome article. Learned a lot from here.
    I have one doubt.
    If the Tactical Tier consumes,say 25% of resources, then remaining 75% would be available for Timeshare with highest resources being assigned to Top and lowest to Low.
    If the Top (or any of the priority) receives ,say 25% of the 75% from Timeshare, then how the distribution of this 25% would be for Top ? Will this 25% be shared among all the queries assigned to Top or each of the queries under Top will be using the full 25% of resource allocated to Top ?

    Thanks,
    Dhruba

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Protected by Security by CleanTalk and CleanTalk Anti-Spam