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.

 

 

4 Comments
Enthusiast

Thanks Carrie !!

 

MY question is on Throttle for system users. Should we apply Throttle(workload) on user like viewpoint & tdwm in any case ? consider both users are part of bypass user list.

Also in case if we apply throttle on these users and few sessions are in delay queue for viewpoint user like 30-60 sec then what may be the impact ?

Teradata Employee

SandeepY,

 

I would not suggest putting a throttle on viewpoint and tdwm users. Those users are bypassed by default, and I do not believe you can un-bypass them. That means they will bypass any throttle rule that you set up. In addition, there is valid reason I can think of to throttle their requests, which are usually very short and are critical to workload management and monitoring.

 

Throttles are very useful, but when you apply throttles, look first at the low priority, more resource-intensive work.

 

Thanks, -Carrie

Enthusiast

Thanks Carrie !!

 

As you mentioned --"Those users are bypassed by default, and I do not believe you can un-bypass them. That means they will bypass any throttle rule that you set up"-- 

 

Please correct me if I misundertood this. Any throttle rule mean they should bypass workload throttle as well ?

But this is not the case, they follow workload throttle. 

 

Thanks

Sandeep.

Teradata Employee

Bypass means that the user will not be impacted by either a system throttle or a system filter.  If you refer to the TASM orange book, it says:

 

 Bypass

You can designate particular users, accounts and profiles that should be exempted from TASM filtering and throttling at the system level. For example, you may designate a special administrative user bypass privileges so that the DBA/User can always access the system for immediate troubleshooting purposes. Note that Bypass does NOT exempt requests from being managed by the Workload it is classified into, including Workload Throttling.