Initial Guide To Database Tuning

People often ask me some quick tips for database tuning. Its like asking your Chartered Accountant for tips to save taxes just around the end of financial year. The accountant can give you some guidelines to save money that will help you file less returns for the year, but the real way of utilizing the power of money, and databases, is to invest in them from the beginning with a strong plan.

During initial days of development of a software, teams often neglect the designing of a good database. And on top of a poorly designed schema, they add SQL queries which further worsen things. You launch the product, all goes well, there is a steady increase of users (and data) and before you know it, the system is reeling under pressure trying to process the data. The bad SQL queries put additional load on the server.

Hence having a good design and writing proper SQL queries are the first steps for making a strong database. Then you do the actual database tuning and optimization. I follow the steps below to look at any database that is having a problem.

1. Database Design and Development — 70% of the problems are solved using Stored Procedures, Functions, Triggers, Temporary tables, Views etc. and having good SQL queries.

**2. Indexes **— Adding indexes is not the solution to all problems. There is no guarantee that the database server will use the index which you have added. Adding hints might help, but forcing the server to use the hint is definitely not advised. Adding indexes will further increase the size of the database.

3. DB Maintenance — Checking if any tables are corrupted and repairing the same.

4. Query Caching — Many servers now support this feature. Sometimes enabling query cache can be disadvantage for the system depending on the application stack. Any caching mechanism suffers from ‘thrashing’ and the same can be said for query caching.

5. Server Tuning — Checking and adjusting parameters like memory buffer, size of result set can affect the performance of the server. The values of these variables depend on the hardware configuration of the machine on which the server is running and the applications and services running on the same.

There are lot of other steps to be followed after this. Different database servers have different options and syntax, but the underlying principles for tuning any server is more or less the same. First check the code, then adjust the server settings and then go for changing the hardware.iting here...