Using Extended Events to Performance Tune Sage 500 - Part 4
Extract the XE data from the event file and load it into a SQL table
I generally will copy the Extended Events file from the production server to an alternate server to process the data. The shredding that I’ve conducted will be very CPU intensive, and I don’t want to impact production. The process to get the data into a table is pretty straight forward. I’ve placed some detailed information below on how to configure the shredding of the xml. This is optional reading and work. The script to shred that I’ve provided in step 2 is pretty good, I generally use it universally in all environments that I work in.
1. Use the fn_xe_file_target_read function to read from the file(s) into a temporary table. You can specify the full file name or you can use an asterisk (*) as a wildcard to choose all the *.xel files in a specific folder. The script snippet shown below places all the data into a single-column table.
2. Verify the XML data was read into the table and review the structure of the extended events, as each event type might have slightly different data fields or attributes. You will run a SELECT statement to see the data that is in the temp table.
3. SSMS nicely lets you click the XML and will open a new query window with the XML formatted for easier reading. Click on each event_data row in the Results grid to see the structure and the data that is available. The layout will look like the following:
To shred the data into a separate table for analysis you will need to use one of three xPaths to extract the data.
These xPath snippets will allow you to construct a SELECT…INTO statement as follows:
Note that each field uses a different data type. Since I’m not going to be using this table structure in a production environment I’m not really worried about using the nvarchar(max) columns.
Running this script is the step that takes the longest time. On my development machine of 4 cores / 16 GB, processing 1.3 million events took 12.5 minutes, with the 4 CPUs all being pegged. So again you probably don’t want to do this on production.
4. Review the data in the new table with the following query:
I’ve provided a single script for download to ease the pain of the shredding.
You could also double-click the *.xel file to open in SSMS’ Event Viewer Window and use the show column in grid, grouping and aggregation functionality there. However I find doing the work in T-SQL is much more intuitive and configurable.
Note that this shredding method was modeled after a script provided by Jon Cronwell on Github.
More in this Troubleshooting series (available as they are published)
Parsing an Extended Event Attach_Activity_ID into Component Parts with the String_Split Function
Using Extended Events to Performance Tune Sage 500 - Overview
Identifying the Sage 500 Task Name as seen in SQL Server
Running the XE Session and Brief View of Events
GOT QUESTIONS ABOUT Sage 500 ERP?
Contact us if you have more questions about your Sage 500 solution >> Contact RKL eSolutions
