Determining Which Throttle Caused a Delay

Blog
The best minds from Teradata, our partners, and customers blog about relevant topics and features.
Teradata Employee

Throttles are a workload management technique for controlling concurrency in a data warehouse. When a throttle is an active part of a TASM or TIWM ruleset, a counter is kept of how many requests are currently running that are under its control. When a request wants to begin to execute that would cause this counter to exceed the throttle’s defined limit, that request is placed in delay queue.

 

It is not unusual for a request to be under the control of more than one throttle, so when you are analyzing throttle impact after the fact, it can be difficult to know which throttle was responsible for the delay action. There is a field in DBQLogTbl named TDWMRuleID that can aid you in making that determination.

 

Background

The two most commonly used throttles are system throttles and workload throttles. System throttles can manage any or all requests that enter the database, based on how the administrator has defined the throttle’s classification criteria. Workload throttles are simpler to understand because they are tightly bound to one workload. And they manage all requests that have already classified to that workload. Workload throttles themselves do not have classification criteria.

 

DBQL Throttle Fields

You can validate that a request was delayed due to a throttle, and how long it was delayed, by examining the DelayTime field in DBQLogTbl. So how do you know when you look at DelayTime which type of throttle and which specific throttle caused a delay?

 

If a query is delayed due to a system throttle, then there will be a value in DelayTime as well as a value in the TDWMRuleID field. If TDWMRuleID is not NULL, it will be populated with the system throttle ID. You can take that information and join back to TDWM.RuleDefs table if you want the system throttle name.

 

SysThrottleOutput.png

 

If TDWMRuleID is NULL, that indicates the delay was due to a workload throttle or a group throttle. You will still see the time it was delayed in DelayTime, just as is true for a system throttle, but to determine which workload throttle caused the delay you will need to look at the WDID field. The WDID will identify the workload throttle on the workload that was responsible for the delay.

 

WDThrottleOutput.png

 

Conclusion

There is only a single throttle delay queue. Requests delayed by either system throttles or workload throttles will be placed in the same delay queue, and DBQLogTbl DelayTime will indicate the total time the request was delayed. If you see a value in TDWMRuleID, then you know the delay was due to a system throttle, otherwise assume that it was delayed by a workload throttle.