How to start database tuning and how to know, what to tune?

How to start database tuning and how to know, what to tune?

This question is frequently asked by many people online as well as offline.
Actually there is no rule of thumb for performance tuning. Before starting tuning process, one should ask him self, why he want to tune? Is database really require to tune?

Sometime people read article from web regarding "Best practice for database PT" and start to implementing same on their production database. Don't do this. Always take some precautionary steps to avoid bottleneck but this is not mean that you should implement each "Best Practice" on your database.

Performance tuning practice is like medical practice. When you visit your doctor, he/she first ask you few questions to find out what is problem and what is CAUSE of problem, before suggesting any medicine. Just imagine what happen if he/she give you medicine for all general disease without diagnosing any thing. In that case do we really need doctor? No.

So, the important thing is diagnosis of problem. Without knowing actual cause of problem you can't resolve it for a long time. :(though, you can resolve it for a short time :). Suppose you have fever and because of that you have headache and body-ache. In this case root cause of problem is fever, so take medicine for fever, not for headache.

What I am trying to convey is, first identify symptom, root cause of problem and then take necessary steps to solve it. This will solve your problem for long time without any side effect.

Most of the time you have message like application is running slow from end user. After this, it is your task to find out why it is running slow. Most of the time you will found that slow response is because of poorly written sql query. So, try to rewrite it before upgrading hardware or software for this :)

And remember that the person suffering from last stage of cancer do not need medicine for headache.

Share your thoughts for the same. Have a happy weekend..