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

Windows Swap Files on Two Drives

I have just gotten a new PC after a long four years of modding, resuscitating and resurrecting a P4 2.4Ghz / AMD Althon 2400+. Yes, I have changed three motherboards, three power supplies, two CPUs, 4 hard disks, 1 CDROM drive, 2 graphics cards and 4 sticks of RAM. All in all I have more than 1 Tb of hard disk space in 7 hard disks :P

Well, I finally threw in the towel and gotten a brand new computer:

• Intel Core 2 Duo E6400
• Kingston ValueRAM 2Gb
• Gigabyte 965G-DS4
• NVidia GeForce 8800 GTS 320Mb
• Barracuda 7200.10 SATA 3.0Gb/s 320-GB Hard Drive
• Asus DRW-1814BLT 18x DVD Writer (SATA)
• Coolermaster Centurion 5

The new computer is fast and quiet! However it is also very bright, thanks to the blue LED on the Centurion 5. Sigh.

Well, here is the problem. I have a old Maxtor SATA 120Gb hard disk lying in my cupboard after my first motherboard with a SATA controller died. All my subsequent motherboards does not have SATA controller. Since the 965G-DS4 can support up to 6 SATA drives, I was more than happy to pop the Maxtor into the computer.

After assembling a new computer, the natural thing for a geek to do is to optimize the system to run at the most optimum speed right? And when you have an additional drive in the computer, you will want to move the Windows paging file to the non-system drive right? But wait. The new drive is SATA2 while the old one is SATA only, therefore it will be a lot faster right?

A quick check using Nero DriveSpeed confirms my suspicion: the new Seagate is at least two times faster than the old Maxtor. So does it makes sense to move the paging file to the another slower drive, or should I leave it on the current faster drive, considering that I have 2Gb RAM, so swapping should be rare (yeah right...)

After some searching, I found the perfect solution: two swap files, one on the faster system drive and one on the older drive. Turns out that Windows will automatically select the faster drive to write to. Clever piece of software :P