View vw_BLOCKING_CHAINS is reporting wrong blocking duration #476
Description
The view vw_BLOCKING_CHAINS is reporting wrong blocking durations. We did some tests and simulated a lock of 5 minutes:
- Opened an explicit tran, updated a table and added a waitfor delay of 5 minutes before committing
- Opened a second session and executed a select on the table to generate a blocking
- After 5 minutes, when the lock was released, we stopped Log Scout and Loaded data into Nexus
Because of performance issues in the lab, perfstats has only 2 runtimes as we can see below in the table tbl_HEADBLOCKERSUMMARY.
This is what the view is doing to calculate the blocking duration
CASE WHEN DATEDIFF(s, blocking_start, blocking_end) >= 20 THEN DATEDIFF(s, blocking_start, blocking_end) ELSE max_wait_duration_ms / 1000
Going back to the table tbl_HEADBLOCKERSUMMARY. As we can see, the total wait duration in the second runtime is 271316 milliseconds (271 seconds) and this should be the time of the blocking duration, not the 151 seconds we are currently reporting. Maybe we could use the column max_total_wait_duration_ms from the view vw_BLOCKING_CHAINS instead.
select * from vw_BLOCKING_CHAINS
