RKL eSolutions | Insights, Tips and Trends from a top Sage Reseller and Technology Specialist

Using Extended Events to Performance Tune Sage 500 – Part 6

Written by RKL Team | Jan 23, 2020 11:45:00 AM

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

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

CREATE AN EXTENDED EVENTS SESSION FOR RECORDING ALL THE ACTIVITY UNDER A TASK

Running the XE Session and Brief View of Events

EXTRACT THE XE DATA FROM THE EVENT FILE AND LOAD IT INTO A SQL TABLE

ADD HELPER COLUMNS TO THE XE TABLE

ANALYZE THE DATA FOR LONG-RUNNING SINGLE QUERIES

ANALYZE THE DATA FOR QUERIES THAT AGGREGATE TO LARGE IMPACT

GOT QUESTIONS ABOUT Sage 500 ERP?

Contact us if you have more questions about your Sage 500 solution >> Contact RKL eSolutions