An Exception to Workload Exception Demotions

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

When a “Workload” is defined in Teradata Workload Management’s TASM feature it may be given a supplemental capability called a “workload exception”. When a workload exception is defined, it will cause characteristics of each running request that classifies to the workload to be monitored. Depending on the thresholds that have been defined, a workload exception may take an action if a request reaches that threshold.

 

Workload Exception Criteria

There are several different criteria that can define a workload exception. The current threshold-based exception criteria include:

  • Maximum spool rows
  • I/O count
  • Spool size
  • Blocked time
  • Elapsed time (Including or excluding delay and blocked times)
  • Number of AMPs
  • CPU time (sum over all nodes)
  • Tactical CPU usage threshold (per node)
  • Tactical I/O physical bytes (per node)
  • I/O physical bytes

You select which type of exception you wish to include in a workload, then pick the threshold value that meets your needs. For example, you could decide that when a query in this workload consumes up to 200 CPU seconds, that constitutes an exception and you want to take action on the query.

 

Workload Actions

The next step is to specify the action you want to take place should the threshold be reached by an actively-running query in the workload. That action could be of low impact, such as sending an email to the administrator, or logging the condition. However, most workload exceptions specify a stronger action of demoting the request to another workload. This demotion is usually to a workload with a lower priority.

 

The available exception actions include:

  • Abort
  • Abort on Select
  • Change Workload
  • Notifications
    • Alert (must be defined in Common Alerting Mechanism)
    • Run program
    • Post to a Queue Table

 

Elapsed Time Workload Exceptions

The Elapsed Time workload exception is special. It is limited in the actions that it can be paired with. If you have selected Elapsed Time as the workload exception criteria, this will not be compatible with the “Change Workload” action. That particular action will be disabled.

 

This is designed to work this way. Elapsed time is more a reflection of system conditions than of query characteristics. For example, there might be a normally quick query that runs 10 or 20 times longer than usual at a time when the system is congested, or when that workload has experienced a high arrival rate.

 

Demoting such queries could result in inconsistent behavior, based solely on how busy the system is at the time query executes. For that reason, selecting an action to move requests to a different workload when their elapsed time reaches a specified threshold is disallowed. Elapsed Time exceptions are more suitable for a notification action, rather than an action that changes priorities.