MSSQL - Who locks what

3. June 2011 08:00

 

Locks are an important part of mssql server as they are used to protect the same information being changed by multiple clients at the same time when each of the clients don't expect the information to change at all. Sometimes it is important to find out what process or user has locks as long running quries can prevent other tasks from being able to retrive data from the database.

 

So below are a few quries for producing some basic information about who is locking what and just how many locks have been taken.

 

The following will produce locking information based on SPID, DatabaseName, LoginName and TotalLocks which are being held.

 

SELECT  L.request_session_id AS SPID, 
        DB_NAME(L.resource_database_id) AS DatabaseName,
		ES.login_name AS LoginName,
		COUNT(*) AS TotalLocks
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
		GROUP BY ES.login_name, L.request_session_id, DB_NAME(L.resource_database_id)
		ORDER BY DB_NAME(L.resource_database_id), ES.login_name

 

 

In certain enviroments the code above may not be that useful so the following will list the locks based on hostname instead of loginname.

 

 

SELECT  L.request_session_id AS SPID, 
        DB_NAME(L.resource_database_id) AS DatabaseName,
		ES.host_name AS LoginName,
		COUNT(*) AS TotalLocks
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
		GROUP BY ES.host_name, L.request_session_id, DB_NAME(L.resource_database_id)
		ORDER BY DB_NAME(L.resource_database_id), ES.host_name

 

 

The following will give SPID, LoginName, DatabaseName, LockedObjectName and the total locks which are being held.

 

 

SELECT  L.request_session_id AS SPID, 
		ES.login_name AS LoginName,
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName, 
		COUNT(*) AS TotalLocks
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
		GROUP BY ES.login_name, L.request_session_id, DB_NAME(L.resource_database_id), O.Name
		ORDER BY DB_NAME(L.resource_database_id), ES.login_name

 

E-mail Kick it! DZone it! del.icio.us Permalink


Pingbacks and trackbacks (1)+