Using Extended Events to Performance Tune Sage 500

Using Extended Events to Performance Tune Sage 500 – Part 1

December 3, 2019

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.