Top Transactions by Blocked Transactions Count

Top Transactions by Blocked Transactions Count

Rate This
  • Comments 1
I'm continuing my series on the Standard Reports in SQL Server Management Studio, and today I'm covering a report that shows the transactions on an instance that block others. I’ve described some of this blocking activity here, but this report orders them differently.

You get three main bands. The outer one is arranged by the Transaction ID:

TransactionID The ID of the Transaction that is blocking other transactions
# Directly Blocked Transactions The number of statements this session is blocking
# Indirectly Blocked Transactions The number of downstream transactions this one causes to block others
Transaction Name Name of the transaction, if set. Otherwise, this is set to "user_transaction"
State Whether this transaction is active or not.
Transaction Type Whether this transaction is a complete statement or a fragment of another.
Start Time The time this transaction started blocking another.
Resource Type The type of lock taken. Values can be: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, or ALLOCATION_UNIT.
Session ID The session ID of the transaction - this is the number that uniquely identifies the statement that also shows up when you do an sp_who2 statement.
Blocking SQL Statement The portion of the transaction where the blocking occurs. This doesn't always indicate the exact offending statement.

The next band shows the statement that is being blocked. It starts off with the Blocking SQL Statement:

Blocking SQL Statement The portion of the transaction where the blocking occurs. This doesn't always indicate the exact offending statement.
Direct/Indirect Shows whether the lock is directly blocking another transaction or is part of a blocking chain.
Blocked Transaction ID The Transaction ID that is blocked.
Blocked Transaction Name Shows the name of the transaction that is blocked, if set. Otherwise, this is set to "user_transaction"
State Whether this transaction is active or not .
Transaction Type Whether this transaction is a complete statement or a fragment of another.
Start Time The time this transaction started blocking another.
Resource Type The type of lock taken.
Session ID The session ID of the transaction - this is the number that uniquely identifies the statement that also shows up when you do an sp_who2 statement.
Blocked SQL Statement The portion of the transaction where the blocking occurs. This doesn't always indicate the exact offending statement.

The last band just shows the Blocked SQL Statement – but that may perhaps the most important piece of the puzzle. That’s often where you’ll get your first indication that something is wrong.

Leave a Comment
  • Please add 3 and 1 and type the answer here:
  • Post
  • I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today I'm

Page 1 of 1 (1 items)