February 20, 2018 at 5:00 am
Hi,
Lately we notice blocking issues on our SQL server, because one of the query's somehow (the DEV'ers are currently investigating) haven't released a lock on a KEY or PAGE. Meanwhile the connection that executed the query that placed the lock has continued executing other queries. My problem is we only notice there is an issue with this unreleased lock at the moment when other connections start experience blocking. I would like to know upfront if there is an exclusive lock on an object for a period longer than say 1 minute. In that case I can already investigate it before other connections experience problems. In the DMV "dm_tran_locks" there is no timestamp of when the lock is set.
Is there a way to find unreleased locks that are set a specific time ago? Or find locks that are still active for a specific period of time?
February 20, 2018 at 5:09 am
HanShi - Tuesday, February 20, 2018 5:00 AMHi,
Lately we notice blocking issues on our SQL server, because one of the query's somehow (the DEV'ers are currently investigating) haven't released a lock on a KEY or PAGE. Meanwhile the connection that executed the query that placed the lock has continued executing other queries. My problem is we only notice there is an issue with this unreleased lock at the moment when other connections start experience blocking. I would like to know upfront if there is an exclusive lock on an object for a period longer than say 1 minute. In that case I can already investigate it before other connections experience problems. In the DMV "dm_tran_locks" there is no timestamp of when the lock is set.
Is there a way to find unreleased locks that are set a specific time ago? Or find locks that are still active for a specific period of time?
Why can't you try these ?
February 20, 2018 at 5:13 am
Thanks, I will give it a try.
I was so focused on getting this information from a DMV I forgot to look at Extended Events :doze:
February 20, 2018 at 8:10 am
Unfortunately the extended events approach was not really working for me. The Extended Events generated a lot of data, even though I extended the filter to only include the eXclusive locks. The query to analyse the results took about 1/5 of the time the Extended Events trace was running. So if the blocking occurred after one hour, the analyzing script took over 10 minutes to run.
But I've also found a good script created by Olaf Helper which shows all active locks on the current database by querying several DMV's. See https://gallery.technet.microsoft.com/scriptcenter/List-all-Locks-of-the-2a751879. It displays the duration of a lock by combining the information from [dm_tran_locks], [dm_exec_sessions] and [dm_tran_active_transactions].
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply