Longest Running Query

Tools & Utilities
Enthusiast

Longest Running Query

Is there a SQL to find THE SQL's which ran for the longest running time or which have taken most CPU time in the past 1 day.
Any help will be appreciated.

Thanks
AC
4 REPLIES
Enthusiast

Re: Longest Running Query

if you enable DBQL, you will get most of the info you are looking from the Qry logs views ..
Enthusiast

Re: Longest Running Query

DBQL is enabled.
Can you provide me any SQL query which can help me accurately find this info.

Thanks
Enthusiast

Re: Longest Running Query


There's a query that will you the Top 10 performers on time in the past one day ....

SELECT UserName, QueryText, ElapsedTime, RANK() OVER(ORDER BY ElapsedTime DESC) AS RNK
FROM DBC.QRYLOG
WHERE STARTTIME(DATE) >= CURRENT_DATE-1
QUALIFY RNK <= 10
;

And this one for the CPU crunchers .....

SELECT UserName, QueryText, TotalCPUTime, RANK() OVER(ORDER BY TotalCPUTime DESC) AS RNK
FROM DBC.QRYLOG
WHERE STARTTIME(DATE) >= CURRENT_DATE-1
QUALIFY RNK <= 10
;

There are lots of other columns in DBC.QryLog to play around with depending up on what you are looking for ...

You might have to join with the DBC.QryLogSQL (on query id and proc id) depending on whether DBQL was enabled to log no SQL text on QryLog and SQLs are logged on QryLogSQL.

That should get you started ...

Enthusiast

Re: Longest Running Query

Thanks a lot for your help!