2

I have created an extended event and I'd like to query data from event file / log. How can I query this to get locks only for a certain table (table name) and a specific database given by name as well.

CREATE EVENT SESSION [LocksXE] ON SERVER 

ADD EVENT sqlserver.lock_released(
    ACTION(sqlserver.database_name, sqlserver.query_hash, 
           sqlserver.request_id, sqlserver.sql_text)
    WHERE [mode]<>(6) AND [resource_type]=(5) OR [resource_type]=(6) AND [duration]>=(1000000)) 

ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.TRANSSPED2012\MSSQL\Log\LocksXE.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
      MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
      MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=ON)
GO

And a minor question - what can be done to get actual duration? (Now it's 0, always).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
pzaj
  • 1,062
  • 1
  • 17
  • 37

0 Answers0