Write your own sp_lock system stored procedure in SQL Server 2005

The sp_lock system stored procedure is a great tool for checking the amount of locking that occurs on your database system. It returns the number and types of locks that are being held by current active SQL Server sessions.

One of my previous articles describes how you can use sp_lock to diagnose problems on your SQL Server .

There are, however, a few drawbacks to sp_lock. The results it returns are not very straightforward to the novice DBA. It does a great job showing what session is causing the largest number of locks on the system, but it does not show you much accompanying detail of those objects or sessions. You could create custom scripts to look up that information, but those results tend to be complicated, and the detail is mediocre at best. There were other system tables that you could use to look up lock information, such as syslockinfo, but that detail isn't all that great either. Another huge drawback is that sp_lock and syslockinfo are deprecated features, so they may not be included in future SQL Server releases. SQL Server 2005 includes new dynamic management views that contain a lot of locking detail and make it easy to correlate locking information to make it much more meaningful.

sys.dm_tran_locks

The new dynamic view sys.dm_tran_locks returns information about current locks in the system. This view returns the same type of information as sp_lock but with a little bit more detail. The magic here is that it is a view, which enables the DBA to easily join it to other tables.

A custom sp_lock example

USE MASTER 
 GO    
 CREATE  PROCEDURE [dbo].[sp_LockDetail]
 AS

 BEGIN
     SELECT
 
         SessionID = s.Session_id,
         resource_type,   
         DatabaseName = DB_NAME(resource_database_id),
         request_mode,
         request_type,
         login_time,
         host_name,
         program_name,
         client_interface_name,
         login_name,
         nt_domain,
         nt_user_name,
         s.status,
         last_request_start_time,
         last_request_end_time,
         s.logical_reads,
         s.reads,
         request_status,
         request_owner_type,
         objectid,
         dbid,
         a.number,
         a.encrypted ,
         a.blocking_session_id, 
         a.text       
     FROM   
         sys.dm_tran_locks l
         JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
         LEFT JOIN   
         (
             SELECT  *
             FROM    sys.dm_exec_requests r
             CROSS APPLY 
sys.dm_exec_sql_text(sql_handle)
         ) a ON s.session_id = a.session_id
     WHERE  
         s.session_id > 50
     END

I want to look at the JOIN statements I use in the procedure. I am joining the sys.dm_tran_locks table with the sys.dm_exec_sessions table to retrieve locking information regarding the current sessions on the server. This JOIN allows me to link up with session detail and the corresponding lock detail for that session.

Next, I perform a LEFT JOIN on a subquery to retrieve execution-related information for any statements that are currently executing. A LEFT JOIN is used because there will likely be current sessions on the server that may be holding some type of locks that are not currently executing. If the query execution data is there, that's great; if not, it isn't a problem because of the LEFT JOIN.

Notice that in the subquery I use the sys.dm_exec_sql_text function with the CROSS APPLY operator. This allows me to use the sql_handle field stored in the sys.dm_exec_requests view to determine the statement being executed. The sql_handle contains a hash of the SQL statement that is currently executing; this is handy information when you're tryng to solve problems.

Note: I typically do not recommend using the * to return all of the rows from a query (especially in a production database), but it is okay for the purposes of this example.

The rest of the query returns details from the views and subquery used in my join. In my WHERE statement, I am filtering out any database session that is less than or equal to 50 to eliminate any system sessions. For this system procedure, I am only interested in user sessions.

The above procedure could easily be implemented as a view instead of a stored procedure. I like the procedure better because it lets me quickly return data without having to issue a SELECT statement against a view.

Marking a system stored procedure

I want to mark the procedure I created as a system procedure because it will allow me to run the stored procedure in any database context and retrieve information specific to that database. I have already completed the first step in marking an object as a system procedure, which is creating the object in the master database. Once I have the procedure in the master database, I need to run another system stored procedure to mark the object. Below is the call to mark the procedure as a system procedure:

USE MASTER
EXECUTE sp_ms_marksystemobject 'sp_LockDetail'

I can execute the procedure sp_LockDetail in the context of any database on my SQL Server instance and return locking information for that database. This is much easier than creating the procedure in every user database.

I'll show you how to put the new system stored procedure to use. First, I will create a table and load some data into it.

CREATE TABLE LockTempData
(
IDCol INT IDENTITY(1,1) PRIMARY KEY, NumberField VARCHAR(5) ) GO DECLARE @I INT SET @I = 5000 WHILE @i > 0 BEGIN
INSERT INTO LockTempData( NumberField) SELECT datepart(ms, getdate()) + @I SET @I = @I - 1 END

I will update records in this table inside of a transaction and leave the transaction open. This transaction will hold the locks on the records in the update. I added the WITH(HOLDLOCK) hint to ensure that the database isolation level wouldn't affect our test.

BEGIN TRANSACTION UPDATE TOP(2000) LockTempData WITH(HOLDLOCK) SET NumberField = NumberField + IDCol

In a separate query session, execute the sp_LockDetail stored procedure that we created earlier. I will execute this stored procedure in the same database context as my previously issued UPDATE statement.

EXECUTE sp_LockDetail

My results returned just over 2,000 rows, which indicates that the UPDATE statement issued used row (KEY) level locking to issue the statement.

This stored procedure produces a lot of meaningful data. I am instantly able to identify who issued the statements, what program was used to issue the statements, the number of reads involved, and the type of lock. If we were to run this statement while the original UPDATE statement was running, we would be able to see the exact statement that was causing the locks.

Try it out

System stored procedures are great tools that allow you to write a procedure in one database and run it in the context of other databases. The sp_LockDetail example is just one example of how you can write custom system stored procedures to monitor your databases. Other possible uses for system stored procedures are: getting the size allocations for all tables stored in your database and generating SQL statements on the fly to save coding time. Try these out for yourself -- you'll be glad that you did.