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

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.

The following query provides information for the current date:

• How many steps cause 5% of the parallel inefficiency
• How many steps cause 10% of the parallel inefficiency
• How many steps cause 25% of the parallel inefficiency
• How many steps cause 50% of the parallel inefficiency
• How many steps cause 90% of the parallel inefficiency
• The number of actions included in the analysis

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

13 queries caused 90% of the parallel inefficiency. Improving these queries could significantly enhance the system’s performance. Your system’s situation is likely to be comparable.

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(l75) “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 (order by Wasted_CPU desc ) TOTAL_WASTE
, sum(Wasted_CPU) over (order by Wasted_CPU desc rows unbounded preceding) TOTAL_WASTE_OLAP
, TOTAL_WASTE_OLAP *100.00 / TOTAL_WASTE x
, sum(1) over (order by Wasted_CPU desc rows unbounded preceding) rnk
, Total_CPU
, CPU_IMPACT
from
(
select
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;