DWHPro Insights

SQL Tuning Goals: Improving Performance and Reducing Resource Usage

By Roland Wenzlofsky ·

SQL Tuning Goals: Improving Performance and Reducing Resource Usage

What are the Goals of SQL Tuning?

Resource consumption must be minimized to optimize a database system, and three crucial metrics to consider are skew, IOs, and CPU seconds. Our experience has shown that Teradata faces significant challenges with IOs and skew. Transferring rows from mass storage to main storage is a time-intensive process, as Teradata can only transfer data blocks containing a designated number of rows instead of individual rows. Thus, data blocks must be stored in the main memory to locate the required rows.

Systems heavily reliant on the CPU may face challenges due to high CPU usage. However, I have rarely encountered this limitation in my experience.

Measure latency and throughput together with CPU, I/O, spool and skew under comparable load. Resource use can vary with caching, concurrency and runtime conditions even with the same plan; it is not invariant.

Where I/O and Skew Costs Come From

Teradata stores table rows in data blocks, and every read, even of a single row, copies at least one whole block from disk into memory. Each statement type has a typical I/O pattern:

  • SELECT: read the blocks that contain the required rows.
  • UPDATE: read the blocks, apply the changes in memory, write the changed blocks back.
  • INSERT: read the blocks that will hold the new rows, add the rows in memory, write the blocks back.
  • DELETE: read the blocks that contain the rows, remove the rows in memory, write the blocks back.

When blocks grow or shrink to fit their rows, this can cost additional I/O. Joins add more, because rows may have to be redistributed or sorted. Fewer block transfers is therefore the first tuning goal.

Not every logical I/O is a physical read: a block that is already cached in memory avoids the disk access. Logical I/O counts block operations whether or not they were served from cache, so treat it as a measure of the work a query asks for, not of disk reads.

Parallelism is the second goal. Each AMP has its own memory and storage and works on its own rows. An all-AMP step is finished only when the last AMP is finished, so if one AMP holds or receives far more rows than the others, the whole step waits for it. Even distribution of rows across the participating AMPs, during every step and not only in the base tables, is what keeps the parallel units busy for the same amount of time.

1. Ensure Completeness and Correctness of Teradata Statistics

To optimize Teradata, accurate and comprehensive statistics must be provided to support its optimizer.

We must meticulously evaluate three fundamental situations and consistently collect extensive data.

  1. Non-indexed columns used in predicates:

    Missing statistics force the Optimizer to do a wildly inaccurate heuristic estimation.
  2. Skewed indexed columns:

    Random AMP sampling on skewed index results in wrong estimates and execution plans.
  3. Small tables:

    Estimations for small tables are unreliable when there are fewer table rows than AMPs. In this case, most AMPs will not contain rows. A dynamic-AMP sample taken from an AMP without a few rows will give a wrong estimation.

Adhering to these three guidelines can resolve numerous issues about absent data.

We optimize Teradata SQL performance by efficiently balancing query plans and acquiring statistical data.

Discovering Missing Statistics

Enable diagnostics to uncover missing statistics.

DIAGNOSTIC HELPSTATS ON FOR SESSION;

Each statement includes a statistically-supported recommendation.

EXPLAIN
SELECT * FROM TheTable WHERE TheColumn = 'value';

We must individually assess each recommendation and only adopt ones that improve query efficiency. We do not integrate all proposals at once.

No confidence indicates limited estimation evidence, not proof of a bad plan. Compare estimates and actual row counts; neither stale-statistics detection nor collected statistics guarantees accuracy.

Detection of Stale Statistics

Teradata identified table expansion in version 14.10 by contrasting two arbitrary AMP samples – one gathered during statistical analysis and the other during inquiry execution.

Teradata’s Release 14.10 enables UDI count tracking for deleted and inserted rows. As a result, the Optimizer gives UDI counts higher priority than random AMP sample comparison.

Frequently collecting statistics is the most effective way to ensure up-to-date statistics.

We must compare our estimates to the row count to determine which tables require statistics updates. This data can be utilized in two manners:

  • The Execution Plan (by placing the EXPLAIN modifier in front of our query)
  • The Statistics Histogram (by executing the SHOW STATISTICS VALUES ON <TABLE> statement)

Histograms inform the Optimizer and aid in the creation of the execution plan.

  • The timestamp of the last statistics collection
  • Collected & Random-AMP Sampling  row counts
  • Inserted and deleted rows since the last statistics collection
  • Maximum column values
  • Minimum column values
  • Biased column values
  • Break up the SQL into smaller pieces (using volatile tables)
  • Get rid of joins on expressions

Why did I say “almost the same information”? The statistics improve with every new insight the Optimizer acquires, which can come from prior join steps, individual table predicates, or aggregations.

Different extrapolation techniques can modify the estimates shown in the histograms.

