How to diagnose blocking issues using blocked process report?
During these times we see a massive spike in response times from the database that holds ASP session state. In an effort to to diagnose the issue I have used SP_CONFIGURE’blocked process threshold’,15 and ran a trace to catch any events that are fired. During the next incident the trace capture some blocking events in the Session State database.
Why is the BLOCKED process waiting on a key?
The blocked process is waiting on a KEY (therefore on a row), for 20631 ms (~20s). The blocking transaction is still executing a statement, likely the UPDATE that causes the blocking. It is not blocked, if it would be blocked then it wouldn’t be the tip of the wait chain.
Which is blocked by the process ID 73?
In turn, the process = 73 is blocking other sessions (with id = 75, 77). Furthermore, the process 74 is at the same level than the process id = 73 and it is blocked by the process id = 72.
Which is the process that is blocking a session?
Let’s begin by the first category. You can see here the hierarchy tree and the blocked interactions that exist between the different processes. The above picture shows the process id = 72 that is blocking the process id = 73. In turn, the process = 73 is blocking other sessions (with id = 75, 77).
How to know if a process was blocked in SQL?
The amount of time (in microseconds) that the process was blocked. Time at which the event ended. This column is not populated for starting event classes, such as SQL:BatchStarting or SP:Starting. Type of event = 137. The sequence of a given event within the request. ID for the index on the object affected by the event.
What does it mean when a transaction is not blocked?
The blocking transaction is still executing a statement, likely the UPDATE that causes the blocking. It is not blocked, if it would be blocked then it wouldn’t be the tip of the wait chain. Is executing, right now is suspended (not blocked!) meaning it just yielded CPU.