Dynamic workload management features are only available on systems which are using Teradata Active System Management (TASM). Teradata Integrated Workload Management (TIWM) doesn't offer most of these features.
What are Teradata Dynamic Workload Management Features?
While TIWM solely relies in static workload definitions (with a few exceptions we will mention later), TASM offers features which allow 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 type 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 to define at least two planned environments, one covering the day time (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 which is characterized by a certain set of workload rules (in our example, we named them in a way 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 type of events could have happened. If at least one event happened, the workload management rules of the related state will 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 conditions are matching, 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 planned environment and health condition it was executed. This is a mighty tool for the experienced Teradata tuning expert as it allows to analyze performance issues, to identify wrongly assigned queries, and much more.
The following columns carry the relevant information:
OpEnvId = Planned Environment
SysConId = Health Condition
Another useful column is LastStateChange, as it shows the timestamp when the planned environment for the request was activated.