Monitoring Lock Activity in SQL Server

To monitor the performance of the system, it is necessary to keep track of locking activity in SQL Server. Following are the more commonly used methods to do so:

• Using the sp_lock stored procedure

• Querying the syslockinfo table directly

• Viewing locking activity with SQL Enterprise Manager

• Viewing locking activity with SQL Profiler

• Viewing the current quantity of locks with Performance Monitor

As you read through the rest of this chapter, you might want to examine or monitor the locking activity for the examples presented. To assist you in that effort, the remainder of this section describes the methods of examining lock activity in SQL Server 2000.

Using the sp_lock Stored Procedure

The stored procedure sp_lock provides a snapshot of the locks that are currently being held on resources by various commands and transactions in SQL Server. The syntax of sp lock is as follows :

 Exec sp_lock [SPID1] [,SPID2] 

Following is the sample output of this command:

 Exec sp_lock  go spid   dbid   ObjId       IndId  Type Resource         Mode     Status ------ ------ ----------- ------ ---- ---------------- -------- ------     51      8           0      0 DB                    S        GRANT     51      1    85575343      0 TAB                   IS       GRANT     53      8  1685581043      1 PAG  1:5798           IX       GRANT     53      8           0      0 DB                    S        GRANT     53      8  1685581043      0 TAB                   IX       GRANT     54      8  1653580929      1 KEY  (c6028dcecb9e)   RangeS-S GRANT     54      8  1653580929      1 PAG  1:126            IS       GRANT     54      8  1653580929      1 KEY  (7601e649921b)   RangeS-S GRANT     54      8           0      0 DB                    S        GRANT     54      8  1653580929      1 KEY  (bc023be5404b)   RangeS-S GRANT     54      8  1653580929      1 KEY  (b3018d0ff0ac)   RangeS-S GRANT     54      8  1653580929      1 KEY  (c70165b0fdb4)   RangeS-S GRANT     54      8  1653580929      1 KEY  (020285578a77)   RangeS-S GRANT     54      8  1653580929      0 TAB                   IS       GRANT     54      8  1653580929      1 KEY  (c402f8d775ed)   RangeS-S GRANT     54      8  1653580929      1 KEY  (3803e05ac6f2)   RangeS-S GRANT     54      8  1653580929      1 KEY  (6e01b3d59a06)   RangeS-S GRANT     54      8  1653580929      1 KEY  (67018a3bdf5c)   RangeS-S GRANT     54      8  1653580929      1 KEY  (9e028a40b4ce)   RangeS-S GRANT 

The columns in the sp_lock output provide the following information:

• The spid is the process ID for a transaction.

• The dbid is the ID of the database on which locks are held.

• The ObjId is the ID of the resource on which table, key, or page locks are held.

• The IndId is the ID for the table index on which locks are held. A value of 0 indicates that the lock is on a data row, data page, table, or database. A value of 1 indicates that the lock is on a clustered index data row, index row, or index page. A value between 2 and 254 indicates that the lock is on a nonclustered index row or page, and a value of 255 indicates that the lock is on a text or image page.

• The Type is the type of lock being held on the resource.

• The Resource is the internal name of the resource on which locks are placed. This information comes from the syslockinfo table in the master database. Information displayed by this column is directly governed by the type of lock held on the resource. An explanation of the values in this column is presented later in this chapter in the "Lock Types and the syslockinfo Table" section.

• The Mode is the type of lock that is requested by the transaction. All the lock types possible will be discussed in the "SQL Server Lock Types" section later in this chapter.

• The Status is the current status of request. The possible values are GRANT , WAIT , and CNVRT .

By default, sp_lock returns locking information for all processes in SQL Server. In a system with a large number of concurrent processes executing, this can generate a substantial amount of output. If you are interested in examining the locks for only one or two processes, you can specify the process ID ( spid ) for those processes as arguments to sp_lock . sp_lock will then display only the locks that are associated with those spid (s):

 exec sp_lock 53  spid   dbid   ObjId       IndId  Type Resource         Mode     Status ------ ------ ----------- ------ ---- ---------------- -------- ------     53      8           0      0 DB                    S        GRANT     53      8  1685581043      0 TAB                   IX       GRANT     53      8  1685581043      1 PAG  1:5798           IX       GRANT 

