Recently I needed to find a stored procedure that was creating a lock on a table and updating the table regularly. So my starting point was the table name, but I needed to find where the commad was coming from that was locking the table, and what stored procedure was doing the update, if any. As I could not be sure that it was a sproc, I wanted to make sure that I also got the program name.
A simple version of the query would look like this:
--simple SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID() AND resource_associated_entity_id = OBJECT_ID(N'CciDemo')
But this does not get full info. But by joining on the dmv exec_sessions and exec_requests on the corresponding session id columns, and by cross applying on the exec_sql_text on the exec_request sql_handle column I can get the full text of the command. I can also narrow down the lock requests types to only the update lock types:
--verbose SELECT sesh.program_name, sesh.host_name, st.text, sesh.login_name, lock.request_mode, lock.request_session_id FROM sys.dm_tran_locks lock INNER JOIN sys.dm_exec_sessions sesh ON sesh.session_id = lock.request_session_id INNER JOIN sys.dm_exec_requests req ON req.session_id = lock.request_session_id CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS st WHERE lock.resource_database_id = DB_ID() AND lock.resource_type = 'OBJECT' AND lock.resource_associated_entity_id = OBJECT_ID(N'CciDemo') AND lock.request_mode IN ('X', 'U', 'IX')
The results of the queries whilst I was running a long running update on a table is below.