Recently I have faced a Long running job where user executed two queries INSERT INTO and DELETE FROM in same session and getting very slow response due to some background bottleneck. I just wanted to know if I can get some more details about the session apart from CPU/ IMPACT CPU etc.
SyntaxEditor Code Snippet
SELECT a.logdate, a.CollectTimeStamp,a.SessionID,a.UserName,a.DefaultDatabase as DatabaseName, (a.FirstRespTime - a.StartTime) hour to second(4) as FirstRespElapsedTime,a.starttime,a.TotalIOCount,a.AMPCPUTime+ParserCPUTime TotalCPUTime,b.sqltextinfo FROM pdcrinfo.dbqlogtbl_hst a inner join pdcrinfo.dbqlsqltbl_hst b on a.procid = b.procid and a.queryid = b.queryid and a.logdate = b.logdate where sessionid='xxxxxx' and a.logdate ='yyyy-mm-dd' order by a.starttime;
I have used above query for session level info which is not sufficient.
Did you check dbqlsteptbl for those QueryIds?
All step details will be found there if DBQL step level info is enabled.
"Two queries in same session":
Maybe they were submitted as a MultiStatementRequest (DELETE - INSERT/SELECT) and then both DMLs get journalled compared to individual requests where both will utilize FastPath processing (almost) without Transient Journal?