Basic Teradata Workload Management without TASM

Full-featured Teradata workload management, called the TASM (Teradata Active System Management), is not available for the appliance systems or Active EDW Systems without a TASM license.

The Teradata Integrated Workload Management

Nevertheless, Teradata System without TASM includes at least the so-called “Teradata Integrated Workload Management”, which offers many core functionalities of TASM.

The “Teradata Integrated Workload Management” is all about assigning the different kinds of work running on our platform to a specific workload.

We could, for example, define workloads for Single AMP operations, Load tasks, Adhoc Queries, Strategic Queries, etc.

Whenever a query enters the system, it will be handled by the Parsing Engine, and several typical parsing activities will take place, such as syntax checking, checking for permissions, etc.

Optimization of the query will follow, and the resulting execution plan is a sequence of query steps passed to the AMPs.

As soon as the AMPs receive the query steps, the Integrated Workload Management knows all the technical characteristics and additional information, such as the user id or the login account. All this information is used to map the query to one of the existing workloads.

Classification Criteria

Classification criteria do the mapping of this combined information to a specific workload.

Query characteristics can be grouped into three main groups:

The Source of the Query:

• User
• Account
• Application
• ClientID
• Profile
• Queryband
• IP Address

The target of the query:

• Database
• Table
• View
• Function
• Stored Procedure

The technical characteristic of the query:

• Join Type
• Estimated runtime
• Statement Type
• Estimated Final Row Count
• Percentage of rows accessed
• Load Utility Type (Fastload, Multiload, etc.)
• Number of AMPs involved

The classification criteria can be described in one short sentence:

Who is accessing which database objects, and which technical characteristics does the query have?

But classification can not only be used to map queries to defined workloads. The Teradata Integrated Workload Management offers two additional features on a system and workload level:

Filters and Throttles

Filters prevent inappropriate queries from execution (or at least issue a warning, which can be used to identify such queries).

Such a filter could be: Don’t allow any reporting user logins between midnight and 6 am. With this filter, any reporting user query would be simply rejected, and the user would get an error message.

Throttles are used for concurrency control. Again, classification is used to identify queries with the same characteristics. Throttles limit the number of queries with the same characteristics that can be executed simultaneously. New queries are put into a delay queue if the limit is exceeded. Until Teradata 14.10, the delay queue was always a FIFO queue (First In, First Out). Since Teradata 15.10, delay queue entries can be ordered by timeshare priority (Low, Medium, High, Top, Tactical), which we will cover in the next section.

Again, classification is used to identify queries with the same characteristics. Throttles limit the number of queries with the same characteristics that can be executed simultaneously. New queries are put into a delay queue if the limit is exceeded. Until Teradata 14.10, the delay queue was always a FIFO queue (First In, First Out). Since Teradata 15.10, delay queue entries can be ordered by timeshare priority (Low, Medium, High, Top, Tactical), which we will cover in the next section.

New queries are put into a delay queue if the limit is exceeded. Until Teradata 14.10, the delay queue was always a FIFO queue (First In, First Out). Since Teradata 15.10, delay queue entries can be ordered by timeshare priority (Low, Medium, High, Top, Tactical), which we will cover in the next section.

Throttles can be applied on the system level (limiting the total number of queries executing in parallel), and on the workload level (restricting the number of parallel queries per workload definition). A system throttling possibility can be applied to the load utilities (number of parallel fast loads, multi-loads, and fast export).

Workload Priorities

Teradata Integrated Workload Management offers the following priorities:

• Tactical Tier
• Top
• High
• Medium
• Low

The tactical tier has access to all the system resources and should, therefore, be used very carefully, for example, for single AMP Index accesses only. To mitigate the risk for the system, if long-running queries are assigned by mistake to the tactical tier, an exception handler will automatically assign any query which consumes more than a few resources to a lower priority.

Top, High, Medium, and Low are the so-called Timeshare Tier. These priorities are fixed. The Top priority receives eight times more resources than the Low priority, four times more resources than the medium priority, and two times more resources than the High priority.

Be warned that the Timeshare Tier receives only the resources not consumed by the tactical tier.