Additional examples of using sp_lock will be presented in other sections in this chapter where appropriate.

Querying the syslockinfo Table

The information presented by sp_lock is retrieved from the memory resident table syslockinfo , which resides in the master database. The syslockinfo table contains information on all the locks currently granted or waiting to be granted in SQL Server. (The information contained in the syslockinfo table and the meaning of the values is described in more detail later in this chapter in the "Lock Types and the syslockinfo Table" section.) If you would like to see the information more directly, or you don't like the way the information is presented by sp_lock , you can write your own queries against the syslockinfo table to view and monitor the locking behavior.

For example, sp_lock displays the database ID instead of the database name, and displays the object ID instead of the object name, even if run in the same database in which the object exists. It would also be helpful to display the login ID associated with the spid . Listing 38.1 provides an example of a query against the syslockinfo table.

Listing 38.1 Example of a Query Against the syslockinfo Table

 select convert(varchar(30), suser_sname(p.sid)) as login,        convert (smallint, req_spid) As spid,        convert(varchar(30), db_name(rsc_dbid)) As db_name,        case rsc_dbid when db_id()             then convert(varchar(30), object_name(rsc_objid))             else convert(varchar(30), rsc_objid) end As Object,        rsc_indid As indid,        substring (lock_type.name, 1, 4) As Type,        substring (lock_mode.name, 1, 12) As Mode,        substring (lock_status.name, 1, 5) As Status,        substring (rsc_text, 1, 16) as Resource    from master..syslockinfo s    join master..spt_values lock_type on s.rsc_type = lock_type.number    join master..spt_values lock_status on s.req_status = lock_status.number    join master..spt_values lock_mode on s.req_mode = lock_mode.number -1    join master..sysprocesses p on s.req_spid = p.spid    where lock_type.type = 'LR'      and lock_status.type = 'LS'      and lock_mode.type = 'L'      and db_name(rsc_dbid) not in ('master', 'msdb', 'tempdb', 'model') order by spid, lock_type.number go login spid db_name       Object    indid  Type Mode       Status Resource ----- ---- ------------- --------- ------ ---- ---------- ------ -------------- sa      51 bigpubs2000   NULL           0 DB   S          GRANT sa      53 bigpubs2000   NULL           0 DB   S          GRANT sa      53 bigpubs2000   stores         0 TAB  IX         GRANT sa      53 bigpubs2000   stores         1 PAG  IX         GRANT  1:5798 sa      54 bigpubs2000   NULL           0 DB   S          GRANT sa      54 bigpubs2000   sales          0 TAB  IS         GRANT sa      54 bigpubs2000   sales          1 PAG  IS         GRANT  1:126 sa      54 bigpubs2000   sales          1 KEY  RangeS-S   GRANT  (7601e649921b) sa      54 bigpubs2000   sales          1 KEY  RangeS-S   GRANT  (c6028dcecb9e) sa      54 bigpubs2000   sales          1 KEY  RangeS-S   GRANT  (bc023be5404b) sa      54 bigpubs2000   sales          1 KEY  RangeS-S   GRANT  (b3018d0ff0ac) sa      54 bigpubs2000   sales          1 KEY  RangeS-S   GRANT  (c402f8d775ed) sa      54 bigpubs2000   sales          1 KEY  RangeS-S   GRANT  (3803e05ac6f2) sa      54 bigpubs2000   sales          1 KEY  RangeS-S   GRANT  (c70165b0fdb4) sa      54 bigpubs2000   sales          1 KEY  RangeS-S   GRANT  (020285578a77) sa      54 bigpubs2000   sales          1 KEY  RangeS-S   GRANT  (6e01b3d59a06) sa      54 bigpubs2000   sales          1 KEY  RangeS-S   GRANT  (67018a3bdf5c) sa      54 bigpubs2000   sales          1 KEY  RangeS-S   GRANT  (9e028a40b4ce) 

Note that to translate the integer values for the lock type, mode, and status, you need to do lookups against the values stored in the spt_values table in the master database. Also, the query contains a CASE expression for displaying the object name. If the database ID of the locked resource is the same as the current database context, it returns the object name; otherwise , it returns the object ID because the object_name() function operates only in the current database context.

