SQL Server Solutions – Health Management

The Client

Database Trouble Shooting and Database Health Management

The Problem

Working with a leading international engineering professional services organisation, Intersect were asked to trouble shoot a severe performance issue they were experiencing with their ERP system and was also effecting Microsoft Outlook and other standard MS Office applications. The ERP system was used by over 5000 employees worldwide. This issue had not suddenly occurred but had become progressively worse over time and had reach a point where it was affecting the operations of the business.

All their IT infrastructure ran off a single Storage Area Network (SAN) storage solution.  The capacity of the SAN was over 90% utilised, with 90% of this disk load coming from the Unit4 ERP system. The IT Department was under an extreme amount of pressure to resolve the issue because of the impact it was having on the business.

Before Intersects involvement, the only solution put forward was to purchase a second like for like SAN costing a six-figure sum and moving their Unit4 ERP system on to it.  This would alleviate some of the more pressing issues with other applications, however this would not have any noticeable effect on the performance of their ERP system.

What We Did

We investigated, identified and resolved the issue within 12 hours, reducing the SAN usage from around 90% to averaging around 10%.

The issue was identified as a legacy script implemented by either a 3rd party or someone internally. The script checked the system for transactions of a specific type and if it found any it would update their status field. The query was not particularly complex, but it was carrying out a SQL update command against two very large tables. The query was taking over 1 minute to run and was scheduled to run every 2 minutes.

When the query was running it was consuming extreme amounts of Disk I/O, in addition to locking up the table that it was trying to update. Because of the frequency of the schedule the underlying servers were always under excessive load.

We made a few adjustments to the query, optimized it, and reduced the run time to 1 second. This resolved the performance issues of their Unit4 ERP, and it also resolved the other applications issues they were experiencing.

The Benefits

Performance across the board increased, business operations were no longer interrupted, the organisation saved a six-figure sum, as they did not need to purchase an additional SAN. IT were no longer under constant pressure for a resolution and all the end users were happy and could continue with business as usual without interruption.