What are Throttles used for in Teradata Workload Management?
Throttles limit the number of concurrent sessions, requests, or utilities. We use throttles in Teradata workload management to protect critical resources – such as memory, AMP worker tasks, and CPU – from being exhausted. Throttles’ usage helps to reduce resource contention on systems with a high level of concurrency, usually increasing the total system performance.
Throttling Requests
Request throttling limits the number of concurrent requests (queries). The defined request limit is ensured at any point in time. The workload management uses a simple counter to keep track of the number of executing requests. If the limit is reached and additional queries are ready for execution, they are added to the so-called delay queue (this is the most used application) or rejected immediately. As soon as the request counter falls below the limit, the first query waiting in the queue (FIFO) will be executed. Requests being executed never will be moved back to the delay queue.
Request throttles can be defined per workload or on the system level.
Workload throttles enforce the limits for the requests executing on behalf of the workload. They are applied after requests have been classified into the workload.
System throttles enforce limits on the system level. Without any further request classification, the limits will be implemented for each and every single query. Nevertheless, we explained all the classification criteria 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.).
System throttles and workload throttles can be active for a request simultaneously.
Enforcing Session Limits
Session limits are used to restrict the number of parallel sessions allowed at one time. In contrast to request throttles, sessions cannot be delayed, enabling the delay of requests. If the session limit is reached, no more sessions can be logged on (logins will be rejected).
Session limits can be applied for the complete system or classification targets (user, account, IP, etc.). Additional classification criteria, apart from targets, can’t be used.
Throttling Best Practice
It’s much easier to deal with workload throttles than with system throttles. The impact of workload throttles is limited to a group of queries. Workloads collect requests with common characteristics; Workload throttles allow to define limits for each group of requests, and the impact of these limits can be easily identified.
Most times, the same limitations can be achieved much more straightforwardly with workload throttles. System throttles are much more complicated to implement from a classification point of view.
Nevertheless, system throttles have their place in workload management. Usually, they are used to manage situations not related to specific workloads but the overall system condition, such as periods of system recovery or critical backup / restore activities. These are examples of when it may make sense to limit requests on the system level.
However, you are defining your throttles: Never apply throttling to your tactical workload (the reason should be obvious).
How to identify Throttling in DBC.DBQLOGTBL
The query log keeps track of all delays caused by either workload or system throttles, Â helping to identify bottleneck situations and other performance-related issues on your system. Two columns show us all information we need:
WDDelayTime  and DelayTime
WDDelayTime demonstrates the number of seconds a request was a delayed in a workload queue. This column is only referring to workload delays.
DelayTime is the total number of seconds the request was delayed, mainly the sum of workload delays and system delays. Unfortunately, as workload and system delays can overlap for the same request, we can’t just subtract WDDelayTime from DelayTime to get the system delays.
There is no way to calculate the system delays if workload delays and system delays happen.
What we know:
- If WDDelayTime equals DelayTime, we are aware that there were no system throttles applied.
- If WDDelayTime is NULL and DelayTime > 0, we are aware of the system delay time (as no workload delays have been used)
All workload management-related columns are available as well in the DBC view QryLogTDWM.