For extrapolated estimates, please refer to the following statement:

SHOW CURRENT STATISTICS VALUES ON <Table>;

This is an example of a single table predicate that uses estimated derivations.

SELECT * FROM <Table1> t01 INNER JOIn <Table2> t02 ON t01.Key = t02.Key WHERE t01.Column IN (1,2,3);

The estimation for the retrieving step of <Table1> yields three distinct values that are used in the joining process. If both tables have an average of one row per value (unique data), the join results in a spool of three rows.

Stale statistics can be detected using a straightforward method:

  • Decompose the query  into single retrieve and join steps
  • Figure out why step estimations and real numbers don’t match
    Statistics could be outdated, or they could be missing. It might even be that the estimation results from how the Optimizer works.
    Here is an example:
SELECT * FROM TheTable WHERE TheColumn = 1;

Value 1 is missing from the <Table>. Therefore, the Optimizer will estimate the result set based on the average number of rows per value. Some of you may expect a result consisting of zero rows.

More information is available here: Teradata Statistics Basics

Finally, my advice:

Keep records of the execution plans before and following the modification to evaluate the impact of the change.

2. The Primary Index Choice

Optimizing Primary Index selection necessitates balancing competing requirements.

Improved distribution and performance of data joining.

AMP-local joins require compatible placement of matching rows, often through compatible PI join keys and maps. A merge join can also use redistributed/sorted spools, so base tables need not share a PI.

Maximize query efficiency by utilizing the Primary Index for joining whenever possible, as it is the most economical method.

Consider utilizing volatile or temporary tables with a distinct primary index to enhance performance. These tables should have the same structure and content as the original ones while having the necessary primary index. Utilizing temporary tables can be beneficial for queries involving numerous join steps.

3. Teradata Indexing & Partitioning

Indexes offer supplementary data access routes to the Optimizer, improving selective retrieval operations. Nevertheless, they take up permanent disk space and require upkeep in case of any modifications made to the underlying base table.

Indexing Recommendations:

It is recommended to promptly remove an index if the Optimizer is not utilizing it and it does not contribute to PDM design, to prevent unnecessary consumption of resources and storage.

A unique secondary index (USI) allows direct access to rows like a primary index. In contrast, a non-unique secondary index (NUSI) requires a full index subtable scan on all AMPs.

Covering indexes can avoid base-row lookup, but index scans, storage, maintenance and spool still have costs.

Indexes are sub-tables, while partitioning is another way to organize the base table. Partitioning allows the Optimizer to limit access to certain data blocks within a partition. The main advantage of partitioning is the regular use of partition elimination. However, using indexes requires specific requirements, such as gathering statistics on the index columns before using NUSI.

Indexing offers a significant advantage as we create and oversee its usage, discarding it if deemed unnecessary. Have you attempted to partition a 200 Terabyte table for experimentation? Presumably, this is not an optimal undertaking.

Another disadvantage of partitioning is worth considering:

Poor performance can occur due to partition mismatches or the absence of partitioning in a table.

When implementing partitioning, it is important to consider the data warehouse architecture and ensure your solution aligns with it. Use unique join techniques for partitioned tables to avoid suboptimal join performance caused by mismatched partitions.

Conclusion: There is no one-size-fits-all solution. It is important to identify the most effective approach for oneself.

4. Query Rewriting

Query performance can often be enhanced through query rewriting. Consider the following suggestions:

  • DISTINCT instead of GROUP BY depending on the number of different values
  • Replace UNION with UNION ALL only when retaining duplicates preserves the required result. Likewise, an outer-to-inner join rewrite must preserve unmatched-row semantics.
  • Splitting a skewed query into two parts. One for handling the skewed values, the other to deal with the rest
  • Adding WHERE conditions to allow for partition elimination
  • Removing unreferenced columns and expressions from the select list (could help to cut joins)
  • Convert an outer join to an inner join only when constraints and predicates make the results equivalent, including NULL and duplicate behavior.

Rewriting queries can enhance performance even when other techniques are unsuccessful.

Rewriting queries is an effective method for enhancing performance, but it typically necessitates comprehension of the business logic (“Is it possible to substitute an inner join for this left join?”).

Rewriting a query technically is feasible. However, comprehending the query’s business logic uncovers additional opportunities for optimization.

5. Physical Data Model Design

I hesitated to include the physical data model in this list due to the limited ability to alter the physical database design. Redesigning would be necessary to accommodate numerous consumers utilizing the database, including reporting tools and data marts.

Improving the physical model remains one of the most impactful changes.

The most valuable advice I can offer:

Normalize your core model and denormalize in your data marts. Although some may disagree, my experience has shown that premature denormalization can lead to poor performance, particularly when no clear justification exists.

