SQL Server - Who's got a lock and how long've they had it?

SQL Server 2016 SP1 for the adventure below.

A question came up this morning on Twitter #sqlhelp about capturing all activity against a given SQL Server table for a period of time.  The goal was to identify what was taking out a lock on a table and taking its sweet time before releasing it 😀

Extended Events and SQL Trace are options to capture all of this activity.  But the observer overhead is something to consider.

Depending on whether observer overhead is a risk and whether transactions span SQL requests (and thus might prevent identifying the SQL text responsible for the lock request), polling at a given interval such as 1 or 5 minutes with a query like that below might also fit the bill.


Session 1
Create the table, grab table lock.

drop table if exists sqL_handLe;
begin tran
create table sqL_handLe (pk bigint);
waitfor delay '00:00:30';

Session 2
Identify the locks

select so.name as [lock_object_name],
tl.resource_associated_entity_id as [lock_object_id],
tl.resource_database_id,
st.session_id,
dt.database_transaction_begin_time,
--dt.database_transaction_log_record_count,
--dt.database_transaction_log_bytes_used,
--dt.database_transaction_log_bytes_reserved,
tl.request_mode,
tl.request_status,
tl.resource_type, 
--tl.resource_subtype,
--tl.resource_description,
--tl.resource_lock_partition,
sqlT.text as request_sql_text
from sys.dm_tran_locks tl
join sys.objects so with (nolock) 
on so.object_id = tl.resource_associated_entity_id
join sys.dm_tran_database_transactions dt on dt.transaction_id = tl.request_owner_id
and tl.resource_database_id = dt.database_id
join sys.dm_tran_session_transactions st on st.transaction_id = tl.request_owner_id
full outer join sys.dm_exec_requests req on req.session_id = st.session_id
outer apply sys.dm_exec_sql_text(req.sql_handle) as sqlT
where so.name ='sqL_handLe'

Results of the query while the batch which created the table is still executing (during the waitfor).

Results of the query after the batch has completed - no request so no sql_handle => no SQL text returned.  But the lock is still held.

Polling for locks every minute or so and storing results in a table can be much more lightweight than a trace or Extended events.  However - not guaranteed to capture the SQL text which was responsible for creating the lock if the transaction spans batches/requests.  So that's the trade-off.

A reminder to myself to clean up after myself.

drop table if exists sqL_handLe;
commit tran