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 Hour AMPCPU Spool I/O
2017-06-12 09 0.02 18432 373
2017-06-12 10 0.43 18432 1784
2017-06-12 11 1.12 134617088 3519
2017-06-12 12 2.85 134617088 3543
2017-06-12 13 0.15 617472 1083
2017-06-12 14 0.14 773120 5221
2017-06-12 15 0.14 656896 1149
2017-06-12 16 0.4 89576448 4252
2017-06-12 17 1.85 204800 33051
2017-06-12 18 0.04 617472 1066

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 Hour AMPCPU Spool I/O
2017-06-12 09 4.04 7709696 122626
2017-06-12 10 4.4 7692288 122957
2017-06-12 11 10.67 858554880 142473
2017-06-12 12 22.32 1962375168 190284
2017-06-12 13 3.54 8031744 91908
2017-06-12 14 2.36 6888448 71100
2017-06-12 15 3.6 7745024 102353
2017-06-12 16 5.68 406583808 111934
2017-06-12 17 4.88 5433344 115486
2017-06-12 18 3.17 16286720 95226

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

One Reply to “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 *