none
Querying the sys.dm_xe_session_targets - SQL Server 2008 R2/2012

    Question

  • Hi,

    can I query the sys.dm_xe_session_targets after stopping an extended event session?

    Thanks


    • Edited by pscorca Tuesday, October 22, 2013 9:37 AM
    Tuesday, October 22, 2013 9:37 AM

Answers

All replies

  • Ok, I could explain better the question.

    In order to read the data collection obtained by an extended event session, when can I query the data saved in the target (fe file)? Between start and stop the event session? After the session stop?

    Many thanks for your suggests.

    Tuesday, October 22, 2013 11:28 AM
  • In order to read the data collection obtained by an extended event session, when can I query the data saved in the target (fe file)? Between start and stop the event session? After the session stop?

     

    You can read extended event data collected in files with the sys.fn_xe_file_target_read_file TVF either during or after the session.  Be aware that data are written asynchronously to the live file so recent collected events may not be immediately visible according the MAX_DISPATCH_LATENCY specification.

     


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, October 22, 2013 12:14 PM
  • Hi Dan, thanks for your reply.

    Inside a step job I've put the create event session + session start before the exec statement that calls a stored procedure. After this exec command I've inserted the session stop. As a target, I've written a file (*.xel, *.xem).

    When I run a such query:

    SELECT name, target_name, CAST(xet.target_data AS xml)
    FROM sys.dm_xe_session_targets AS xet
    JOIN sys.dm_xe_sessions AS xe
       ON (xe.address = xet.event_session_address)
    WHERE xe.name = 'myTrace'
    

    I cannot see any results, but the files are created.

    Now, how can I see the event data collection results, if possible?

    Thanks

    Tuesday, October 22, 2013 12:49 PM
  • Ok, I think to understand.

    In the create event session definition is present MAX_DISPATCH_LATENCY = 300 SECONDS, that is an high value.

    The script was generate by the SSMS add-in of Codeplex for SQL Server 2008 R2.

    Right?

    Tuesday, October 22, 2013 1:02 PM
  • Any ideas, please?
    Tuesday, October 22, 2013 2:29 PM
  • Ok, I think to solved the issue.

    In the event session I've added a filter based on the duration of the query to monitor (fe duration > 1000). Reducing the value for it I've collected some event data.

    Bye

    Tuesday, October 22, 2013 8:41 PM