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

The Science of Performance Tuning
Performance tuning SQL Server is a science. In this chapter I will show you how to get started, and I will
walk you through the steps necessary to approach any performance problem — in fact, more than just
any performance problem. The approach used here is one that works with just about any problem that
needs troubleshooting.

This approach is nothing new or revolutionary. It is just a methodical scientific approach to problem
solving using data collection and analysis to determine the root cause of any problem. This is the
approach I learned during the three years I spent working in support at Microsoft. It is an approach
that enabled me to solve problems for many Microsoft customers on a wide variety of different products
and technologies:

1.

You start with a problem statement that helps you describe what you are addressing, and
what an acceptable outcome might be. This helps frame the rest of the process.

2.

From the problem statement you can form some ideas as to what the problem might be.
From these early ideas comes a plan of attack. The plan of attack includes details of the
data you want to collect to support (or, unfortunately sometimes, refute) those ideas.

3.

Once you have some data, you analyze it to confirm or reject the original idea, and use this to
refine the problem statement as needed.

4.

If the analysis produced a clear culprit as the root cause of the problem (in the case of perfor-
mance tuning this would be that you have identified a bottleneck), then you proceed to find
ways to remove the bottleneck.

5.

When you think you have removed the bottleneck, you collect more data, re-analyze, and
confirm that the issue is resolved.

Performance tuning is an iterative process. You may need to repeat the above steps many times before
the system as a whole arrives at an acceptable level of performance. This doesn't change the basic process
that needs to be followed. You simply repeat the steps until you have removed enough bottlenecks that
the system has acceptable performance.

Unfortunately in some cases you may reach the limits of your budget in terms of time, money, or
resources before you have acceptable performance. It is always important to understand what your
time, financial, and resource constraints are as you start a project. If these matters haven't already
been defined by your client or employer, make sure that you ask them to define these matters for you.
Understanding the scope of these expectations is every bit as important to your success as your actual
performance-tuning skills.

The Problem Statement
The first step in this approach is to develop a problem statement. The problem statement doesn't need
to be more than a simple sentence or two that briefly describes the issue you are dealing with. This is a
very valuable first step as it starts to help you determine the true nature of the problem, and will help
you determine a starting point for the following steps.

If you already have a problem query isolated, then the problem statement is pretty simple, and it will
read something like, ''Stored procedure X takes too long to run. It is currently taking x seconds and
needs to take less than y milliseconds.''