Using Extended Events to Performance Tune Sage 500 - Part 6
Analyze the data for long-running single queries
Wow, you are getting close to the finish…and so am I! Again this post assumes that you have followed the previous 5 parts in this blog series and have two tables: an extended events trace table with additional helper columns and a table that has aggregates for the activity_ids.
The following query shows the top 100 individual queries in the entire data set. The pct_query_duration column shows the individual query duration as a percent of the total duration of the activity_id. The attach_activity_id_text_ref column gives a reference as to what is happening in this overall activity. It comes from the event with a seq = 1 for the attach_activity_guid.
This query identifies the individual queries that are candidates for tuning.
In my example data set the first query is took 34 seconds to run and is 10 times longer than the next longest query of 3.2 seconds. It is also is 98% of the total activity that starts with spciPrePost. This is definitely a query that is in need of tuning.
More in this Troubleshooting series
GOT QUESTIONS ABOUT Sage 500 ERP?
Contact us if you have more questions about your Sage 500 solution >> Contact RKL eSolutions