To save yourself the trouble of having to type in the query listed in Listing 38.1, or having to read it in from a file each time you want to run it, you might want to consider creating your own system-stored procedure that invokes this query. You can then use that stored procedure to monitor locks instead of sp_lock . (For more information on creating system-stored procedures, see Chapter 28, "Creating and Managing Stored Procedures in SQL Server.")

Viewing Locking Activity with SQL Enterprise Manager

You have seen that the output of sp_lock is somewhat unfriendly because it displays the IDs of the database and objects. The custom query against the syslockinfo table provides somewhat more user -friendly output. You can also use the SQL Server Enterprise Manager to display the locking information. To see the output from the Enterprise Manager, expand the server items, expand the Management folder, expand the Current Activity item, and click on either Locks/Process ID or Locks/Object to display the locking information in SQL Server.

TIP

To see more information when viewing the lock activity in Enterprise Manager, be sure to go to the EM View menu and choose the Detail option. EM will then display detailed information about the locks beyond just the process ID or object name. The information displayed includes the lock type, lock mode, lock status, and index involved.

The Locks/Process ID item displays in the left windowpane a list of the processes that are currently holding locks in SQL Server. Clicking on one of the processes lists the locks currently being held by that process (see Figure 38.1).

Figure 38.1. Viewing locks by Process ID using SQL Enterprise Manager.

The Locks/Object item displays in the left windowpane a list of all the objects that currently have locks held on them. Clicking on one of the objects lists the processes currently holding locks on that object and the locks being held (see Figure 38.2).

Figure 38.2. Viewing locks by Object using SQL Enterprise Manager.

To display the command that was last executed by a process associated with a lock, you can double-click on an item in the right windowpane, or right-click on the item and select the Properties menu option. This will bring up a pop-up window containing the SQL text of the last command that was executed by that process.

NOTE

Personally, I am not a big fan of Enterprise Manager (EM) as a lock-monitoring tool because it has a number of frustrating shortcomings.

One problem you might run into when using EM to monitor locks is that it uses tempdb to build the results that it displays onscreen. At times, Enterprise Manager might end up being blocked requesting a lock in tempdb, and you are left watching an hourglass mouse cursor. This sort of defeats the purpose of a having a lock-monitoring tool to identify the process that is causing locking contention if the tool ends up being blocked as well, and cannot display the information.

Another shortcoming of lock monitoring in EM is that the lock information can only be displayed for a single process or for a single object. You cannot view all the current locks together in a single window. This makes it difficult to identify conflicting or competing lock requests between multiple processes or multiple objects.

A third, and one of the most annoying, shortcomings of using EM as a method of monitoring locking in SQL Server is that it has no automatic refresh capability for refreshing the lock information. To update the information displayed, you have to right-click on the Current Activity item in the left windowpane and choose the Refresh menu option. Unfortunately, when you do this, EM closes out the Locks/Process and Locks/Object windows and you have to go back and reopen them to get back to what you were looking at before. This can be tedious and cumbersome when you are trying to watch what is going on in real time.

Personally, I find that using Query Analyzer and sp_lock or a custom system-stored procedure that retrieves information from syslockinfo to be a better method of monitoring locking in SQL Server 2000. It provides more flexibility over what you can display, and a refresh of the results is as simple as clicking on the Execute button.

Viewing Locking Activity with SQL Profiler

Another tool to help you monitor locking activity in SQL Server 2000 is SQL Profiler. SQL Profiler provides a number of Lock events that you can capture in a trace. The trace information can be viewed in real time, or saved to a file or database table for further analysis at a later date. Saving the information to a table allows you to run different reports on the information to help in the analysis.

NOTE

This chapter provides only a brief overview of how to capture and view locking information using SQL Profiler. For more information on the features and capabilities of SQL Profiler and how to use it, see Chapter 7, "Using the SQL Server Profiler."

SQL Profiler provides the following lock events that can be captured in a trace:

• Lock:Acquired ”Indicates when a lock on a resource, such as a data page or a row, has been acquired .

• Lock:Cancel ”Indicates when the acquisition of a lock on a resource has been canceled (for example, as the result of a deadlock).

• Lock:Deadlock ”Indicates when two or more concurrent processes have deadlocked with each other.