Inspect all table columns to determine if the data can be further decomposed. Verify that they share uniform data types and character sets. Avoid columns that contain multiple data components, which may necessitate expression joins. Doing so could result in the Optimizer disregarding statistics and primary index utilization during the join process.

Choose character sets and compression from the required value domain and measured workload. Preserve Unicode characters and business constraints; MVC is not automatically beneficial on every column/table.

Please note that the aforementioned advice is merely a general outline of potential solutions for repairing a defective data model.

6. Make Use of Teradata-Specific Features

Consider several distinctive optimization opportunities.

  • Using MULTISET tables can decrease Disk IOs
  • A multistatement request does not generally bypass the transient journal. Empty-target fast-path INSERT SELECT optimization has specific request and table restrictions.
  • Use CLOB columns instead of VARCHAR if you seldom select the column. Teradata stores CLOBs in sub-tables
  • DELETE instead of DROP tables
  • Use ALTER TABLE instead of INSERT…SELECT into a copy
  • Use MERGE INTO instead of INSERT & UPDATE

7. Real-Time Monitoring

Observing a running query facilitates the identification of critical steps. Real-time monitoring is commonly done using Viewpoint, but I personally prefer using dbmon, a tool created by a Teradata Austria developer, despite Viewpoint’s sluggishness.

SQL performance issues stem from either of the following:

  • Skewed steps
  • Stale and missing statistics fool the Optimizer into creating wrong join decisions: Doing product joins instead of merge joins, duplicating instead of rehashing.

I monitor in real-time using my preferred method.

I wait for a step with skewed input or nonsensical estimated row counts for a join, like when two spools with millions of rows are joined using a product join. My optimization efforts are focused on these types of steps.

I will analyze the skew of the join column value if it is problematic. If estimations are inaccurate, I will review the statistics to ensure their currency.

Fixing statistical problems can be done swiftly, while skewed issues may prove to be more challenging. Query rewriting is typically a last resort unless an obvious and uncomplicated solution is available.

8. Comparison of Resource Usage

Extract appropriate steps from the query log daily using a SQL query. Assign a unique QUERYBAND to each query version to differentiate them. Access to “DBC.DBQLOGTBL” requires “select” privileges.

SET QUERY_BAND = 'Version=1;' FOR SESSION;
SELECT
   AMPCPUTIME,
   (FIRSTRESPTIME-STARTTIME DAY(2) TO SECOND(6)) RUNTIME,
   SPOOLUSAGE/1024**3 AS SPOOL_IN_GB,
   CAST(100-((AMPCPUTIME/(HASHAMP()+1))*100/NULLIFZERO(MAXAMPCPUTIME)) AS INTEGER) AS CPU_SKEW,
   MAXAMPCPUTIME*(HASHAMP()+1) AS CPU_IMPACT,
   AMPCPUTIME*1000/NULLIFZERO(TOTALIOCOUNT) AS LHR
FROM
   DBC.DBQLOGTBL
WHERE
     QUERYBAND = 'Version=1;' AND StartTime >= CURRENT_TIMESTAMP - INTERVAL '1' HOUR

The search results will display:

  • The total CPU Usage
  • The Spool Space needed
  • The LHR (ratio between CPU and IO usage)
  • The CPU Skew
  • The Skew Impact on the CPU

The objective is to reduce CPU utilization, spool capacity, and imbalance.

9. Tactical Workload

A tactical workload demands a specific skill set, without which even the best tuner will falter. I have witnessed entire projects flounder due to developers’ disregard for this crucial aspect.

The skill set needed for strategic tasks is distinctive.

I highly recommend reading this comprehensive post that explains all the details.

The DBQL formula assumes HASHAMP()+1 is the relevant AMP population; sparse maps and partial-AMP work need the actual participating-AMP scope. The bounded query executed with zero rows, validating syntax but not real workload metrics. Divide by 1024**3 for GiB, not decimal GB.

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

Practical data engineering insights, regularly

Join more than 3,500 data professionals: a regular newsletter with practical insights on data engineering, new data platforms (Snowflake, Databricks, Teradata) and AI.

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

3 thoughts on “SQL Tuning Goals: Improving Performance and Reducing Resource Usage”

  1. Collecting statistics on composite columns will help to improve estimations for highly correlated columns.
    Still, multicolumn statistics limit the queries for which the optimizer can use them.
    Since Teradata 14 the optimizer can use the first columns of multicolumn statistics.
    For example, if we have a multicolumn statistics on (a, b) and a where condition the optimizer can use these statistics.
    If the where a condition is on column bit can’t be used.

    Reply

Leave a Comment

Get more practical data engineering advice

Join the DWHPro newsletter for practical data engineering tips, troubleshooting examples and data platform insights.

Protected by Security by CleanTalk and CleanTalk Anti-Spam