Lock Detection Scripts in SQL Server 2005

In SQL Server 2005, the dynamic management views like sys.dm_tran_locks, sys.dm_os_tasks etc. have added much more information to troubleshoot locking issues.

You can use the following query to associate a SPID value with a Windows thread id. You can then monitor the performance of the thread in the Windows Performance Monitor. This query does not return SPIDs that are currently sleeping.

SELECT OSTasks.session_ID, OSThreads.os_thread_id
FROM sys.dm_os_tasks AS OSTasks
INNER JOIN sys.dm_os_threads AS OSThreads
ON OSTasks.worker_address = OSThreads.worker_address
WHERE OSTasks.session_ID IS NOT NULL
ORDER BY OSTasks.session_ID;
GO

The following example returns all of the locks held by the current session:

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

This SQL can be used to get more information on the locks. It joins a couple of dynamic management views and also sys.sysprocesses. You can play around with these and chose the columns and the information that makes the most sense for your environment:

SELECT DISTINCT
SP.SPID,
SP.HOSTPROCESS,
SP.LOGIN_TIME,
SP.LAST_BATCH,
SP.OPEN_TRAN,
SP.STATUS,
SP.HOSTNAME,
SP.PROGRAM_NAME,
SP.CMD,
SP.LOGINAME,
SP.CPU,
SP.MEMUSAGE,
A.RESOURCE_TYPE, A.RESOURCE_SUBTYPE,
A.RESOURCE_ASSOCIATED_ENTITY_ID,
(CASE A.REQUEST_MODE
WHEN ‘SCH-S’ THEN ‘SCHEMA STABILITY’
WHEN ‘SCH-M’ THEN ‘SCHEMA MODIFICATION’
WHEN ‘S’ THEN ‘SHARED’
WHEN ‘U’ THEN ‘UPDATE’
WHEN ‘X’ THEN ‘EXCLUSIVE’
WHEN ‘IS’ THEN ‘Intent Shared’
WHEN ‘IU’ THEN ‘Intent Update’
WHEN ‘IX’ THEN ‘Intent Exclusive’
WHEN ‘SIU’ THEN ‘Shared Intent Update’
WHEN ‘SIX’ THEN ‘Shared Intent Exclusive’
WHEN ‘UIX’ THEN ‘Update Intent Exclusive’
WHEN ‘BU’ THEN ‘Bulk Update’
WHEN ‘RangeS_S’ THEN ‘Shared Key-Range and Shared Resourcelock’
WHEN ‘RangeS_U’ THEN ‘Shared Key-Range and Update Resource lock’
WHEN ‘RangeI_N’ THEN ‘Insert Key-Range and Null Resourcelock’
WHEN ‘RangeI_S’ THEN ‘Key-Range Conversion lock, created by an overlap of RangeI_N and S locks’
WHEN ‘RangeI_U’ THEN ‘Key-Range Conversion lock, created byan overlap of RangeI_N and U locks’
WHEN ‘RangeI_X’ THEN ‘Key-Range Conversion lock, created byan overlap of RangeI_N and X locks’
WHEN ‘RangeX_S’ THEN ‘Key-Range Conversion lock, created byan overlap of RangeI_N and RangeS_S locks’
WHEN ‘RangeX_U’ THEN ‘Key-Range Conversion lock, created byan overlap of RangeI_N and RangeS_U locks’
WHEN ‘RangeX_X’ THEN ‘Exclusive Key-Range and ExclusiveResource lock’
ELSE NULL
END) AS REQUEST_LOCK_MODE,
A.REQUEST_TYPE,
A.REQUEST_STATUS,
A.REQUEST_OWNER_TYPE,
C.NAME,
C.TRANSACTION_BEGIN_TIME,
C.TRANSACTION_TYPE,
C.TRANSACTION_STATE,
C.TRANSACTION_STATUS,
C.TRANSACTION_STATUS2,
C.DTC_STATE,
C.DTC_ISOLATION_LEVEL,
DB_NAME(SP.DBID) DATABASE_NAME
FROM
SYS.DM_TRAN_LOCKS A
INNER JOIN SYS.SYSPROCESSES SP
ON A.REQUEST_SESSION_ID = SP.SPID
LEFT OUTER JOIN SYS.DM_EXEC_REQUESTS B
ON A.REQUEST_REQUEST_ID = B.REQUEST_ID
LEFT OUTER JOIN SYS.DM_TRAN_ACTIVE_TRANSACTIONS C
ON A.REQUEST_OWNER_ID = C.TRANSACTION_ID
WHERE SP.SPID > 50 /*REMOVING THE SYSTEM SPIDS*/
AND SP.PROGRAM_NAME NOT LIKE ‘SQLAGENT%’
ORDER BY SPID
GO