查询长事务和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