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.