locked
Querying Extended Events Target RRS feed

  • Question

  • I'm trying to query an extended events file I created during my extended events session.  The session I'm using for this is SP_Statement_Completed.  However when I run the event, I capture the execution of an AdventureWorks SP.

    I have wrote the following query to capture the extended events target data, but some values are coming in as NULL when I know values exist, maybe you guys can take a look at my script and see if I'm querying correctly.  I'm getting NULL values on Database Name, Object Name, SessionID, Username, and a couple of others.

    /* I use this as a template to pull the info I need for Extended Events.  It's obviously customizable to whatever event you want to run
    and what data you want to view on.
    
    */
    
    If OBJECT_ID('tempdb..#Session_Table') IS NOT NULL
    	DROP TABLE #Session_Table
    Go
    
    WITH xEvents
    AS
    --This allows you to read the results of the Extended Events Session from File You created.
    (Select object_name as EventName,
    Cast (event_data as XML) AS xEventData
    FROM sys.fn_xe_file_target_read_file('D:\Deployments\SP_batchcomp*.xel', NULL, NULL, NULL))
    
    --This pulls the EventName(Extended Event Used), and Event_Data(holds the values) columns which can be split up by individual columns, like the example below, Duration and Object_id
    
    Select EventName,
    xEventData.value('(/event/data[@name="duration"]/value)[1]', 'bigint') Duration,
    --Object_name(xEventData.value('(/event/data[@name=''object_id'']/value)[1]', 'bigint')) [Object_Name]
    xEventData.value('(/event/data[@name="database_name"]/value)[1]', 'varchar(100)') DatabaseName,
    xEventData.value('(/event/data[@name="object_name"]/text)[1]', 'varchar(100)') ObjectName,
    xEventData.value('(/event/data[@name=''session_id'']/value)[1]', 'int') SessionID,
    xEventData.value('(/event/data[@name=''Username'']/text)[1]', 'varchar(100)') UserName,
    xEventData.value('(/event/data[@name=''query_plan_hash'']/text)[1]', 'varchar(1000)') QueryPlanHash,
    xEventData.value('(/event/data[@name=''plan_handle'']/text)[1]', 'bigint') PlanHandle,
    xEventData.value('(/event/data[@name=''logical_reads'']/value)[1]', 'bigint') LogicalReads,
    xEventData.value('(/event/data[@name=''physical_reads'']/value)[1]', 'bigint') PhysicalReads
    
    INTO #Session_Table
    From xEvents
    
    --I sent the results into a temp table where I can go ahead and filter out the results just like a standard SQL query.
    Select EventName, DatabaseName, Duration, ObjectName, SessionID, UserName, QueryPlanHash, PlanHandle, LogicalReads, PhysicalReads
    From #Session_Table
    --Where [ObjectName] = 'uspGetEmployeeManagers'
    Go
    
    Drop Table #Session_Table
    Go


    Friday, December 28, 2018 3:20 PM

Answers

  • Ok, I figured out what it is.  You have to pay a close attention at the xml file that you're reading from.  Some events are classified as data, and others are classified as action.  Also, they're all classified as value and no text, at least from the xml that I have.  See the fix below.  I made bold a few items below to show the difference.

    Select EventName,
    xEventData.value('(/event/data[@name="duration"]/value)[1]', 'bigint') Duration,
    --Object_name(xEventData.value('(/event/data[@name=''object_id'']/value)[1]', 'bigint')) [Object_Name]
    xEventData.value('(/event/action[@name="database_name"]/value)[1]', 'varchar(100)') DatabaseName,
    xEventData.value('(/event/data[@name="object_name"]/value)[1]', 'varchar(100)') ObjectName,
    xEventData.value('(/event/action[@name="session_id"]/value)[1]', 'int') SessionID,
    xEventData.value('(/event/action[@name="username"]/value)[1]', 'varchar(100)') UserName,
    xEventData.value('(/event/action[@name="query_plan_hash"]/value)[1]', 'varchar(100)') QueryPlanHash,
    xEventData.value('(/event/action[@name="plan_handle"]/value)[1]', 'varchar(100)') PlanHandle,
    xEventData.value('(/event/data[@name="logical_reads"]/value)[1]', 'bigint') LogicalReads,
    xEventData.value('(/event/data[@name="physical_reads"]/value)[1]', 'bigint') PhysicalReads


    • Proposed as answer by Olaf HelperMVP Saturday, December 29, 2018 6:02 AM
    • Marked as answer by Olaf HelperMVP Friday, September 27, 2019 1:04 PM
    Friday, December 28, 2018 8:45 PM
  • Ok, I figured out what it is.  You have to pay a close attention at the xml file that you're reading from.  Some events are classified as data, and others are classified as action.  Also, they're all classified as value and no text, at least from the xml that I have.

    I suspect the query in your question may have been originally developed for a ring buffer target. The XML format of events in the ring buffer is different than that of an event file.

    As you found, actions and fields are different beasts in the XE world. It's not that events are classified differently, it's that data captured with the event is distinguished differently depending on whether the source data is part of the event itself (a field, contained in the /event/data/value element text) or gathered separately when the event fired (an action, /event/action/value element text).

    Additionally, each event has name, timestamp, and package attributes that are neither actions nor fields. The name and timestamp are returned by sys.fn_xe_file_target_read_file resultset as columns object_name and timestamp_utc respectively so no need to parse those from the XML. However, package is not so you'll need to extract the value from the XML if that is of interest to you.

    xEventData.value('(/event[1]/@package)', 'sysname') PackageName,
    xEventData.value('(/event[1]/@name)', 'sysname') EventName,
    xEventData.value('(/event[1]/@timestamp)', 'datetime2(3)') EventTimestamp
    One thing I'd like to make clear is that extended events in the file are not stored as XML. The actual file format is binary to reduce storage and improve performance. The sys.fn_xe_file_target_read_file TVF exposes much of the event data as XML because a relational rowset doesn't lend itself well to potentially returning thousands of columns of varying data types.

     

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Puzzle_Chen Tuesday, January 1, 2019 2:42 AM
    • Marked as answer by Olaf HelperMVP Friday, September 27, 2019 1:04 PM
    Saturday, December 29, 2018 12:14 PM

