One of our core strengths is database tuning where we've been asked to look at applications or systems that are unacceptably slow when using real data volumes or where the performance has started to deteriorate once live.

Tuning needs to be addressed very early in the project lifecycle. The physical database design stage should consider data access and potential de-normalisation. After that execution plans and performance tests need to be done at the Module Testing stage (and should be part of the development standards).

However sometimes the development has occurred long before we've become involved. Perhaps it is a prototype system that has become indespensable and so has moved into production, or an existing application has been customised resulting in a change in the nature and dynamics of the product.

Either way we would usually tackle the tuning task in a similar way. Once we've gathered as much information as possible about the problem we would:

• look at logical accesses and structure of transactions
• monitor disk I/O to look for bottlenecks
• use operating system tools to look at paging and memory usage
• identify worst offending queries
• study locking strategies and check for dead-locks

There are tools available to help identify the bottlenecks and some that will suggest solutions. Occasionally it is possible to resolve the problem simply by adding additional indexes. However each application is different and usually there is no alternative to a detailed review of the process.

We like to work closely with our customer's in-house team - as they are the most knowledgeable about both their business and the systems - and systematically work through until we find a suitable solution. However we do try to be flexible: we can work on an ad-hoc basis to find the solution to enable the in-house staff to make the changes, or we can fully manage the whole process to design and implement a complete solution.