Database performance can be a complex and difficult task, but there are some high-level maxims that can help to simplify optimizing the performance of your databases and applications. Let’s look at some of these guiding rules of thumb.
Do Not Over-Tune
You should always keep in mind the business objectives of the databases and applications you manage. It is wise to manage performance based on the expectations and budget of the business users. Even though it might be an interesting intellectual challenge for you to fine-tune a query to its best performance, doing so may take too much time away from your other duties.
It is best to base your performance tuning objectives on predefined service level agreements. And then stop tuning when performance reaches the predefined service level for which the business users are willing to pay.
Remain Focused
Of course, it is necessary to understand the goal for each task you perform and remain focused on it. This is important because the DBMS is complex, and when you are tuning one area, you might find problems in another. If so, it is best to document what you found for later and continue with the tuning task at hand. Furthermore, by jumping around trying to tune multiple things at once, you will have no idea of each task’s impact on the environment.
Do Not Panic
Performance management can be complex, with many interconnected systems and applications relying on one another to operate effectively and perform well. Sometimes, there may be unrealistic expectations that you should “know everything” about the DBMS and the environment in which it operates.
Nobody can know all there is to know about any system as complex and interrelated as a modern application that requires database services. “I don’t know, but I’ll find out” is one of the most important phrases in your communications arsenal. When you do not know, do not pretend that you do. Instead, reach out to your colleagues and manager for guidance. They may not have the answers you need, but they may know who does. And that is what you sometimes need!
Communicate Clearly
Communication is key to assuring properly tuned, high-performance database systems. Although DBAs must be at the center of that communication, discussions and workload must be coordinated and assigned among business users, programmers, managers, analysts, and system administrators.
Furthermore, the world of IT in general, and database technology in particular, sometimes uses a language all its own. Many similar and confusing terms are thrown about, and folks are expected to understand what they mean. Be sure to clearly define even basic terms so that you’re all speaking the same language.
Accept Reality
Many organizations talk about being proactive but, in reality, have very little interest in stopping performance problems before they happen. Sure, most executives say they are interested in fixing performance problems quickly … and everybody wants to fix things after the problems occur, as quickly as possible.
This can be a frustrating environment in which to work. Those of us involved in performance tuning and optimization would rather set up preventative maintenance for the DBMS environment. But this requires budget, time, and effort—all of which are in short supply for many strapped IT organizations.
So, we must be content to accept reality and deal with problems as they occur—even when you know there are better ways of tackling performance management.
[This column was adapted from Craig’s latest book, Optimizing Database Performance: Techniques to Optimize the Efficiency of Database Systems and Applications, now available at Amazon: https://tinyurl.com/5a4b37vf. —Ed.]