Basic Teradata Workload Management without TASM

1
428

teradata sql tuningFull-featured 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 certain workload.

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

Whenever a query is entering 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 which are passed to the AMPs.

As soon as the AMPs received 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 together is used to map the query to one of the existing workloads.

Classification Criteria

The mapping of this combined information to a certain workload is done by classification criteria.

Query characteristics can be grouped into 3 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/or 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 just 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 which can be executed at the same time. If the limit is exceeded, new queries are put into a delay queue. 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), 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 which can be executed at the same time. If the limit is exceeded, new queries are put into a delay queue. 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), we will cover in the next section.

If the limit is exceeded, new queries are put into a delay queue. 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 system level (limiting the total number of queries executing in parallel), on workload level (limiting the number of parallel queries per workload definition), and there is a system throttling possibility which can be applied to the load utilities (number of parallel fast loads, multi-loads, and fast exportd).

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 8 times more resources than the Low priority, 4 times more resources than the medium priority, and 2 times more resources than the High priority.

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

Questions?
If you have any questions about all this, please ask in the comments! I’ll be paying close attention and answering as many as I can. Thank you for reading. Whatever this blog has become, I owe it all to you.
Our Reader Score
[Total: 4    Average: 4.5/5]
Basic Teradata Workload Management without TASM written by Roland Wenzlofsky on October 13, 2017 average rating 4.5/5 - 4 user ratings

1 COMMENT

  1. Hi Roland,
    First of all , Thanks for the awesome article. Learned a lot from here.
    I have one doubt.
    If the Tactical Tier consumes,say 25% of resources, then remaining 75% would be available for Timeshare with highest resources being assigned to Top and lowest to Low.
    If the Top (or any of the priority) receives ,say 25% of the 75% from Timeshare, then how the distribution of this 25% would be for Top ? Will this 25% be shared among all the queries assigned to Top or each of the queries under Top will be using the full 25% of resource allocated to Top ?

    Thanks,
    Dhruba

LEAVE A REPLY

Please enter your comment!
Please enter your name here