DWHPro Insights

Throttles in Teradata Workload Management: Usage, Request and Session Limits

By Roland Wenzlofsky ·

Throttles in Teradata Workload Management: Usage, Request and Session Limits

What are Throttles used for in Teradata Workload Management?

Throttles restrict concurrent sessions, requests, or utilities to safeguard vital resources, such as CPU, memory, and AMP worker tasks, from depletion. Their implementation in Teradata workload management minimizes contention for resources on highly concurrent systems, thus bolstering overall system performance.

Throttling Requests

Throttle queue order, exemptions and supported session criteria depend on release and configuration. Inspect the current rule definitions rather than treating FIFO or this historical criteria list as universal.

Throttles for requests can be defined either for individual workloads or at the system level.

Workload throttles limit requests executed for a specific workload category. These limits are applied after requests have been categorized.

System throttles enforce limits on the system level. Without any further request classification, the limits will be applied to every single query. Nevertheless, all the classification criteria explained in the first part of our workload management series (Teradata Workload Management – The Basics) can be used to restrict limits to a group of queries with common characteristics (source, target, queryband, etc.).

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

Both system and workload throttles may concurrently affect a request.

Enforcing Session Limits

Unlike request throttles, which can delay requests, session limits restrict the number of concurrent sessions. Once the session limit is met, any further logins are rejected.

Throttling Best Practice

Managing workload throttles is simpler than managing system throttles, as their impact is confined to a specific set of queries. Workloads are comprised of requests that share similar features. Implementing workload throttles enables the establishment of individual capacity limits for each set of requests, simplifying the identification of potential limitations.

Workload throttles are often a simpler solution to achieve the same limitations. Implementing system throttles is more complex when considering classification.

System throttles are useful for workload management, particularly in situations that do not pertain to specific workloads but rather to the system’s general condition. This includes times of system recovery and critical backup or restore activities. In such cases, limiting requests on a system level can be beneficial.

Avoid applying your throttles to your tactical workload due to the potential implications when configuring them.

How to identify Throttling in DBC.DBQLOGTBL

The query log records delays resulting from either workload or system throttles, enabling the identification of bottleneck situations and other performance-related issues. Two columns provide all the necessary information.

WDDelayTime  and DelayTime

WDDelayTime shows the number of seconds a request was delayed in a workload queue. This column refers to workload delays only.

DelayTime is the total number of seconds the request was delayed, meaning the sum of workload delays and system delays. Unfortunately, as workload and system delays can overlap for the same request, we can’t subtract WDDelayTime from DelayTime to get the system delays.

Calculating system delays is impossible when both workload and system delays occur.

Our only knowledge:

  • WDDelayTime equal to DelayTime does not prove that no system throttle applied: system delay can overlap fully with workload delay. These totals alone cannot reconstruct the overlapping intervals.
  • If WDDelayTime is NULL and DelayTime > 0, we are aware of the system delay time (as no workload delays have been used)

The DBC view QryLogTDWM includes all columns related to workload management.

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.

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