How can one query this Extended Events session?

• VoldemarG

  Hall of Fame

  Points: 3883

  More actions

  July 28, 2016 at 8:20 am

  #328671

  My session definition for reference (captures executions over 2 sec long):

  HOW CAN I query this session? After it runs, say, for 24 hours.

  ------------------------------------------

  CREATE EVENT SESSION [LongRunningQueries]

  ON SERVER

  ADD EVENT sqlserver.sp_statement_completed(

  ACTION (package0.collect_system_time, sqlserver.database_id, sqlserver.sql_text)

  WHERE (([duration]>(2000000) AND [source_database_id]<>(1) AND [source_database_id]<>(4) AND [source_database_id]<>(15)))),

  ADD EVENT sqlserver.sql_statement_completed(

  ACTION (package0.collect_system_time, sqlserver.sql_text)

  WHERE (([duration]>(2000000) AND [source_database_id]<>(1) AND [source_database_id]<>(4) AND [source_database_id]<>(15))))

  ADD TARGET package0.ring_buffer

  WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 1 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = ON)

  Likes to play Chess

• BLOB EATER

  SSChampion

  Points: 14609

  More actions

  July 28, 2016 at 9:02 am

  #1892497

  You basically have to query that FIFO ring buffer.

  Jonathan goes into good detail about how to parse / query the data. Hope you like XML 😀

  https://www.sqlskills.com/blogs/jonathan/extended-events-ring_buffer/%5B/url%5D