Database Performance Tuning Methodology

Recently, I have attended a course on the SQL Server 2000 Performance Tuning & Optimization Workshop. The course covered in detail all the components of the system such as the architecture, hardware and the database. The availability of the different tools were also introduced on how they can be use to analyze the performance of the database. Reflection on the course conducted, I realize that the principles or a methodology in performance tuning was not really discussed and covered in detail.

With some research on the internet, looking for the principles or a methodology on performance tuning, nothing much was found and discussed. Interestingly, the only substantial list of basic principles on performance tuning is found in one of the site. But I don’t think they should be constituted as principles, but more on the components to work with or look at when optimizing the database performance. What I am looking for are actually a set of principles or methodology that how should one go about with the optimization of a database performance.

Finally, I managed to find this book SQL Server 2000 Performance Tuning from Microsoft Press that discussed the performance tuning methodology. I believe this methodology is as important as understanding the components of the system and making use of the different tools. The following is a series of steps that maximize the efficiency at solving performance problems on the systems:

1. Determine the problem
2. Formulate a potential solution to the problem
3. Implement the solution
4. Analyze the results

Does a system with load or significant load suffer performance issue? Optimizing the database is not just about having a perfect database design and having the best hardware right from the start, but a lot more is in the review and on the maintenance of the database. At the start, database are seldom working at the maximum load or even half the load, so it is more likely that along the way an application evolve and push the database towards a higher load. Database performance is rarely slow due to all its components hitting their 100% usage, but it is usually due to one of its components hitting their limit resulting in a bottlenecks. A usual saying: “A chain is as strong as its weakest link” and this truly applies to the database performance.

To optimize a database performance, analyzing the existing performance and look out for bottlenecks are important. With this, the problem can be determined, and formulating a solution and implementing the solution can follow. And lastly, analyzing the result of the implemented solution will tells how effective was the solution implemented. With such a methodology, I believe it can be applicable to any database. However, to understand the components and making use of different tools available, I will said is more dependent on the type of database and it will takes more than a book to cover such topics. Also, such information can be easily found and discussed on the internet.

Some useful links on the performance tuning for the different database:
Oracle9i Database Performance Tuning Guide and Reference
Microsoft SQL Server 2000 RDBMS Performance Tuning Guide for Data Warehousing