Teradata Architecture Overview

Shared-Nothing MPP Architecture

Teradata distributes data across AMPs (Access Module Processors), each handling a subset of data independently. Efficient query execution depends on uniform distribution and minimizing inter-AMP communication.

Key Components

  • Parsing Engine (PE): Parses and optimizes SQL
  • BYNET: Interconnects PE and AMPs
  • AMPs: Perform data storage, retrieval, joins
  • DBQL and TASM: Monitoring and workload control

Critical Troubleshooting Areas

1. AMP Skew

Skew occurs when one AMP processes significantly more rows than others, leading to bottlenecks and poor performance.

SELECT ampnumber, COUNT(*)
FROM dbc.tableStatsV
GROUP BY 1;

2. Skewed Joins

Joining on non-unique or poorly distributed columns can cause severe skew during redistribution steps.

EXPLAIN SELECT a.*, b.*
FROM big_table a
JOIN dim_table b ON a.state_code = b.state_code;

3. Excessive Spool Space Usage

Queries using large intermediate result sets may exceed spool limits and fail with error 2646 (no more spool space).

4. Locking Conflicts

Concurrent DML operations can result in 2631 or 2632 locking errors. Poor transaction boundaries or explicit LOCK commands often exacerbate this.

Diagnostics and Tools

Using Teradata Viewpoint

Viewpoint provides visualizations for:

  • Real-time query monitoring
  • CPU and IO usage by user/session
  • Skewed steps and spool analysis

Query Logging with DBQL

Enable DBQL for problematic users or workloads:

BEGIN QUERY LOGGING WITH STEPINFO ON user_name;

Query the logs:

SELECT * FROM dbc.qrylogv
WHERE username = 'user_name'
ORDER BY starttime DESC;

Explain and Visual Explain

Always analyze the EXPLAIN plan to identify:

  • Product joins (high cost)
  • Redistribution steps
  • Duplicated rows

Common Pitfalls

1. Bad Primary Index Selection

Default or non-unique primary indexes can lead to AMP skew and poor join performance. Always align PI with join/access patterns.

2. Underutilized Statistics

Missing or outdated stats cause the optimizer to make incorrect assumptions about cardinality and join order.

COLLECT STATISTICS ON sales(column_name);

3. Ineffective Workload Management

Improperly defined TASM rules can starve critical queries or allow ad-hoc users to consume excessive resources.

Recommended Fixes

1. Rebalance Data Distribution

Change primary indexes or use hash functions to distribute data more evenly across AMPs.

2. Optimize Join Strategies

Use multistage joins, derived tables, or pre-aggregated data to reduce redistribution. Use PI alignment for co-located joins.

3. Tune Spool Usage

Rewrite queries to avoid unnecessary aggregation, large Cartesian joins, or DISTINCT operations.

4. Lock Management

Ensure transactions commit quickly. Use LOCKING ROW FOR ACCESS or LOCKING TABLE FOR READ to reduce contention for read-only operations.

Best Practices

  • Regularly collect stats on high-usage tables
  • Review query plans before promoting to production
  • Segment workloads using TASM and classify by SLAs
  • Avoid product joins unless absolutely necessary
  • Monitor skew metrics continuously using Viewpoint or custom scripts

Conclusion

Teradata's performance and scalability shine when properly tuned, but without active monitoring and optimization, even well-designed systems can encounter serious degradation. By focusing on primary index strategy, statistics maintenance, join behavior, and workload management, enterprises can prevent performance cliffs and maintain consistent response times across their analytics landscape. Advanced diagnostics like DBQL and Viewpoint provide the necessary visibility to stay ahead of these challenges.

FAQs

1. What is an AMP and why does skew matter?

AMPs are Teradata's processing units. Skewed workloads on AMPs create imbalance and delays, as all AMPs must finish for a query to return.

2. How can I detect if a query is using a product join?

Use the EXPLAIN plan. If the plan shows a product join, it typically indicates no join condition was found or stats are missing.

3. What causes spool space errors?

Large intermediate result sets or poor query design. Also happens when user or system spool limits are too low for the operation.

4. When should I collect statistics?

After bulk loads, schema changes, or when the optimizer starts generating suboptimal plans. Frequent collection on critical columns is recommended.

5. How can I improve performance for concurrent users?

Implement TASM rules to allocate resources fairly, segment workloads, and prioritize time-sensitive queries over ad-hoc traffic.