SQL Server Management Studio Standard Reports – Top Transactions by Locks Count: [DatabaseName]

I'm continuing my series on the Standard Reports in SQL Server Management Studio, and today we’re in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears, click the Standard Reports, and then select the title at the top of this post.

This report shows information similar to the Locking and Blocking reports I’ve covered in the other posts in this series, but since this is a database-level report, it only focuses on one database at a time, and it gives you more lock information than some of the server-level reports.

The first band groups the blocking information by the Transaction ID, which is a unique number assigned to each unit of work the server is doing. You can use this number to locate lots of other information in many of the Dynamic Management Views or other Meta-Data views:

Column Description
Transaction ID A unique number assigned to every transaction – you can use this to join on other meta-data
Transaction Name The name of the transaction assigned by the developer, otherwise the first part of the first statement
State Whether the statement is active or not
Transaction Type The type of transaction – Read only, read/write, or a system transaction
Start Time The time this transaction started
First Update Time The time this transaction was first updated
Isolation Level The Transaction Isolation Level, if known. You can find out more about those here
# Locks The number of locks this transaction is taking
Session ID The unique ID of the session, which you can link to other meta-data views
Login Name The Windows or SQL Server account associated with this transaction

The second band starts a grouping for the Resource Name. This name shows the table or other database object that is involved in the transaction.

Below each resource being locked, you’ll see the following columns:

Column Description
Lock Type This is the type of lock that is being taken, such as PAGE and so on. You can read more about these lock types here
Request Mode Whenever the server needs to take a lock, it asks for it in a certain way. This is called the “Request Mode”, and you can read about those here
# Locks Granted How many locks are granted on this resource name within this transaction
# Locks Waiting The number of locks still needed by the transaction but not yet granted

The last band shows the partial contents of the Currently Executing SQL Statement.

Skip to main content