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.
CAST(TB1.STARTTIME AS VARCHAR(13)) AS COL1, MAX(TB1.AMPCPUTIME),MAX(TB1.SPOOLUSAGE),MAX(TB1.TOTALIOCOUNT)
TB1.QUERYID = TB2.QUERYID
TB1.PROCID = TB2.PROCID
TB1.STARTTIME BETWEEN ‘2017-06-12 00:00:00’ AND ‘2017-06-14 00:00:00’
GROUP BY 1
ORDER BY 1;
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.
How do you calculate hourly consumption of Teradata resources ? Will this query work for you ? Share your feedback with us.