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:
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. DWHPro Letters is free. Subscribe to get new issues by email.
- 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_IMPACTfrom(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_IMPACTfrom(select AMPCPUTime Total_CPU, (MaxAMPCPUTime * (hashamp () + 1)) CPU_IMPACT, CPU_IMPACT - Total_CPU Wasted_CPUfrom dbc.dbqlogtbl wherestarttime(date) = dateand ampcputime>0) x) y) zgroup by 1;Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe for free and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.