查询长事务和SQL执行等待间隔时间

   通过SQL Profile跟踪SQL，并将跟踪的sql存入表中，运行下列语句就可以获取长事务的id，在EXCEL中通过TransactionID 筛选就可以得到事务的sql和运行时间。

   

select  TransactionID [事务编号],
        count(*) [SQL条数],
        datediff(second, min(StartTime), max(EndTime)) [事务时间S]
from    yy
where TransactionID is not null
group by TransactionID
order by 3 

 

   SQL执行等待间隔时间

 

 SELECT t.TransactionID 事务ID,DATEDIFF(s,t.EndTime,t2.StartTime) [事务SQL间隔时间S],
 t.StartTime,t.EndTime FROM
 (SELECT TransactionID, DENSE_RANK() OVER (partition BY  TransactionID ORDER BY eventsequence) rn,
 StartTime,EndTime,TextData
 FROM yy 
 WHERE TransactionID IS NOT NULL) t ,
 (SELECT TransactionID, DENSE_RANK() OVER (partition BY  TransactionID ORDER BY eventsequence) rn,
 StartTime,EndTime,TextData
 FROM yy 
 WHERE TransactionID IS NOT NULL) t2 
 WHERE t.TransactionID=t2.TransactionID AND t2.rn=t.rn+1
 ORDER BY 2 desc