All replies

  • Ok, I figured out what it is.  You have to pay a close attention at the xml file that you're reading from.  Some events are classified as data, and others are classified as action.  Also, they're all classified as value and no text, at least from the xml that I have.  See the fix below.  I made bold a few items below to show the difference.

    Select EventName,
    xEventData.value('(/event/data[@name="duration"]/value)[1]', 'bigint') Duration,
    --Object_name(xEventData.value('(/event/data[@name=''object_id'']/value)[1]', 'bigint')) [Object_Name]
    xEventData.value('(/event/action[@name="database_name"]/value)[1]', 'varchar(100)') DatabaseName,
    xEventData.value('(/event/data[@name="object_name"]/value)[1]', 'varchar(100)') ObjectName,
    xEventData.value('(/event/action[@name="session_id"]/value)[1]', 'int') SessionID,
    xEventData.value('(/event/action[@name="username"]/value)[1]', 'varchar(100)') UserName,
    xEventData.value('(/event/action[@name="query_plan_hash"]/value)[1]', 'varchar(100)') QueryPlanHash,
    xEventData.value('(/event/action[@name="plan_handle"]/value)[1]', 'varchar(100)') PlanHandle,
    xEventData.value('(/event/data[@name="logical_reads"]/value)[1]', 'bigint') LogicalReads,
    xEventData.value('(/event/data[@name="physical_reads"]/value)[1]', 'bigint') PhysicalReads


    • Proposed as answer by Olaf HelperMVP Saturday, December 29, 2018 6:02 AM
    • Marked as answer by Olaf HelperMVP Friday, September 27, 2019 1:04 PM
    Friday, December 28, 2018 8:45 PM
  • Ok, I figured out what it is.  You have to pay a close attention at the xml file that you're reading from.  Some events are classified as data, and others are classified as action.  Also, they're all classified as value and no text, at least from the xml that I have.

    I suspect the query in your question may have been originally developed for a ring buffer target. The XML format of events in the ring buffer is different than that of an event file.

    As you found, actions and fields are different beasts in the XE world. It's not that events are classified differently, it's that data captured with the event is distinguished differently depending on whether the source data is part of the event itself (a field, contained in the /event/data/value element text) or gathered separately when the event fired (an action, /event/action/value element text).

    Additionally, each event has name, timestamp, and package attributes that are neither actions nor fields. The name and timestamp are returned by sys.fn_xe_file_target_read_file resultset as columns object_name and timestamp_utc respectively so no need to parse those from the XML. However, package is not so you'll need to extract the value from the XML if that is of interest to you.

    xEventData.value('(/event[1]/@package)', 'sysname') PackageName,
    xEventData.value('(/event[1]/@name)', 'sysname') EventName,
    xEventData.value('(/event[1]/@timestamp)', 'datetime2(3)') EventTimestamp
    One thing I'd like to make clear is that extended events in the file are not stored as XML. The actual file format is binary to reduce storage and improve performance. The sys.fn_xe_file_target_read_file TVF exposes much of the event data as XML because a relational rowset doesn't lend itself well to potentially returning thousands of columns of varying data types.

     

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Puzzle_Chen Tuesday, January 1, 2019 2:42 AM
    • Marked as answer by Olaf HelperMVP Friday, September 27, 2019 1:04 PM
    Saturday, December 29, 2018 12:14 PM