As we all know XEvents is a very strong tool or utility from SQL Server, it provides way more information than the tried and tested Traces. Extended Events was introduced way back in SQL Server 2008, but I was never able to use it just because of all the XML involved.
No matter how much valuable information you are getting but if you are not able to get that information out then it’s of now use, and I was never ever able to get my head over the XML and extract data out of it. Took me a lot of time understanding how to parse XML data using T-SQL, but still I never was able to like Extended Events just because of that. 😀
I am not sure how many DBAs out there know about this but now SQL Server Management Studio has a nice inclusion of “Extended Events” tab which is quite helpful.
You can Group your data, add Aggregation, in this blog we’ll talk about the last option in this tab that is “Export to”.
First thing is to have this tab you need either open an xel file in SSMS or go to your Extended Event and select “View Target Data”
Once you have opened the XEvent data go to the “Extended Event” and select Export and the options available are CSV file, Table and an XEL file (in case you have filtered out the data and want just this information in an XEL file)
When you click on Table as an Export option you will be prompted to connect to your target SQL Server instance, once connected you need to provide the Database, Schema and the Table (it could be either existing one or if provided a new name then it will create it for you)
It gets exported like below:
When you have this goldmine of information in your table then you can find anything, at least now I can. 🙂