I am trying to troubleshoot a blocking issue which happens for less than a second. The OLTP application is very sensitive and has to have an response time of less than 200ms for some transactions as per the agreed SLA. We had some lock escalation issues with the new code release which we were able to solve with reducing the batch size in the updates. Even with the small batch size, we suspect that the new sp is blocking the same rows that the OLTP transactions is updating.
I need to find the session that is getting blocked and the resource its waiting on. As per my understanding "blocked process threshold" can set for a minimum of 1 second and so this will not capture the blocking.
I am experimenting with wait_info and wait_completed x events.
Is there any other way we could track this. Thanks