Extended Events- how to log to an SQL table?

Question

Extended Events- how to log to an SQL table?

Maxer

SSCrazy Eights

Points: 8964
More actions
October 16, 2019 at 8:20 pm

#3689430

I want to enable an Extended events session.
However, I don't want it to go to a file on the HDD, I 'd really like to send it all to an SQL table so I can do grouping, analysis, reporting, etc...
How can I get that data into an SQL table for longer term storage and analysis?
Do I need to use the ring_buffer and then setup a job to query that and dump it into a table?
I'm at a loss here. Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

extended events have always puzzled me - in theory they are better than a profiler trace, but the display format and functionality is less friendly

personally i'd use a profiler trace and use the "save to table" option - even if you save to disk in profiler, there is a function you can use to treat a trace file like a table

https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-trace-gettable-transact-sql?view=sql-server-ver15

USE AdventureWorks2012;

GO

SELECT * INTO temp_trc

FROM fn_trace_gettable('c:\temp\mytrace.trc', default);

GO

MVDBA

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

There's a similar function that you can use to read from an XE file. Granted, you still have to save to disk and then query the file at regular intervals, but you have the advantage that Extended Events doesn't have such a heavy footprint as Profiler does.

John

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 3

Have a look at the ExtendedEventsWorkloadListener of WorkloadTools

The tool is designed to replay or analyse a workload from system A to system B using Extended Events.

It has a streaming XE capturer. You might be able to save to a table

Maxer SSCrazy Eights Points: 8964 More actions · Answer 4

What about storing it in the ring_buffer and just pulling it out of there?

Am I totally missing the purpose of Extended Events? I thought they were supposed to be like Profiler BUT you could leave them running (on a less verbose setting) to get overviews of your activity?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Maxer wrote:

What about storing it in the ring_buffer and just pulling it out of there?

That would work, but data in the ring buffer doesn't live for ever, so if you want a guarantee that it'll still be there when you come to query it, you need something a bit more permanent.

John

Mike Good SSCertifiable Points: 7415 More actions · Answer 6

I am not good at this stuff, really not a fan of XML, but here's an example of approach I use. Crude example, but maybe enough to get you started. The tabular SELECT is at the bottom, followed by screenshot of results If need to persist the data then the SELECT results can readily be INSERTed to a table:

--create new session and start it
CREATE EVENT SESSION ProcedureDoesNotExist ON SERVER 
ADD EVENT sqlserver.error_reported
    (ACTION (
sqlserver.client_hostname,
sqlserver.client_app_name,
sqlserver.server_principal_name,
sqlserver.sql_text)
    WHERE (error_number=2812))
ADD TARGET package0.asynchronous_file_target
(
SET FILENAME = 'ProcedureDoesNotExist.xel',
METADATAFILE = 'ProcedureDoesNotExist.xem',
        MAX_FILE_SIZE      = 1000,
MAX_ROLLOVER_FILES = 5,
INCREMENT          = 100
)
WITH (MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=ON)
GO
ALTER EVENT SESSION ProcedureDoesNotExist ON SERVER STATE=START;
GO

--read the data
SELECTtimestamp      = data.value('(event/@timestamp)[1]','datetime2'),
host           = data.value('(event/action[@name="client_hostname"]/value)[1]','varchar(50)'),
app_name       = data.value('(event/action[@name="client_app_name"]/value)[1]','varchar(250)'),
login          = data.value('(event/action[@name="server_principal_name"]/value)[1]','varchar(250)'),
textdata       = data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(4000)')
  FROM (SELECT data = CONVERT(XML, event_data)
          FROM sys.fn_xe_file_target_read_file('ProcedureDoesNotExist*.xel', 'ProcedureDoesNotExist*.xem', NULL, NULL)) AS x

ProcedureDoesNotExist

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 7

Maxer wrote:

What about storing it in the ring_buffer and just pulling it out of there?
Am I totally missing the purpose of Extended Events? I thought they were supposed to be like Profiler BUT you could leave them running (on a less verbose setting) to get overviews of your activity?

you can leave profiler traces running using sp_trace_setstatus and you don't need the client tools open - as for less verbose... yes they are a bit chatty, but if you pick your events they aren't so bad - i'm old fashioned, I started on SQL 6.5 (after i got out of access and visual foxpro) so I'm more comfortable with something i've used for 20 years.... yes I am a dinosaur

MVDBA