Calculate hourly resource consumption using DBQL in Teradata

Recently Raghav reached out to us on our Facebook page asking for a SQL query in Teradata to identify the highest AMPCPU, I/O and SPOOL usage for each hour during a given time period. He was actually working on some activity to find out the consumption of resources on Teradata box on hourly basis. The purpose was to find out time range when Teradata is most heavily loaded and when it is relatively free. He was looking for information in hourly manner for any given day or multiple days. Also he was referring to our post to check SQL Query Performance in Teradata however the Query could not be used directly to give desired result. Hence required some modifications. I shared with him the below mentioned query and he was happy with the output as it was exactly what he was looking out for.

SELECT
CAST(TB1.STARTTIME AS VARCHAR(13)) AS COL1, MAX(TB1.AMPCPUTIME),MAX(TB1.SPOOLUSAGE),MAX(TB1.TOTALIOCOUNT)
FROM
DBC.DBQLOGTBL TB1
INNER JOIN
DBC.DBQLSQLTBL TB2
ON
TB1.QUERYID = TB2.QUERYID
AND
TB1.PROCID = TB2.PROCID
AND
TB1.STARTTIME BETWEEN '2017-06-12 00:00:00' AND '2017-06-14 00:00:00'
GROUP BY 1
ORDER BY 1;

Sample Output:

Date HourAMPCPUSpoolI/O
2017-06-12 090.0218432373
2017-06-12 100.43184321784
2017-06-12 111.121346170883519
2017-06-12 122.851346170883543
2017-06-12 130.156174721083
2017-06-12 140.147731205221
2017-06-12 150.146568961149
2017-06-12 160.4895764484252
2017-06-12 171.8520480033051
2017-06-12 180.046174721066

I thought of sharing this with other readers too in case they also come across similar requirement then they can use this query. Also his requirement was basically to find out the highest AMPCPU, SPOOL and I/O consumption however if you want to see the actual resource consumption then just replace the MAX function with SUM function in above query. This should give you total AMPCPU,SPOOL and I/O consumed on hourly basis.

Sample Output:

Date HourAMPCPUSpoolI/O
2017-06-12 094.047709696122626
2017-06-12 104.47692288122957
2017-06-12 1110.67858554880142473
2017-06-12 1222.321962375168190284
2017-06-12 133.54803174491908
2017-06-12 142.36688844871100
2017-06-12 153.67745024102353
2017-06-12 165.68406583808111934
2017-06-12 174.885433344115486
2017-06-12 183.171628672095226

How do you calculate hourly consumption of Teradata resources ? Will this query work for you ? Share your feedback with us.

One thought on “Calculate hourly resource consumption using DBQL in Teradata

  1. Hi Raj,
    SQL is correct but … it just reports informations (CPU, SPOOL, …) related to the hour the query started. That is to say we can collect and sum data anchored to the hour of starttime, we do not calculate consumption hour per hour along the duration of query, when query is over one hour (with a set of huge spools).
    It looks like the only way is to use "resusage" tables (for CPU & IO), where user and query are unknowned.
    Pierre

Leave a Reply

Your email address will not be published. Required fields are marked *