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.
Create the table, grab table lock.
Identify the locks
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.