When Load Utility Throttle Slots Are Released
Many sites are managing concurrency of their utility jobs by means of utility throttles combined with workload throttles. Utility throttles set system-wide limits for total number of utility jobs of a specific type that can run concurrently. For example, you may not want more than 20 load jobs from any source running at the same time. Workload throttles (specifically on workloads that include “Utility” classification criteria) can be used to limit load jobs by user or by application. For a given load utility to run, both the counter for the utility throttle and the counter for any applicable workload throttle must be under their respective limits.
Utility jobs running in the Teradata Database have a utility unit of work (UOW), as illustrated in the graphic below. The UOW begins on the utility's CHECK WORKLOAD END statement. The UOW completes on the END LOADING statement. If a utility is delayed due to throttles, that delay happens on the CHECK WORKLOAD END statement, the point at which the UOW begins. The check to see if the utility can run immediately, or be delayed is made before any utility sessions are logged on, so no active sessions are held while a utility job is in the delay queue.
Both utility throttles and workload throttles (specifically, workloads with “Utility” classification) will allocate their throttle slot (if one is available) as part of the CHECK WORKLOAD END statement. As mentioned above, this is also the statement where all the utility sessions are logged on. On a busy system there could be a gap between the CHECK WORKLOAD END STATEMENT and the BEGIN LOADING statement, due to the time it is taking to connect all the utility sessions under those conditions.
When the UOW is complete at the END LOADING statement, the workload throttle will release the throttle slot and decrement its counter. At that point the workload throttle will be able to give that throttle slot to another utility job.
However, the utility throttle will not release its throttle slot until all of the clean up work related to the utility has been completed. This clean up includes SQL, such as DROP TABLE, that takes place after the END LOADING statement. This clean up work is not part of the utility job’s UOW and will classify to a workload as a normal SQL statement. But from the perspective of workload management, this post-UOW clean up is part of the loading activity, and has to be completed before a system-level utility throttle slot is made available to another utility job.
Normally, this window between when the workload-level throttle slot is released and the system-level utility throttle slot is released is very minor and not usually noticeable. But if the system is very congested, and especially if there is contention on the AccessRights table, the clean up work may take longer. Under those circumstances it may appear that utility throttles are holding their throttle slots longer than they should. Recognize that the system-level utility throttle slot is being held during this clean up activity. This is normal behavior, and can be resolved by addressing the causes of contention that are slowing down the clean up work.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.