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.