Calculating Query Block time from DBQL

Database
Enthusiast

Calculating Query Block time from DBQL

SyntaxEditor Code Snippet

For some time now I have tried to document the blocking issues we have on some queries.
First task was to find the Queries delayed by blocking.
The idea was to do ElapsedTime - ExecutionTime to get the real Delaytime ( =Blocking time ).
WRONG! =)
We have queries blocked for hours (easy to see in Viewpoint) but the Blocking period is included in the Execution time because the query gets to parser step 4 before figuring out that the objects needed are actually blocked.
Now the Blocking Time counts for Execution Time.

Therefore the DelayTime i 0 for all queries but in reality many of them were blocked.

Any ideas on how to single out the real Blocked Time from DBQL

I used this code:

SELECT
SessionID,QueryID,CollectTimeStamp, ( ( a.firstresptime - a.starttime) DAY( 4 ) TO SECOND ) AS ElapsedTime, ( ( a.firstresptime - a.firststeptime ) DAY( 4 ) TO SECOND ) AS ExecutionTime,ElapsedTime - ExecutionTime AS DelayTime FROM DBC.DBQLogTbl a WHERE SessionID = 14693851 ;