Blocktrees of processes, including blockers and waiters
When a process must wait to acquire a lock, it is a waiter. The other process that already has the lock (and is causing the waiter to wait) is a blocker.
This view contains rows for both the blocker processes and the waiter processes in the database. If the row is a blocker process, then BLOCKERKPID = 0 and BLOCKERSPID = 0. If the row is a waiter process, then the "BLOCKERxx" columns have information about its immediate blocker.
Waiters being blocked by a blocker can themselves also block other waiters, potentially forming a blocktree involving many processes. Thus, a row can be a waiter and a blocker, in some cases (and can involve different locks).
| Name | Description |
|---|---|
| Inst ID | Instance ID; 0 = non-CE |
| SPID | Session process ID |
| Blocker SPID | Blocker session process ID (SPID); 0 = the row is a blocker process |
| Time Blocked | Duration of time blocked |
| Login | Login name |
| Program | Application name |
| Blocked Count | Number of processes being blocked, if the row is a blocker process |
| Root Blocked Count | Total number of processes being blocked in the entire blocktree involving this row |
| Host | Host name |
| IP Address | Client IP address |
| Client OS PID | Client OS PID |
| Set Client Name | Client name |
| Set Client Application | Client application name |
| Set Client Host | Client host name |
| KPID | Kernel process ID |
| Name | Description |
|---|---|
| ID | ID of this node in the plan |
| Parent ID | ID of parent of this node in the plan; 0 = this node is the root node in the plan |
| Operation | Name of operation for this node |
| Options | Options for the operation for this node |
| Object | Target object of this operation |
| Object2 | Second target object of this operation |
| IO Size Data Pages | I/O size used for data pages for this node |
| BRS Data Pages | Buffer Replacement Strategy used for data pages for this node |
| IO Size Index Leaf Pages | I/O size used for index leaf pages for this node |
| BRS Index Leaves | Buffer Replacement Strategy used for index leaf pages for this node |
| Name | Description |
|---|---|
| Inst ID | Instance ID; 0 = non-CE |
| SPID | Session process ID |
| Context ID | Current context ID of the procedure |
| Stmt # | Current statement number (unique in a batch) of the procedure |
| Type | Type of procedure (stored procedure, etc.) in the stack |
| Object | Name of the procedure in the stack |
| Line # | Current line number of the procedure |
| Compile Time | Compile date of the procedure |
| Owner | Name of the owner of the procedure |
| Database | Name of the database that contains the procedure |
| Memory | Memory usage |
| Attributes: | MDA |
|---|---|
| Minimum database version: | 15.5 |