SQL Server Management Studio Standard Reports – Top Transactions by Locks Count

I’m continuing my series on the Standard Reports in SQL Server Management Studio, and today I’m covering a report that shows more about locking activity, by transaction.

Before I begin describing the columns and values in this report, it might make some sense to talk a little about the locking and blocking in the database engine. I’ve covered this in some of the other reports, but I wanted to point out some links that might be useful about locks:

OK – down to the task at hand: This report shows locks by transactions, and the first band groups everything by Transaction ID:

Transaction ID The ID of the Transaction that is locking.
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 is a Full or partial transaction.
Start Time The time this transaction started locking.
Isolation Level There are several types of “isolation levels” for the lock activity. You can read more about those levels here.
# Locks The number of locks in this session.
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.
Login Name The name of the login associated with this session.

The next band groups the information by Database Name:

Database Name The database where the locking is taking place.
Transaction State Shows whether the session is active or not.
First Update Time The time this transaction was first updated.
# Locks The number of locks on this particular database.

Another band below that shows the Resource Name. This is the object being locked. Below that is a row for each type of lock taken:

Lock Type There are several lock types (also called “granularities”), which you can read more about here.
Request Mode The mode of lock being requested. More here.
# Locks Granted The actual number of locks.
# Locks Waiting The number of locks that are waiting for a lock.

The final band shows a part of the Currently Executing SQL Statement. This might be blank if the locks are system-generated.

Skip to main content