How to get SQL Query Stats in Teradata

To understand SQL query performance in Teradata, various parameters come into play. I believe AMPCPUTime, TotalIOCount, and SpoolUsage are the primary indicators to assess SQL query performance.

When executing multiple queries sequentially in Teradata, it is not always accurate to assume that the query taking the longest time is the worst. However, utilizing three specific parameters makes it possible to determine the worst query in most cases. This information can be obtained from two tables in DBC: DBQLOGTBL and DBQLSQLTBL. To obtain SQL Query Stats, use the following query:

SEL
TB1.queryband,TB1.NumResultRows,TB1.NumSteps,TB1.TotalIOCount,
TB1.AMPCPUTime,TB1.ParserCPUTime,TB1.NumOfActiveAMPs,TB1.MaxCPUAmpNumber,
TB1.MinAmpIO,TB1.MAxAmPIO,TB1.MaxIOAmpNumber,TB1.SpoolUsage,
SUBSTR(TB2.SqlTextInfo,1,1000) AS SqlTextInfo
FROM
DBC.DBQLOGTBL TB1
INNER JOIN
DBC.DBQLSQLTBL TB2
ON
TB1.QueryID = TB2.QueryID
AND
TB1.ProcID = TB2.ProcID
AND
TB1.SessionID=12345;

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. Early subscribers keep launch access before the paid plan launches.

Get the next issue

The columns can be adjusted according to your needs. However, the ones listed above are crucial in determining query performance in Teradata. If the AMPCPUTIME is high, it is necessary to fine-tune your query for optimal performance.

Get the next issue by email.

Consider these three factors when executing the query above:

SQL query results may not be immediately visible due to a brief delay in transferring query information to DBQL tables.

The query above may take a significant amount of time to produce results due to inadequate indexing on both tables. Upon inspecting both tables' primary index columns, we note that the ProcID and CollectTimeStamp are identical. However, the CollectTimeStamp value may differ for the same query in either table, making joining by the second column unwise. Consequently, leveraging the primary index is not possible, resulting in an extended query time.

To obtain the SessionID, execute the SEL SESSION command within the session where you execute your queries.

Avoid labeling the query with the longest execution time as the worst. Inspect the query DBQL statistics to personally identify the query with the poorest performance.

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 before the paid plan launches 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.