Database Reference
In-Depth Information
Iterating the Process
Performance tuning is an iterative process where you identify major bottlenecks, attempt to resolve them, measure
the impact of your changes, and return to the first step until performance is acceptable. When applying your
solutions, you should follow the golden rule of making only one change at a time where possible. Any change
usually affects other parts of the system, so you must reevaluate the effect of each change on the performance of the
overall system.
As an example, adding an index may fix the performance of a specific query, but it could cause other queries to run
more slowly, as explained in Chapters 8 and 9. Consequently, it is preferable to conduct a performance analysis in a test
environment to shield users from your diagnosis attempts and intermediate optimization steps. In such a case, evaluating
one change at a time also helps in prioritizing the implementation order of the changes on the production server based on
their relative contributions. Chapter 24 explains how to automate testing your database and query performance.
You can keep on chipping away at the performance bottlenecks you've determined are the most painful and
thus improve the system performance gradually. Initially, you will be able to resolve big performance bottlenecks and
achieve significant performance improvements, but as you proceed through the iterations, your returns will gradually
diminish. Therefore, to use your time efficiently, it is worthwhile to quantify the performance objectives first
(for example, an 80 percent reduction in the time taken for a certain query, with no adverse effect anywhere else on
the server) and then work toward them.
The performance of a SQL Server application is highly dependent on the amount and distribution of user activity
(or workload) and data. Both the amount and distribution of workload and data usually change over time, and
differing data can cause SQL Server to execute SQL queries differently. The performance resolution applicable for a
certain workload and data may lose its effectiveness over a period of time. Therefore, to ensure an optimum system
performance on a continuing basis, you need to analyze system and application performance at regular intervals.
Performance tuning is a never-ending process, as shown in Figure 1-1 .
 
Search WWH ::




Custom Search