Wednesday, 16 February 2022

After saving Trace file to table , query which will help what query is taking what time and easy to find query taking longest time to run/return result

 SELECT MAX(ObjectName),

    ApplicationName,
    SPID,
    EventClass,
    MIN(RowNumber),
    MAX(RowNumber),
    Min(StartTime),
    MAX(EndTime),
    Convert(VARCHAR(max),TextData) TextData,
    COUNT(1) Count,
    SUM(Duration)/1000000.0 Sum,
    avg(Duration)/1000000.0 Avg,
    min(Duration)/1000000.0 Min,
    max(Duration)/1000000.0 Max,
    AVG(Reads) Reads,     AVG(RowCounts)   RowCounts
FROM Related  WITH (NOLOCK)
WHERE 1=1
AND EventClass <> 15
-- AND Duration > 1000
GROUP BY Convert(VARCHAR(max),TextData),EventClass, SPID, ApplicationName
 --ORDER BY AVG(RowCounts) DESC
ORDER BY SUM(Duration) desc