Dynamic workload management features are only available on systems using Teradata Active System Management (TASM). Teradata Integrated Workload Management (TIWM) doesn’t offer most of these features.
What is Teradata Dynamic Workload Management Features?
While TIWM solely relies on static workload definitions (with a few exceptions we will mention later), TASM offers features that allow us to adapt the workload management dynamically to certain system conditions.
Dynamic changes are always based on events. TASM distinguishes between time-based events (a defined time window is entered or left) and system-specific events, such as the event that the number of AMP worker tasks (AWTs) in use surpasses a defined limit.
System-specific events are called “health conditions” in TASM; time-based events are called the “planned environments”.
The differentiation makes sense: Time based events are always planned. You define a time window, and as soon as a certain time is reached, it’s being activated. Health conditions, on the other hand, are not planned or even plannable in advance. Depending on the system load, a health condition is activated, but you can’t know in advance when (or if) this will happen.
TASM combines these two types of events in the so-called state matrix. Each combination of a health condition and planned event leads to a certain state in the state matrix. Each state represents a set of workload management rules, such as throttles (request limits, session limits) and priorities,
Of course, as the workload designer, it’s up to you how many planned events and health conditions you define. In the simplest case, you will have one planned environment (the default one, which is called “Always”) and one health condition (called “Normal”).
A commonly used approach is defining at least two planned environments, one covering the daytime (protecting reporting users and applications) and a second one, which is giving higher priority to the nightly batch loads. Below you can see a state matrix with two planned environments (“day” and “night”) and two health conditions (“1” and “2”). Each orange box presents a state characterized by a certain set of workload rules (in our example, we named them to make it easy to identify their purpose).
TASM State Switching
TASM is checking at regular intervals if relevant events took place—initially, TASM checks for health conditions. In a subsequent step, it checks for a switch to another planned environment. During each check, one or both types of events could have happened. If at least one event happened, the workload management rules of the related state would be used.
If more than one planned environment matches during the check, the rightmost in the state matrix will be used. Similarly, if more than one health condition matches, the one nearest to the bottom of the state matrix is used.
The QueryLog and Dynamic Workload Management
The DBC.DBQLOGTBL table makes it easy to identify for each request in which the planned environment and health condition it was executed. This is a mighty tool for the experienced Teradata tuning expert as it allows us to analyze performance issues, to identify wrongly assigned queries, and much more.
The following columns carry relevant information:
OpEnvId = Planned Environment
SysConId = Health Condition
Another useful column is LastStateChange, as it showed the timestamp when the planned environment for the request was activated.