January 21, 2015 at 3:00 pm
Got following query:
SELECT
event_data.value('(event/data/value)[4]', 'bigint') AS cpu_time,
--database name
event_data.value('(event/data/value)[5]', 'bigint') AS duration,
--estimated cost
--estimated rows
--nest level
event_data.value('(event/data/value)[2]', 'bigint') AS object_id,
--object name
event_data.value('(event/data/value)[3]', 'bigint') AS object_type,
event_data as execution_plan,
event_data.value('(event/data/value)[1]', 'bigint') AS source_database_id
FROM
(
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('E:\DBA_Audit\SP_Exec*.xel', 'E:\DBA_Audit\SP_Exec*.xem', null, null)
) results
Basically, is a simple T-SQL query that reads the local file for my already setup extended event sessions. But I can't find the way to retrieve the following attributes as part as the T-SQL query:
--database name
--estimated cost
--estimated rows
--nest level
--object name
Is that possible? Also... I am trying to find a BOL or some MS link with the full list of possible values for event_data.value but can't find one.
Any ideas?
January 21, 2015 at 3:19 pm
** EDIT **
Replace some values and did some trial and error and got this
SELECT
event_data.value('(event/data/value)[5]', 'bigint') AS cpu_time,
--database name
event_data.value('(event/data/value)[6]', 'bigint') AS duration,
--estimated cost
event_data.value('(event/data/value)[7]', 'bigint') as estimated_row,
--nest level
event_data.value('(event/data/value)[2]', 'bigint') AS object_id,
event_data.value('(event/data/value)[9]', 'nvarchar(max)') as object_name,
event_data.value('(event/data/value)[3]', 'bigint') AS object_type,
event_data as execution_plan,
event_data.value('(event/data/value)[1]', 'bigint') AS source_database_id
FROM
(
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('E:\DBA_Audit\SP_Exec*.xel', 'E:\DBA_Audit\SP_Exec*.xem', null, null)
) results
Result set matches the graphical plan, so it looks correct. But still don't know or can't find the full list for event_data.value
January 21, 2015 at 3:37 pm
Quick thought, one can query the xml for both element/attribute names and values, if you have some sample XML then I'll chip in a query for this.
😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply