Best way to track locks - SQL Server

Question

We have a new sp getting released and during testing we found that when it runs its blocking other OLTP transactions. We found that initially it was because the new sp was causing lock escalation on a table and we reduced the number of batch size and was able to avoid that. even after avoiding lock escalation, it is still blocking oltp transactions that are coming in. I think its locking the same row which the oltp transaction is updating.

I need to find a way to track all the locks held and release by the new sp. I tried trace/xevents(lock acquired/release) and it does not look like its capturing all the lock, may be cause it happens so fast.

Just to understand how lock acquired look like, i tested it out by doing a select * from atable. but it gives me different results. When we do select * doesnt it put a series of page locks, so i should be seeing shared page locks in the trace. but all i see is IS lock acquired and released.

what is the best way to track all the locks for a given transaction?

Answer 1

I ran below query in one session

begin tran
update orderstst 
set unitprice=unitprice+1
waitfor delay '00:00:20'

and ran below dmv while the query is running on other session..

select resource_database_id,request_mode,request_type,request_status,txt.text
 from sys.dm_tran_locks lck
 join
 sys.dm_exec_requests ec
 on ec.session_id=lck.request_session_id
  cross apply
  sys.dm_exec_Sql_text(ec.sql_handle) txt

I got below data...

when the transaction is still not committed,but completed,i ran above dmv again.but didnt get any output.since this is not currently executing.

But running below dmv,will still give me lock info of all sessions holding locks..so you will be able to identify which session is holding more locks

  select resource_database_id,request_mode,request_type,request_status
 from sys.dm_tran_locks lck
 join
 sys.dm_exec_sessions ec
 on ec.session_id=lck.request_session_id

Above query gives me below info..

So in summary,you have to run DMV1 or DMV2 for some period through sql agent job and insert into some table for later analaysis..

Further from SQL 2012,you can use extended events also..

Go to Management ->Extended Events ,Right Click and say ,start new session wizard.

Give it a name and check start at server startup

next screen gives you an option to select default template or not,i choose default template for locks as shown below and click next..

In the next screen,you can choose different events,in channel,select all channels and do the same in categories too and select the events of your interest,i choose below ..

In this screen,you can select actions ,i choose text ,sessionid

In next screen,filter like say for example ..gather events only for a databasename like 'somename' or query like some text..

Next screen is where you can save file to disk for later analysis..

Complete rest of screens and finally select start event session immediately option..

When you are done with gathering data,go to extended events and stop the session you created.Right click and say view target data..which shows you below screenn

EDIT: as of 12/3/2019 the start new session wizard is now located here: