Intrepreting DBQL DelayTime in Teradata 13.10
Starting in Teradata 13.10, there is a single delay queue for all throttles. This means that queries delayed by system throttles will reside in the same queue as queries delayed by workload throttles. In earlier releases, delay queues were set up independently by type of throttle, and each workload throttle had its own dedicated queue.
Bringing together all delayed objects into a single queue streamlines the entire throttling experience and makes it easier and more accurate to manage internally. However, as a side-effect, the DelayTime field in DBQL needs a second look. DelayTime takes requires slightly different interpretation in 13.10 than you gave it in earlier releases.
DBQL WDDelayTime has not changed
First, let’s consider what has NOT changed in DBQL delay reporting for 13.10. WDDelayTime has the same meaning as it always has had. It represents the time in seconds that the query was delayed in the delay queue due to a workload throttle.
You will not see this column in DBC.QryLog view, as that view only references purely DBQL fields. Use the view titles QryLogTDWM to see the delay columns, and other TDWM fields, such as WDID.
In 13.10, DelayTime is the total time spent in the delay queue, either due to one or more system throttles, just a WD throttle, or a combination of both. It doesn't matter what reason there was for the delay or how many throttles contributed to the delay. This is the total wall clock time the query was delayed.
However, in 13.10 you cannot subtract WDDelayTime from DelayTime, take the difference, and assume that is time delayed by a system throttle. You could do that in earlier releases because WD throttles had their own separate delay queue, distinct from system throttles. With separate delay queues, as before 13.10, you could safely assume that a query first went into the WD delay queue, and only when released from that delay queue could the query be held in the system throttle queue. There was no overlap in delay times. Now there is. In 13.10 there is one single delay queue, so queries can be in that queue due to being delayed by both types of throttles at the same time.
When delayed by both types of throttles
If you are only delayed by a WD throttle, then both DelayTime and WDDelayTime metrics will be the same. If you are only delayed by a system throttle, WDDelayTime will be null, and you know for sure that the time reported in DelayTime was caused by one or more system throttles.
For example, if DelayTime showed an hour, and WDDelayTime showed 20 minutes, we don't know if the system throttle mandated a delay for that query of 40 minutes or for 1 hour. This is because the system throttle and the WD throttle could both be keeping the query in the delay queue during the same number of minutes. We can conclude, however, that that query was delayed for a total of 1 hour, and that approximately 20 minutes was due to the WD throttle. But we can only conclude that the system throttle caused a delay of possibly as long as an hour.
DelayTime is the total time spent in the delay queue:
- Due to one or more system throttles
- Due to a workload throttle
- Due to a combination of the above
- Due to a utility throttles (delayed on the CHECK WORKLOAD END statement)
WDDelayTime is the total time delayed by a workload throttle.
If your query is only delayed by a WD throttle:
- WDDelayTime = Time actually delayed
- DelayTime = The same time as in WDDelayTime
If your query is only delayed by a system throttle:
- WDDelayTime will be NULL
- DelayTime = Time actually in the delay queue
If your query is delayed by both:
- WDDelayTime = WD throttle delay time
- DelayTime = Wallclock time delayed by both, does not identify system throttle contribution