• Lock:Deadlock Chain ”Provides the information for each of the events leading up to the deadlock. This information is similar to that provided by the 1204 trace flag,

• which is covered in the "Deadlocks" section later in this chapter.

• Lock:Escalation ”Indicates when a lower-level lock has been converted to a higher-level lock (for example, when page-level locks are escalated to a table-level lock).

• Lock:Released ”Indicates that a process has released a previously acquired lock on a resource.

• Lock:Timeout ”Indicates that a lock request that is waiting on a resource has timed out due to another transaction holding a blocking lock.

Figure 38.3 shows an example of choosing a set of locking events to monitor with SQL Profiler.

Figure 38.3. Choosing lock events in SQL Profiler.

SQL Profiler also provides a number of data values to display for the events being monitored . The following data columns are ones that you might find most useful when monitoring locking activity:

• spid ”The process ID of the process that generated the event.

• EventClass ”The type of event that is being captured.

• Mode ”For lock monitoring, the type of lock that is involved in the captured event.

• ObjectID ”The ID of the object that is involved in the locking event ”that is, the object that the lock is associated with.

• ObjectName ”The name of the object involved in the locking event.

• IndexID ”The ID of the index that the lock is associated with.

• TextData ”The query that generated the lock event.

• LoginName ”The login name associated with the process.

• ApplicationName ”The name of the application that is generating the lock event.

Keep in mind that many internal system processes also acquire locks within SQL Server. If you want to filter out those processes and focus on specific processes, users, or applications, use the filters in SQL Profiler to include the information you want to trace or exclude the information you don't want to trace (see Figure 38.4).

Figure 38.4. Filtering out unwanted information in SQL Profiler.

After you have set up your events, data columns, and filters, you can begin the trace. An example of the type of information captured is shown in Figure 38.5.

Figure 38.5. Lock information captured in SQL Profiler trace.

Viewing the Current Quantity of Locks with Performance Monitor

Another method of monitoring locking in SQL Server is through the Performance Monitor. The stored procedure sp_lock and Enterprise Manager provide a snapshot of the actual locks currently in effect in SQL Server. If you want to monitor the locking activity as a whole on a continuous basis, you can use the NT Performance Monitor and monitor the counters that are available for the SQLServer:Locks performance object (see Figure 38.6).

Figure 38.6. Choosing counters for the SQLServer:Locks performance object in Performance Monitor.

You can use the SQLServer:Locks object to help detect locking bottlenecks and contention points in the system, as well as to provide a summary of the overall locking activity in SQL Server. You can use the information that Performance Monitor provides to identify whether locking problems are the cause of any performance problems. You can then take appropriate corrective actions to improve concurrency and the overall performance of the system. The counters that belong to the SQLServer:Locks object are as follows:

• Average Wait Time ”This counter represents the average wait time (in milliseconds ) for each lock request. A high value is an indication of low concurrency of the system.

• Lock Requests/sec ”This counter represents the total number of new locks and lock conversion requests made per second. A high value for this counter is not necessarily a cause for alarm; it might simply indicate a system with a high number of concurrent users.

• Lock Timeouts/sec ”This counter represents the total number of lock timeouts per second that occur for lock requests on a resource that cannot be granted before the lock timeout interval is exceeded. By default, a blocked process will wait indefinitely unless the application specifies a maximum timeout limit using the SET LOCK_TIMEOUT command. A high value for this counter might indicate that the timeout limit is set to a low value in your application, or that you are experiencing excessive locking contention.

• Lock Wait Time ”This counter represents the cumulative wait time for each lock request. It is given in milliseconds. A high value here indicates that you might have long-running or inefficient transactions that are causing blocking and locking contention.

• Lock Waits/sec ”This counter represents the total number of lock requests generated per second for which a process had to wait before a lock request on a resource was granted. A high value might indicate inefficient or long-running transactions or a poor database design that is causing a large number of transactions to block one another.

• Number of Deadlocks/sec ”This number represents the total number of lock requests per second that resulted in deadlocks. Deadlocks and how to avoid them are discussed in the "Deadlocks" section later in this chapter.

For more information on using NT Performance Monitor for monitoring SQL Server performance, see Chapter 37, "Monitoring SQL Server Performance."