The right way to query an extended events session?

I generally suggest outputting a file and querying that. It's still not fast, but you're going to generally be better off, especially in terms of the amount of information stored. Yeah, the buffer is just that, recent events, and it gets cleared regularly.

Grant beat me to it.

I only just started using extended events and have a lot to learn but I have found that querying the file directly has been pretty quick.

Below is an example of how I do it. If I fill this with a couple hundred events it still runs under a second if I comment out the event_data_xml (though, in this example, I'm not calculating averages, min, max, etc...) Perhaps this example will give you something to monkey around with.

USE tempdb

GO

SET NOCOUNT ON;

-- (1) create a getnums function

IF OBJECT_ID('dbo.GetNums') IS NOT NULL DROP FUNCTION dbo.GetNums;

GO

CREATE FUNCTION dbo.GetNums(@rows int)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

WITH

L1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1)) t(N)),

L3(N) AS (SELECT 1 FROM L1 a, L1 b, L1 c),

Nums AS (SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM L3 a, L3 b, L3 c)

SELECT TOP(@rows) N

FROM Nums

GO

-- (2) Create and start an EE session

CREATE EVENT SESSION [queryperf] ON SERVER

ADD EVENT sqlserver.sql_statement_completed

ADD TARGET package0.event_file(SET filename=N'S:\queryperf.xel',max_file_size=(20),max_rollover_files=(10))

WITH

(

MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,

MAX_DISPATCH_LATENCY=120 SECONDS, MAX_EVENT_SIZE=0 KB,

MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=ON

);

ALTER EVENT SESSION [queryperf] ON SERVER STATE = START;

GO

-- (3) Fill it up with events

IF OBJECT_ID('tempdb..#x100K') IS NOT NULL DROP TABLE #x100K;

IF OBJECT_ID('tempdb..#x1M') IS NOT NULL DROP TABLE #x1M;

IF OBJECT_ID('tempdb..#x10M') IS NOT NULL DROP TABLE #x10M;

SELECT x = newid() INTO #x100K FROM dbo.GetNums(1000);

SELECT x = newid() INTO #x1M FROM dbo.GetNums(1000000);

SELECT x = newid() INTO #x10M FROM dbo.GetNums(10000000);

GO 20

-- (4) Drop the session

DROP EVENT SESSION [queryperf] ON SERVER;

-- (5) Get results

SELECT q.duration,q.cpu_time,q.physical_reads,q.logical_reads,q.writes,/*event_data_XML,*/statement,timestamp

FROM

(

SELECT

duration=e.event_data_XML.value('(//data[@name="duration"]/value/text())[1]','int'),

cpu_time=e.event_data_XML.value('(//data[@name="cpu_time"]/value/text())[1]','int'),

physical_reads=e.event_data_XML.value('(//data[@name="physical_reads"]/value/text())[1]','int'),

logical_reads=e.event_data_XML.value('(//data[@name="logical_reads"]/value/text())[1]','int'),

writes=e.event_data_XML.value('(//data[@name="writes"]/value/text())[1]','int'),

statement=e.event_data_XML.value('(//data[@name="statement"]/value/text())[1]','nvarchar(max)'),

TIMESTAMP=e.event_data_XML.value('(//@timestamp)[1]','datetime2(7)'), *

FROM

(

SELECT CAST(event_data AS XML) AS event_data_XML

FROM sys.fn_xe_file_target_read_file('S:\queryperf*.xel', NULL, NULL, NULL)

)e

)q

WHERE q.[statement] LIKE 'select x%' --Filters out all the detritus that we're not interested in!

ORDER BY q.[timestamp] ASC;

The more I see about XE, the less I like it.

Yes, that's why I'm trying to improve my experience 😀

Thanks Grant, I was using the ring buffer simply because I am using a highly targeted session and I size the ring buffer to match the test I am running, so lost events isn't an issue - they shouldn't happen, unless I am mistaken. That made me think, if I target the Ring Buffer, is it "shared" so I could still lose events or do I get "my own"?

I will try using a file target to see if it is better.

Alan, thanks - really similar to the code I am using, except for the file target, so I'll definitely try yours and compare performance.

Does anyone have any good links for benefit comparison of Ext. Event and Trace?