Using Extended Events to Performance Tune Sage 500 - Overview

Using Extended Events for Performance Troubleshooting Slow Tasks in Sage 500 Blog - Overview

Using Extended Events to Performance Tune Sage 500 - Overview

Most people understand that Sage 500 is a client-server application that is written primarily in Microsoft Visual Basic version 6 (VB6.0) for the client-side code and Microsoft Transact-SQL (TSQL) for the database code. Making changes to the VB6.0 code for performance tuning requires working with Sage support or working with your Sage Partner that is certified as a master developer and has access to the source code. Performance tuning the server-side in the SQL Server is open to all customers, but it takes some specific knowledge of how to tune SQL Server.

This series of blogs seeks to share a technique that have found useful in many instances to find the few queries that are causing Performance problems making a certain task in Sage 500 slow.  This is Part 2 of a 5 part series on how to use extended events for performance troubleshooting of slow tasks in Sage 500. In the first blog we discussed  Parsing an Extended Event Attach_Activity_ID into Component Parts with the String_Split Function.

One challenge with performance tuning Sage 500 tasks is the code nesting that occurs within the code base. The VB6.0 task will call a main stored procedure, which will then execute nested stored procedures and these will call others. In addition to the nested stored procedures, the SQL tables are constrained with triggers which fire when records are inserted, updated, or deleted. And finally, the Sage 500 code will use scaler user defined functions, which will execute in a row by row fashion, each iteration executing its own set of queries.

With all this underlying code being executed for a task it is difficult to pinpoint the exact queries that are causing the performance slowdown. Difficult, but not impossible.

The basic steps that we’ve used are as follows:

1. Identify the Sage 500 Task Name as seen in SQL Server
2. Create an Extended Events Session for recording all the activity under a task
3. Run the XE Session and Brief View of Events
4. Extract the XE data from the event file and load it into a SQL table
5. Add helper columns to the XE table
6. Analyze the data for long-running single queries
7. Analyze the data for queries that aggregate to large impact.
   
   

With this technique I have been able to isolate queries within tasks such as Inventory Replenishment, Commit SO Shipments, and AR Process Invoice posting. Once isolated, I have been able to tune these processes to achieve significant performance gains. In one instance I tuned Inventory Replenishment runs that were taking more than 8 hours with only a few key changes these now consistently run in less than 15 minutes.

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

GOT QUESTIONS ABOUT Sage 500 ERP?

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