DWHPro Insights

Optimizing Teradata System Performance: Identify and Improve Resource-Consuming Queries

By Roland Wenzlofsky ·

Optimizing Teradata System Performance: Identify and Improve Resource-Consuming Queries

This query analyzes DBQL query records, not individual execution steps. It requires populated logging data. HASHAMP()+1 is only appropriate when it matches the participating-AMP scope; adapt that input for maps and restricted-AMP work. Ties in Wasted_CPU need a stable query identifier for reproducible ranks. Zero total waste returns NULL rather than a percentage.

Improve the Performance of your Teradata System

Executing the following query may reveal that a small number of queries are responsible for consuming a significant portion of the resources in the Teradata System. Improving the performance of these queries can greatly impact the system’s overall health.

Your Teradata System’s parallel efficiency is crucial to its overall health. By identifying queries that consume excessive resources due to poor parallel efficiency, we can enhance system performance within days.

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

The following query provides information for the current date:

  • How many logged query records account for 5% of this calculated imbalance measure.
  • How many logged query records account for 10% of this calculated imbalance measure.
  • How many logged query records account for 25% of this calculated imbalance measure.
  • How many logged query records account for 50% of this calculated imbalance measure.
  • How many logged query records account for 90% of this calculated imbalance measure.
  • The number of actions included in the analysis

The test site yielded an unexpected outcome: 1, 1, 1, 1, 13, 1414.

In that historical sample, 13 logged queries accounted for 90% of the calculated imbalance. This is a prioritization heuristic, not a guarantee that another system will have the same distribution or recover that amount of CPU.

Once you have an overview, you may delve into specific details using the DBC.DBQLOGTBL. Useful metrics you can reuse from the following query include CPU_IMPACT and WASTED_CPU.

select 'NUMBER_OF_QUERIES' (TITLE ''),
  min(l5) "TOP5 Percent",
  min(l10) "TOP10 Percent",
  min(l25) "TOP25 Percent",
  min(l50) "TOP50 Percent",
  min(l90) "TOP90 Percent",
  max(rnk) "Queries"
from
(
  select
  case when x < 5.00 then null else rnk end l5
,case when x < 10.00 then null else rnk end l10
,case when x < 25.00 then null else rnk end l25
,case when x < 50.00 then null else rnk end l50
,case when x < 90.00 then null else rnk end l90
,rnk
, Total_CPU
, CPU_IMPACT
from
(
select
 sum(Wasted_CPU) over () TOTAL_WASTE
, sum(Wasted_CPU) over (order by Wasted_CPU desc, StartTime, ProcID, QueryID rows unbounded preceding) TOTAL_WASTE_OLAP
, TOTAL_WASTE_OLAP *100.00 / NULLIFZERO(TOTAL_WASTE) x
, sum(1) over (order by Wasted_CPU desc, StartTime, ProcID, QueryID rows unbounded preceding) rnk
, Total_CPU
, CPU_IMPACT
from
(
select
 ProcID, QueryID, StartTime, AMPCPUTime Total_CPU
, (MaxAMPCPUTime * (hashamp () + 1)) CPU_IMPACT
, CPU_IMPACT - Total_CPU Wasted_CPU
from dbc.dbqlogtbl where
starttime(date) = date
and ampcputime>0
) x
) y
) z
group by 1;

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.

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