Calculating Query Block time from DBQL


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 ).
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:

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 ;