Database Reference
In-Depth Information
As you can see from this process, there is quite a lot to do to ensure that you correctly tune the performance of a
given query. It is important to use a solid process like this in performance tuning to focus on the main identified issues.
Having said this, it also helps to keep a broader perspective about the problem as a whole, since you may believe
one aspect is causing the performance bottleneck when in reality something else is causing the problem.
Performance vs. Price
One of the points I touched on earlier is that to gain increasingly small performance increments, you need to spend
increasingly large amounts of time and money. Therefore, to ensure the best return on your investment, you should
be very objective while optimizing performance. Always consider the following two aspects:
What is the acceptable performance for your application?
Is the investment worth the performance gain?
Performance Targets
To derive maximum efficiency, you must realistically estimate your performance requirements. You can follow many
best practices to improve performance. For example, you can have your database files on the most high-performance
disk subsystem. However, before applying a best practice, you should consider how much you may gain from it and
whether the gain will be worth the investment. Those performance requirements are usually set by someone else,
either the application developers or the business consumers of the data. A fundamental part of query tuning will
involve talking to these parties to determine a good enough and realistic set of requirements.
Sometimes it is really difficult to estimate the performance gain without actually making the enhancement. That
makes properly identifying the source of your performance bottlenecks even more important. Are you CPU, memory,
or disk bound? Is the cause code, data structure, or indexing, or are you simply at the limit of your hardware? Do
you have a bad router, a poorly configured I/O path, or an improperly applied patch causing the network to perform
slowly? Be sure you can make these possibly costly decisions from a known point rather than guessing. One practical
approach is to increase a resource in increments and analyze the application's scalability with the added resource.
A scalable application will proportionately benefit from an incremental increase of the resource, if the resource
was truly causing the scalability bottleneck. If the results appear to be satisfactory, then you can commit to the full
enhancement. Experience also plays an important role here.
“Good Enough” Tuning
Instead of tuning a system to the theoretical maximum performance, the goal should be to tune until the system
performance is “good enough.” This is a commonly adopted performance tuning approach. The cost investment
after such a point usually increases exponentially in comparison to the performance gain. The 80:20 rule works very
well: By investing 20 percent of your resources, you may get 80 percent of the possible performance enhancement,
but for the remaining 20 percent possible performance gain, you may have to invest an additional 80 percent of
resources. It is therefore important to be realistic when setting your performance objectives. Just remember that
“good enough” is defined by you, your customers, and the business people you're working with. There is no standard
to which everyone adheres.
A business benefits not by considering pure performance but by considering the price of performance. However,
if the target is to find the scalability limit of your application (for various reasons, including marketing the product
against its competitors), then it may be worthwhile to invest as much as you can. Even in such cases, using a third-
party stress test lab may be a better investment decision.
 
Search WWH ::




Custom Search