I’m continuing a series on the Standard Reports you can find in SQL Server Management Studio (SSMS).
We’re now up to the “Activity” reports, which show more information than you’ll find in SQL Server Management Studio in the “Activity Monitor” node. That view is more immediate; these reports show more detail. This particular report shows a lot of information about transaction blocking. If you’re not familiar with transactions, look here for more information. If you’re not familiar with locking in SQL Server, look here for more information.
This report will only show something if you have blocking transactions. You’ll have multiple descendig grids within a “parent” one if you have any blocks. Here are the columns you’ll see in each:
|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|
|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 format of this gried layout is that it shows the blocking session at the top, with all of the statements it blocks stepped down below it. The columns are the same for each.
To find this report, open SSMS, right-click a Server, and then select “Reports” and then “Standard Reports”.This report takes a few moments to render.