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 thought on “Calculate hourly resource consumption using DBQL in Teradata”
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