RKL eSolutions Blog Trends and Insights

Using Extended Events to Performance Tune Sage 500 – Part 1

Using Extended Events for Performance Troubleshooting Slow Tasks in Sage 500 Part 1

Identifying the Sage 500 Task Name as seen in SQL Server

This is part 1 in a series of blog posts on using extended events to performance tune Sage 500. On a SQL Server running under load from hundreds of users, integrations and jobs, the number of batch requests per second can be in the thousands. Trying to trace specific activity needs to be filtered.  There are several common ways to filter this activity. When a tuner is working with a specific user, the XE session can be filtered to only capture events for that user. However, many times performance needs to be recorded throughout the day as it may be slow at unexpected times or it may be difficult to coordinate a work session between the tuner and the user.

To setup an unattended XE session, I filter by the Sage 500 task name. This has to be the task name as seen by SQL Server, not the caption shown on the user’s screen.

First open the screen in the Sage 500 Client

Then open SQL Management Studio and run the sp_who2 stored procedure.

In this list find the user and the Program Name. In this example for Process Invoices, SQL Server sees the Program Name as Sage 500 ERP/AR Batch Form/ I will use this as I setup the extended event session to trace this activity.

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

Tags: Sage 500
RKL Team

Written by RKL Team

Since 2001, RKL eSolutions has helped growing companies maximize their technology resources and investment. Over the years, we have helped hundreds of small and medium sized businesses as their strategic business partner. We specialize in the needs of Entertainment, Software & SaaS, Professional Services, Manufacturing, and Non Profit organizations. Our experienced consultants have a passion for making every facet of your business successful and are intent on building a long-term relationship with every client.