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 queries can prevent other clients from being able to run queries from the database.

So below are a few queries 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 environments the code above may not be that useful so the following will list the locks based on hostname instead of login name.

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