SQL Server Management Studio Standard Reports – Resource Locking Statistics by Object: [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.

In the top area of the report you’re shown a small graph that displays the number of locks against the number of transactions that are waiting. You can use this information to correlate the blocking chains, and trace back what the transactions are waiting on. In this way you can re-code your applications to ensure that an update doesn’t block a read and so on.

The first band in the report groups the blocking information by the Object Number, which is a unique number within this report:

Column	Description
Object Number	A unique number assigned to every object in this report
Locked Object ID	The Object ID number from the sys.sysobjects table that is being locked
Locked Resource Name	The “friendly name” of the object, such as the table name that is being locked
# Locks (Granted)	How many locks are granted on this object
# Waiting Transactions	The number of transactions waiting on this object
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 Lock type. Lock types indicate the level the database is locking, such as TABLE, OBJECT and so on. You can read more about those lock types here. Just next to the type of lock is the # Locks (Granted). This number shows how many locks (of this type) are currently being used by this Object.

Below each Lock Type, you’ll see the following columns:

Column	Description
Lock Owner Type	Whether this is a transaction owner, or another owner. More about that here.
Locking Mode	When a lock is taken, it can block other kinds of operations or locks. For instance, an “Exclusive” lock even blocks a read operation. You can find out more here.
Locking Status	Locks move through a series of “ramp-up” steps, where they ask the engine for the lock, and then they are given the lock, and then they release it.
# Locks (All)	This field tallies all of the locks for this Transaction Owner Type
Session ID	A unique number that identifies this session. You can use this number to link up other meta-data to locate the user that is running the statement causing the lock.