Adaptive Consulting - Exploring all paths to reach the peak in your business

Database Performance Improvements - Part 1: Troubleshooting Problems
Written by Roshan Joseph | Wednesday, 05 January 2011 16:00

slow-sign-200pxOver the past 15 years or so I have been called upon to investigate and improve database performance issues as well to reconcile databases to ensure data integrity. Whilst most of my experience has been with IBM DB2 and Microsoft SQL Server databases I have found my experience equally transferable across to Oracle and MS Access databases as well.

In this and the following article, I will be sharing some of my expertise on identifying and fixing database performance issues. While the suggestions for database performance improvements are not exhaustive, most business or their Database Administrators should find a few useful tips that will help them in resolving some of the common problems.


Identifying Performance Bottlenecks

The following is a typical scenario that many businesses face:

They spend a few hundred thousand dollars to develop or purchase and install a new business software application (think Warehouse Management or Manufacturing Planning software). Everything works great at the start and staff enjoy using the new system. Over time things start to deteriorate: Reports takes hours to run and it becomes impossible to get any real time data without wasting time.

The most common reason for this is, when you first start using the application, the database would hold a small number of data or information. This data then grows over time (sometimes in a short period) as the system is being used and you start to notice some performance issues.

 

The Symptoms

So how would a business know or be able to identify if there is a problem? Some of the easily visible symptoms for Database Performance issues include:

  • Poor response times for functions such as enquiries and data entry
  • Even simple reports are slow to run and they need to be scheduled or queued
  • Unhappy users or users reverting back to the old ways of doing things
  • An increase in Business Process issues such as Customer Orders being late, Inventory balances not being accurate, and so on

Things to check

Now that you have an idea that there could be performance issues with the database, here are some things that can be checked to get an understanding of what may be wrong. I've numbered these for ease of reference and not to imply any priority or importance.


Some simple things that can be checked include:

1. Does the Database server run more applications than just the database? Is the CPU / Memory usage very high?

2. Do users process large, non-critical reports or functions during the day, when the system is at it's busiest?

 

If you are a technical person with a good understanding of databases, the following can easily be checked:

3. Are data stored in incorrect data types? I.e. Are numbers stored in text/character fields and are dates and times stored in text or numeric fields?

4. Are the Transaction tables extremely large?

5. Are queries being run over non-indexed fields?

6. Are large volumes of data being processed for each query or report?

7. Are queries returning data for all the columns in the selected tables?

8. Are there many custom developed reports - especially reports created by everyday users of the system?


If you have expert knowledge on databases, you can also check the following:

9. Are multiple Triggers being fired each time a database record is updated?

10. Do any tables have a large number of indexes?

11. Are any database objects being updated during the day?


In the next article I will discuss how you can improve performance based on the findings for the above questions.

 

Photo by stock.xchng user hisks

Tags: