Professional SQL Server® 2005 Performance Tuning

1.3. 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.

1.3.1. 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.

1.3.1.1. Problem Statement

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.

1.3.1.2. Plan of Attack

When you don't have a clear target for data collection, it is necessary to start at the top (the server resources) and work down until you find a bottleneck that is the root cause of the problem. In this case, on the first iteration you can collect multiple sets of data. A good ...