International Invoice Collections Firm

The client, an international invoice collections firm, provides a credit management service to their customers through its own custom software system. This system imports details of invoices raised from Enterprise Resource Planning (ERP) systems such as SAP and Oracle and then enforces a process that manages the collection of the payment of those invoices.

It is hosted in a data centre and provided as Software as a Service (SaaS) to their customers, the system was developed in ASP.NET and uses SQL Server as its data repository.

Situation

The customer base for the system consisted of a number of well-known organisations, including many of the FTSE 100, and was expanding rapidly. There were concerns that certain aspects of the system were not performing as well as they once were. Occasionally it slowed down to a level such that users could no longer effectively use it, which escalated a concern that issues would only worsen each time large customers were added.

Solution

The client hired Ballard Chalmers for a customised SQL Server Database Performance Review to identify the source of the problems and provide recommendations on how to resolve.

Ballard Chalmers has vast experience with Database Performance Reviews and Performance Tuning. There is no “one size fits all” methodology for troubleshooting SQL Server performance issues, since the problem solving depends on the nature of the specific problem and the environment. In this instance, the basic approach was:

• Investigation of the current production databases and applications using it, and carrying out a performance analysis.
• Locating the main areas where performance issues were occurring or were likely to occur in the future.
• Determining what was needed to improve or stabilise performance.
• Providing performance improvement recommendations.

A complete code review of the .NET web application was not carried out, as most of the performance issues being experienced were believed to be database related. However, some aspects of the .NET code were assessed where they particularly related to database access.

The performance review was conducted via remote desktop access to actual SQL Server servers and then with a physical visit to the client offices.

It was found that server hardware was not the limiting factor with respect to the performance issues being experienced. Databases were also running well, with each of the firm’s customers having their own separate database. This supported growth through scaling out to a second database server and assigning new customers to that server, thereby spreading the load.

However, the database part of application had been developed over many years and as a result some of the code (T-SQL code in particular) had become cluttered and non-optimal in places. A code tidy up was needed for the main stored procedures.

Fundamentally, Ballard Chalmers identified key issues with the dashboard pages. Depending on the filters selected and the number of users using the system, the dashboard pages could cause the system to slow significantly, to the extent that users found it difficult to work or were prevented from working at all. For some customers, new indexes had to be added to specific tables to make the system perform, or certain parts of certain dashboards had been removed to ensure performance levels would be maintained.

Also, the dashboard web page was slower than expected and the bulk of the time task to display the dashboard’s pages was related to database queries being executed. However, even after queries completed approximately 1 second of additional time was taken by the web page to display the charts. It was identified that this was related to the management of large DataSets or to the actual charting software.

Ballard Chalmers recommended an analysis of the .NET and JavaScript using Visual Studio be carried out to investigate where the time is spent and improve where possible.

It was found that the “Analysis Results” stored procedure was large, slow and had locking problems. Called to obtain data for the dashboard pages it carried out extensive data aggregation and used customer specific filtering, on any number of tables and columns. The “Analysis Results” stored procedure would occasionally take up to 0.5 seconds of CPU, making extensive use of table scans, dynamic SQL, lookups of customer specific configuration information. It also used large table variables to store temporary data and deleted and inserted data in an intermediate permanent database table. There was evidence that periodic usage of the intermediary table caused blocking locks when other users called the procedure at the same time. Occasionally the procedure waited for WRITELOG indicating the transaction log writes were holding it back.

In many respects the system was too flexible and this compromised the ability to produce optimal queries, because the user could easily select from many different filter combinations.

This stored procedure and the queries it contained were identified as the main issue behind the slow dashboard pages, particularly when filters were used but not indexed.

Other aspects were identified in slowing the system down, including:

• • A particular slow “Search” stored procedure (often taking in excess of a CPU second and using multiple CPU threads)
  • The ”Debtor Balances” stored procedure used excessive input/output and executed many full table scans, often taking up to 7 seconds of CPU time for just one query.
  • Expensive queries were running at peak time.

Outcome

Overall the application was intuitive and well-focused, allowing customers to effectively manage payment of invoices, but the SQL Server Performance Review found it was suffering from database problems in a number of areas, confirming that these problems were limiting and would be even more so once additional and larger customers were added to the system.

To resolve this, Ballard Chalmers proposed that a number of critical database procedures be refactored to improve performance, and the bulk of data analysis and reporting functionality be moved to new data warehouse databases on separate warehouse database servers.

The Database Performance Review enabled the client to identify the root causes of database performance issues and provided workable suggestions on how to remedy them. With the above recommendations in place, the firm’s database servers would perform well under load and performance would be near linear with scale, thus ensuring the system could be scaled to serve any number of future customers.

“Our SQL Server Performance Review found the client’s database was suffering from problems in a number of areas and identified the root causes of performance issues.” Geoff Ballard, Chief Technical Officer