DWHPro Insights

Teradata Tactical Workload

By Roland Wenzlofsky ·

Teradata Tactical Workload

Introduction

This blog post discusses tactical workloads on a Teradata system. Despite Teradata’s implementation of features that support tactical workloads, this workload category remains challenging to manage. Selecting an optimal physical design is essential to meet user expectations for query speed.

Designing the Teradata tactical workload on a test environment can be frustrating, especially when it fails to meet user expectations on the production system.

I’ve observed that applications executing tactical queries often don’t satisfy users’ expectations. This is because they’re built with the same approach as a data warehouse designed for strategic workloads.

Consider a web application that enables users to access information about individual customers. This task pertains to the traditional OLTP model and may not be a fitting workload for a data warehouse system. Typically, users will anticipate the ability to navigate through a customer’s data promptly. Performance is critical to maintaining usability.

To ensure user satisfaction, it is crucial to attain consistent query run times. Inconsistencies, where a query may take 2 seconds at times and several minutes at others, are unacceptable. Unfortunately, I have frequently encountered this issue due to designers’ oversight of tactical workload specifics during physical database design. Typically, applications are developed on unloaded test systems, leading to surprises when competing with the production workload upon deployment.

The Cause of Performance Problems for Tactical Queries

Teradata is a database system optimized for parallel processing and efficiently reading large data sets. However, for tactical workloads, the focus is on optimizing access to individual rows on disk for efficient reading and writing.

Full table scans hinder tactical queries in Teradata, as their runtime is dependent on the overall load of the system. While enhancing runtimes by enlarging the system is possible, such a course of action is often not practical. Even with system expansion, Teradata’s query results may be slightly faster, but runtime remains unreliable. Sometimes, organizations purchase a dedicated Teradata system solely for their isolated tactical application. However, this solution is frequently unattainable for various reasons. This leads to the question: why restrict oneself to a Teradata system when a specialized system for tactical workloads can be utilized instead?

The aim of tactical query tuning is to reduce the required IOs to access a single or a few rows on a single AMP. Ideally, the query execution plan should localize the work to only one AMP and require just one data block transfer from disk to memory.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book
Teradata Tactical Workload

Workload management can reserve AWT capacity and prioritize tactical work according to configured rules. Tactical AWTs are not exclusively for single-AMP requests, and classification does not guarantee zero waiting.

If a query does not perform as expected, it is immediately downgraded to a lower-priority workload group to safeguard valuable resources. Further details will be provided in this blog post.

We strive for a single AMP access query to ensure consistent execution time. Thus, obtaining a stable execution plan with accurate statistics is imperative.

How to Design Tactical Workload in Teradata

We must differentiate between retrieval steps and joining steps.

Optimizing retrieve steps is simpler than optimizing join steps. To achieve this, we require primary index access in the form of UPI, NUPI, or a single table join index. By ensuring primary index access, we can localize data rows within minimal blocks and maintain a consistent access time.

Nested and merge joins can support limited-AMP plans in suitable cases. Available join methods and AMP scope depend on release, maps, predicates and optimizer choice; inspect the actual EXPLAIN plan.

Single-AMP operations offer the added benefit of utilizing ROWHASH-level locking during retrieval, eliminating the need for more intrusive locking methods. This increases concurrency when multiple tactical queries concurrently access the same table.

Our main objective in designing tactical workloads is to acquire an execution plan primarily involving single-AMP or group-AMP steps rather than full table scans.

As previously stated, the primary index access is our key tool in each step. Thus, employing a database specialized for the application that submits tactical queries can often provide benefits. In doing so, we can model indexes as we require. However, this may not always be feasible. In my experience, the same database is frequently utilized for tactical and strategic workloads, and the option to freely select the primary index is unavailable. Nevertheless, creating direct access is still plausible with Join Index or secondary indexes.

One effective technique is to create a table join index that functions as a “hashed” NUSI. This approach offers both the advantages of direct access and the flexibility of a non-unique index.

In row-partitioned tables, the partition columns must be included in the primary index to guarantee that a row is searched in a single partition.

Direct access through natural keys

Business users and front-end applications usually look rows up by a natural key, such as a product or customer code, rather than by the surrogate key used as the primary index. Keep the natural key in the physical model and give it a direct access path. A unique secondary index (USI) on the natural key lets the optimizer find the row without a full table scan and then join to related tables through their primary index, typically with a nested join:

CREATE TABLE Products
(
  ProductId   INTEGER NOT NULL,
  ProductCode CHAR(10)
) PRIMARY INDEX (ProductId)
UNIQUE INDEX (ProductCode);

CREATE TABLE Sales
(
  ProductId INTEGER NOT NULL,
  SalesId   INTEGER NOT NULL
) PRIMARY INDEX (ProductId);

SELECT *
FROM Products p
INNER JOIN Sales s
  ON p.ProductId = s.ProductId
WHERE p.ProductCode = 'Private';

A USI access is a two-AMP operation: one AMP holds the index subtable row, another the base row. Check in the EXPLAIN that the plan really uses the USI and a nested join rather than a full table scan; a nested join is a good fit for selective access, not a universal rule.

