I'm continuing my series on the Standard Reports in SQL Server Management Studio, and today we’re in the Security reports. You can find these reports by starting SQL Server Management Studio and right-clicking the Logins node underneath the Security node. From the menu that appears, click the Standard Reports, and then select the title at the top of this post. This report shows information about locks taken by logons, and it’s similar to many of the locking reports that I’ve covered in other blogs.
The first section of the report is a graph showing the waiting transactions versus the locks on objects. You can use this graph as a “red flag” to check the locking activity.
The second section of this report shows Locking statistics per each logon. It’s the detail to the graph above:
|Login No.||A unique number used to track each Login|
|Login Name||The Login name for the principal generating the locks|
|# Locks (Granted)||The number of locks this Login is taking|
|# Waiting Transactions||The number of transactions this Login has waiting on the locks|
|Locked Object ID||The unique number used to track each object|
|Locked Resource Name||The name of The object being locked|
|# Locks (Granted)||The number of locks granted on this object, for this Login|
|Lock Type||The type of lock on the object. More into on that here|
|# Locks (Granted)||The number of locks on this object, for this Login, which has been requested and granted|
|Lock Owner Type||The type of the owner requesting the locks. More info on those here|
|Locking Mode||The lock mode on this object, for this Login. More info on that here|
|Locking Status||The status of the lock request. More on those statuses here|
|# Locks (All)||The total number of locks on this object|