0
SELECT ISNULL(SUM(wt.wait_duration_ms) / 1000,0) AS TotalBlockTime_s
  FROM sys.dm_tran_locks AS tm
 INNER JOIN sys.dm_os_waiting_tasks as wt 
                                    ON tm.lock_owner_address = wt.resource_address

The above code gives me exactly what I need, but I noticed it is a bit slow on some of my servers (~6 seconds).

How else can I quickly get total block time on my servers?

To give an example.. if User1 has been running a query for 10 seconds, and two more Users come in and are blocked for 3 seconds each, I want the query to return 6 seconds.

1 Answer 1

0
SELECT ISNULL(SUM(wait_duration_ms) / 1000,0) AS TotalBlockTime_s
FROM sys.dm_os_waiting_tasks
WHERE wait_type IN (
SELECT wait_type FROM sys.dm_os_wait_stats
WHERE wait_type LIKE '%LCK%'
)

Not perfect, but the above should suffice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.