Monitoring SQL  «Prev  Next»

Lesson 4 Locked resources
Objective Monitor SQL Server locks.

Monitor SQL Server Locks

When a connection accesses data, SQL Server will use a lock[1] to control access. These locks are used by SQL Server to ensure data integrity and to control concurrent access of data. In most cases, SQL Server locks do not present a large problem. But, occasionally, a process may hold a lock and stop other processes from accessing data, as shown in the previous lesson as a blocked process. To find out what is locked and what type of locks are being used, you can either use SQL or SQL-EM.

Using sp_lock

The sp_lock stored procedure will return information about tables that have a lock on them at the time the stored procedure was called. The syntax of sp_lock is as follows: 
sp_lock [[@spid1=]spid [, [@spid2=]spid]]

If you do not specify a process ID, 
sp_lock

returns locks for all processes. Otherwise it will only return lock information for the spid(s) passed in.

Apply, Filter, Sort
  1. The System Process Identifier (spid) uniquely identifies an active connection to SQL Server.
  2. Identifies the status of the program.
  3. Loginname identifies the SQL Server login or Windows NT login that is connected.
  4. Hostname identifies the computer from which the connection was made.
  5. Blk indicates if the process is blocked by another process. A zero means that the process is not blocked and any other number identifies the process that is holding the lock.
  6. Dbname identifies in the database that you are logged in to.
  7. Cmd provides a high-level understanding of what the process is currently doing.

sp_lock output

The following diagram describes SQL-EM lock.
1) spid 2) dbid 3) ObjId 4) IndId 5) Type 6) Resource 7) Mode 8) Status
1) spid 2) dbid 3) ObjId 4) IndId 5) Type 6) Resource 7) Mode 8) Status

  1. The system process ID of the connection locking the resource. You can compare this with the ID from sp_who to find out the login name.
  2. The database ID of the object being locked.
  3. The object identifier of the table that is being locked.
  4. The index ID represents the index that is being locked.
  5. Type identifies the type of lock in request.
  6. The resource identifies the specific item that is being locked. For instance, for a page lock, it identifies the database page.
  7. Identifies the mode of the lock. A lock mode can be Shared, Update, Exclusive, Intent, Schema, or bulk update, or a combination of them.
  8. The status column represents the lock status. It can be GRANT for granted locked, WAIT for locks that have been requested, or CNVT for locks that are being converted.

Click the Active SQL connectionlink to see the output of the sp_lock stored procedure. As you can see in the link above, the type column in the sp_lock output indicates what type of lock is in request. Here is a list of the types of locks and the abbreviations used for them.
Lock Types and abbreviation
Lock Types and abbreviation
When looking at the locks you will always see locks for the connection running sp_lock. To convert the object ID to an object name, use the database that is locked and then type select object_name(ID).
In the next lesson, SQL-EM to monitor locks will be discussed.

[1]Lock: A SQL Server mechanism use to ensure transaction isolation levels.