Extended Events Data

I’ve been working quite a bit over the last week or so with extended events in Denali. The sheer magnitude of what you can do with extended events is just becoming clear to me. The interesting thing though is how much the basics are similar to trace. Similar mind you, not the same. For example, the best way to gather trace data is to output it to a file and then read the file into a table for later querying. It’s the same with extended events. There’s even a function that acts as a table:

SELECT *
FROM sys.fn_xe_file_target_read_file ('C:\APath\Query Performance Tuning*.xel', NULL, NULL, NULL);

This can take advantage of roll-over files just like the old function used for traces. You can also provide offsets to read a sub-set of the file, which is cool. The event fields come back within an XML column. Here’s a sample of how I’m dealing with that from the new version of my Query Performance Tuning book:

WITH xEvents AS
(SELECT object_name AS xEventName,
CAST (event_data AS xml) AS xEventData
FROM sys.fn_xe_file_target_read_file
('C:\Apath\Query Performance Tuning*.xel', NULL, NULL, NULL))

SELECT xEventName,
xEventData.value('(/event/data[@name=''duration'']/value)[1]','bigint') Duration,
xEventData.value('(/event/data[@name=''physical_reads'']/value)[1]','bigint') PhysicalReads,
xEventData.value('(/event/data[@name=''logical_reads'']/value)[1]','bigint') LogicalReads,
xEventData.value('(/event/data[@name=''cpu_time'']/value)[1]','bigint') CpuTime,
CASE xEventName WHEN 'sql_batch_completed' THEN
xEventData.value('(/event/data[@name=''batch_text'']/value)[1]','varchar(max)')
WHEN 'rpc_completed' THEN
xEventData.value('(/event/data[@name=''statement'']/value)[1]','varchar(max)')
END AS SQLText,
xEventData.value('(/event/data[@name=''query_plan_hash'']/value)[1]','binary(8)') QueryPlanHash
FROM xEvents;

yeah, XQuery isn’t my strong point. However, you get the idea. Oh, and the CASE statement is necessary because the rpc_complete code is visible in the statement field, but the sql_batch_complete code is visible in the batch_text field. That’s just Microsoft keeping us on our toes.

Want to talk about this and other ways of gathering performance metrics? Please come and visit during SQL In The City:Los Angeles on October 28th.