U4BW – Windows and SQL Server Versions

An Agresso Business World customer had an issue with performance. Once a week several hundred users would all do their timesheet entry into Agresso on the same day. Users frequently complained that the system sometimes ran slow, was sluggish and hung while they were entering their timesheet details. However, this behaviour was intermittent and the issue wasn’t always evident.

So, several hundred users all doing timesheet entry into Agresso in a small time frame were experiencing performance issues.

• “Is it the Agresso Web Server that is under too much load and is struggling with the number of connections?”
• “Could additional indexes on the timesheet tables help?”
• “Is there database blocking going on which is forcing users to wait?”

It was actually none of the above.

The web server was under little load. The connections for the timesheet entry were doing very few reads and there was no sign of blocking.

The issue was traced to a large number of server side processes which were using all of the database servers resources and maxing out the disk and memory.

Server side processes definition in this instance: –  scheduled jobs and services running on the Agresso Application server.

If users connected to Agresso to do their timesheets while one of these large server side processes was occurring, SQL Server had few resources available to service them. This meant that rather than taking milliseconds to process the timesheet entry, it was taking a few seconds.

An unexpected few seconds delay at the application side seems like a long time to the end users.

Basic and some more advanced techniques were used to sort out the long running server side queries. This alleviated the load on the disk I/O subsystem and memory on the database server and enabled it to deliver a fast service to timesheet entry users.

Further ongoing performance monitoring is in place to identify other badly running queries.