Some General Teradata Tuning Ideas
1. One change at a Time
The Teradata system is highly intricate. It may be tempting to modify multiple parameters concurrently to expedite the tuning process, however, refrain from doing so. While you may resolve the issue, you will be unaware of which alteration resolved it. In the event of the problem reoccurring, you will need to re-initiate your inquiries.
2. Keep before and after Explain Plans
Aligning plans with point 1 is crucial. The execution plan is subject to change during tuning activities, particularly when fine-tuning SQL statements. Enhancing the performance of a query without comprehending the root of such improvement is unsatisfactory. Some potential causes include:
- Is the Optimizer choosing a more suitable join type?
- Does the Optimizer have better statistics available?
- Does our change allow the Optimizer to use an alternative access path (secondary index, join index, etc.)
3. Keep Documentation of all Changes
It is crucial to maintain comprehensive documentation of any modifications made. These adjustments will likely need to be implemented in the production system. Providing thorough documentation to the DBA ensures that all changes will be properly applied.
4. Solve the Physical Data Model, and avoid Workarounds
Implementing workarounds may seem expedient, but ultimately investing in a reliable PDM yields greater benefits. Addressing issues at the outset of the ETL/ELT process is advisable to minimize complications.
- Take care of suitable data types in your PDM, and avoid fixing late-stage problems like data marts, reports, etc.
- Avoid adding logic to a view layer only because you forgot to integrate it into the transform process.
- Don’t confuse your data warehouse with an operational data store (ODS); you will force the report designers to repeat business logic repeatedly in the reports.
5. Final test your Changes on the Target System
Various tuning aspects can be tested on any system, but specific details can only be attempted on the target system. The target system may offer unexpected execution plans and bottlenecks, including I/O or CPU limitations.
6. Never use Execution Times alone to estimate your Improvements
When assessing improvements, prioritize absolute CPU time and IOs as key measures. Execution times depend on the system’s overall workload and other unalterable parameters.
7. Don’t miss the Wood for the Trees
Incorrectly attributing poor performance to Teradata is a common mistake. A tardy network connection between Teradata and the reporting server may cause the sluggishness of report generation. In order to identify the root cause, it is imperative to scrutinize all relevant systems, including ETL-Servers (Datastage, ODI, Informatica, Ab Initio), Reporting Servers, Unix Load Nodes, and so on.