Small lookup tables on a single AMP

For small tables used in tactical queries, one option is to add a column that holds the same value in every row and make it the primary index. All rows then hash to one AMP, and a small table may fit in very few data blocks. Queries that specify that value in an equality condition on the primary index column can be resolved as a single-AMP retrieval instead of an all-AMP scan.

Repeated access may keep these blocks in memory, but memory residence is not guaranteed under memory pressure. Different constant values in several such tables can hash to the same AMP, so verify data placement and hot-spot effects before using the technique for many lookup tables.

Tactical Workload Exceptions as Tuning Help

To achieve peak performance, Teradata must accurately identify tactical workloads and ensure queries are processed exclusively in the appropriate tier without prematurely being downgraded to a lower-priority tier.

To prevent a single inefficient query from causing system-wide issues, Teradata has implemented security measures within the tactical workload tier. These measures, known as “Tactical Workload Exceptions,” monitor CPU usage and IO consumption and set specific limits. Queries that exceed these limits are automatically moved to a lower-priority workload.

We can set limits for IO and CPU usage for all combined nodes or individual nodes.

The priority scheduler manages the limits per node (as an independent computer), and TASM controls the summed-up limits.

The following are historical example limits; inspect the configured workload rules and release documentation rather than assuming these defaults:

  • Maximum 2 CPU seconds per node
  • Maximum 200 megabytes per node

For the overall limits, we need to multiply these numbers by the total number of nodes.

Although it must be done cautiously due to the potentially significant impact on the system’s performance, it is possible to modify these limits.

The limits per node only affect the node which exceeds the limit. That is, only on specific nodes may the AMP worker tasks of a query be moved from the tactical tier to one with lower priority. This will happen if the query has high skew and one or a few AMPs have most of the load.

Conversely, universal limits are enforced across all nodes. If the aggregate limit, such as the number of CPU seconds, is surpassed, the workload will be given lower priority on all nodes.

Tuning with Teradata Workload Exceptions

A change from WDID to FinalWDID indicates workload movement, not by itself a specific tactical exception. Correlate the tactical exception fields and workload-management logs with the configured rules.

Review logged workload movement alongside run times. For per-step AMP activity use appropriately enabled step-level DBQL data or Viewpoint; DBQLogTbl request-level totals do not supply a per-step AMP breakdown.

Example: Join Indexes for a Web Front End

In one project, a web tool on Teradata generated mostly full table scans because its tactical nature was not recognised during development, and workload management did not classify its queries as tactical. The tables were small, so table size was not the problem; competing production workload was.

Secondary indexes did not help: because the tables were small, the optimizer judged a full table scan of the base table cheaper than the indexed path. What worked was a set of single-table join indexes, one per access path the tool needed, each containing the base table’s rows and differing only in its primary index. Each lookup could then be served by primary index access on the matching join index.

-- illustrative; one join index per access path
CREATE JOIN INDEX Customer_JI_Email AS
SELECT CustomerId, Email, CustomerName
FROM Customer
PRIMARY INDEX (Email);

COLLECT STATISTICS COLUMN (Email) ON Customer_JI_Email;

Collect statistics on the join indexes so the optimizer can judge when to use them, and check in the EXPLAIN that it does.

Join indexes have a cost. Every insert, update and delete on the base table must also maintain each join index. In that project the maintenance overhead was acceptable because the tables were small, but the many join indexes led to frequent deadlocks. Two ways to address this:

  • Serialize conflicting session activity through a dedicated serialization table: before modifying data, each session first acquires a write lock on it. This removes the conflicts at the cost of concurrency.
  • Rework the critical code paths and reduce how often the tables are modified. This was the approach taken in that project.

Final Considerations

Coordinate physical tuning and workload rules with locking requirements. LOCKING FOR ACCESS may reduce some read blocking but permits dirty or inconsistent reads and does not remove every lock conflict. Use it only when the application accepts those semantics.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

1 thought on “Teradata Tactical Workload”

  1. Nice article, thanks. The “special trick” was interesting! We use dedicated database for web-service client/purpose. A stored proc runs periodically and re-structures source data to fit the definition of table/index designed as needed. The single/few AMP-query is packaged in a macro with input parameters. This to restrict the client (allow only execute macro). A dedicated TD UserName is created for the purpose.
    Tip: You should pay attention to the workload setting, the initial priority given to the query/user, and the time allowed before given reduced priority.
    You should set requirements on the client-side environment. Let’s say it is a MS web server component using the Teradata .NET Data Provider with default settings, connection pooling is on, and it runs on a clustered 4 node web server. The default max pool size is 100 (seen on version 16). From all 4 nodes on a very busy day, that could result in 400 sessions from one tactical client. Say you support several such client services on one Teradata system, and do the math. Then ask your TD DBA how many sessions and AWTs are available at hot hours. Probably you would require the client to set a relatively short query response timeout value depending on your use-case (range around 6 to 20 sec maybe?), connection pooling is beneficial, but restrict the size depending on your situation: A max pool size of 5 for a TD Data Provider that runs on 4 nodes means you allow 20 sessions. Go tactical! Best regards Arent

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam