Professional SQL Server® 2005 Performance Tuning

1.2. 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 performance tuning this would be that you have identified a ...