1

I have a stored procedure dbo.testproc1 that acquires an exclusive table lock on a table dbo.L. This database is used in a multi-user environment and now how can I find, how much time is spent on waiting for this exclusive lock (by other concurrent sessions)?

I know sys.dm_os_wait_stats can be used to find the wait times, but how can I drill down to a specific lock on a particular table?

fr21
  • 1,746
  • 7
  • 26
  • 45
  • You want to find overall statistics? Or just for a particular currently waiting query – Charlieface Dec 28 '21 at 13:16
  • @charlieface : Not for currently waiting query. Need to find amount of time that was already spent by other sessions waiting for this lock. – fr21 Dec 28 '21 at 16:57
  • You cannot easily connect specific lock from session to another session without "Extended Events". You can connect two session and add attributes (actual table lock) by connection of tables: sys.dm_exec_requests (for blocking session id), sys.dm_tran_locks (for associated_entity, request_session_id and lock detail) and derive your time as diff between session and lock start time. Something like https://stackoverflow.com/questions/694581/how-to-check-which-locks-are-held-on-a-table ? – Deadsheep39 Dec 29 '21 at 13:19

0 Answers0