0

I'm trying to create EE session that will track lock_acquired and lock_released events for specific object

Here is definition of EE session

CREATE EVENT SESSION [Locks acquired] ON SERVER ADD EVENT sqlserver.lock_acquired(SET collect_database_name=(1),collect_resource_description=(1) ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username) WHERE ([sqlserver].equal_i_sql_unicode_string AND ([associated_object_id]=(597577167.) OR [associated_object_id]=(72057594043236352.) OR [associated_object_id]=(72057594043236352.)))), ADD EVENT sqlserver.lock_released(SET collect_database_name=(1),collect_resource_description=(1) ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username) WHERE ([sqlserver].equal_i_sql_unicode_string AND ([associated_object_id]=(597577167.) OR [associated_object_id]=(72057594043236352.) OR [associated_object_id]=(72057594043236352.)))) ADD TARGET package0.event_file(SET filename=N'C:\Locks acquired on\Locks.xel')

This will track any lock acquired and released for associated_object_id = 597577167 (this is object_id of specific object , if locked resource is object then we will get associated_object_id = 597577167)

and for [associated_object_id]=(72057594043236352.) ( If locked resource is PAGE or KEY we will get [associated_object_id]=(72057594043236352) , 72057594043236352 is partition id of object 597577167 (there is only single partition for this object ))

This works fine , I have tried to test this , I have this specific table with id 597577167 there is only one row, I have initiated open transaction for this table

begin tran update "table" set col1 = 1

Ok - i have got lock_acquired events for this object ,page ,rid

I have leaved this for three minutes , then I have rollback this transaction and I have got lock_released events for the object , page , rid

But despite on three minutes of locks the lock_released events show me duration - 0

Why this happened ? I have expected time of lock duration between lock-acquired and lock-released but I see only zero for duration for this event or for any other lock events

Did some face with this ?

Alexandr
  • 25
  • 7
  • The duration of lock_released is the time it takes to release the lock, not the duration the lock was held. – Dan Guzman May 27 '20 at 17:49
  • How I can capture required metrics ? I need to have info about lock duration , when specific session has acquired lock and relesed this . – Alexandr May 27 '20 at 17:56
  • 1
    I think you want the `wait_completed` event instead, filtered on `wait_type` 'LCK*'. – Dan Guzman May 28 '20 at 11:59

0 Answers0