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:
|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:
|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.|