If you’re a long time Profiler user like me then you probably often take the option of saving (or loading) your trace results to a table for easy analysis. Well, with Extended Events (XE) it’s easy to do that too.
Once you’ve opened the session to view the data you get an extra drop-down menu “Extended Events” on the menu bar in SSMS. If you open that and browse down, you can quickly find the option to export the results to a table:
Or to a CSV or XEL file if you wish.
You just need to select a destination database connection and table name and the export starts. Be warned that it doesn’t default to the current database connection. I’ve fallen for that and overwritten the data in a table with the same filename on a different SQL instance – whoops!
If the option is greyed out when you open the menu it may be that your event data is still loading. If you look closely in the above screenshot you can see I have over 8 million events captured by this session, so it took a while to load before I was able to export.
Once the export has finished you end up with a table that looks a bit like this (the exact set of columns will depend on which fields you have collected):
One gripe I have with this functionality, and that you can see above, is that all the text columns come out defined as nvarchar(max). Apart from anything else this means you can’t index the columns without changing the data type.
That’s one thing that Profiler did better, for instance database_name captured from Profiler would have been an nvarchar (128).
I thought I’d knock this post together while waiting for my statement to complete which alters database_name to the preferred nvarchar(128). With 8 million records that’s taken 21 minutes so far. And then I’ll have to index it.
Yes, maybe it’s my fault for capturing so many events, but in my defence, I’m trying to do some analysis in a dev environment to work out what I can filter out safely, before passing the same session to a client to use in production to diagnose the problem with a long running SSIS package.
Still, as you can see it’s an easy to process to export your Extended Events data to a table so you can do your analysis there – rather than having to resort to querying XML.
Got a problem or embarking on a SQL Server project and want some help and advice? I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.
8 thoughts on “Exporting Extended Events Session Data to a Table”
Question: Is there a way of Writing into the Table from the get go … without writing to the file …. like the Profiler Trace?
I’m preety sure you can’t do that. It’s understandable really, Extended Events is aimed to be super lightweight, you might have noticed with Profiler that if you write to a table rather than a file that can slow things down significantly.
Is there a way of scheduling the export of the Extended Events data to table?
You can write queries to interrogate the Extended Events data, so no reason you can’t do so and run that from a SQL Agent job.