Performance Tuning - Professional SQL Server 2005 Performance Tuning - page 7

SQL Trace for hours or days, or other data collection activity that may have a serious impact on the
performance of the system from which you need to capture data.
Carefully consider what data to collect so that you can confirm any theory about the root cause of the
problem while minimizing the impact on the system you need to tune.
Data Analysis
After you have collected the data you think you need, the next step is to analyze the data to confirm the
original theory.
If after careful analysis of the data collected, it appears to confirm your theory, then you have just
identified your bottleneck and can move onto removing it.
In some cases the data analysis may not be conclusive. In this case you may need to make a minor
modification to the data collection policy and re-collect data.
The analysis may also provide results that point toward a different problem. When this occurs it is time
to go back and revisit the problem statement again and to refine it with this new information.
In some cases the data analysis will show no problem at all. In fact, from the data everything looks just
great. This is often the hardest option as you now have to go back to the problem statement with what
at first seems like no additional data. You have, however, ruled out one potential cause, so you can start
again with a few less places to go looking for the bottleneck.
Performance Tuning Applied
Now that we have introduced the scientific approach based on data collection and analysis, we will walk
you through two different scenarios to help illustrate how this process might work in practice. The first
scenario is one where your users start reporting apparently random performance problems. In the second
scenario, you know exactly where the problem is, but you don't know what the exact bottleneck is.
Example 1 — Your Application Is Slow
This is the typical problem where the phone rings, and it's one of your users calling to report poor
performance. This comes out of the blue and is soon followed by many more users all calling to report
the same kind of problem with poor performance.
ProblemStatement
Writing a problem statement for these general performance problems can be quite hard, but it's okay for
it to be very general. As you start collecting data, you can refine the problem statement, making it more
specific with each iteration. Here is a first draft of a problem statement for this problem.
Problem Statement:
Users of application X on Server Y are reporting performance problems when accessing features Z1, Z2,
and Z3.