Dynamic workload management features are only available on Teradata Active System Management (TASM) systems. 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 when the number of AMP worker tasks (AWTs) in use surpasses a specified 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 it’s being activated as soon as a specific time is reached. On the other hand, health conditions are not planned or even plannable in advance. Depending on the system load, a health condition is triggered, 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 health condition and planned event combination leads to a specific 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 that prioritizes 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 specific set of workload rules (in our example, we named them to make it easy to identify their purpose).
TASM State Switching
TASM checks at regular intervals if relevant events occur—initially, TASM checks for health conditions. A subsequent step 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 occurred, Teradata would use the workload management rules of the related state.
If more than one planned environment matches during the check, Teradata will use the rightmost in the state matrix. 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 each request in the planned environment and health condition in which Teradata executed it. This is a mighty tool for the experienced Teradata tuning expert as it allows us to analyze performance issues, identify wrongly assigned queries, and much more.
The following columns carry relevant information:
OpEnvId = Planned Environment
SysConId = Health Condition
Another helpful column is LastStateChange, which shows the timestamp when Teradata activated the planned environment for the request.