Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

Performance Tuning SQL Server: Tools and Processes

Last updated Mar 28, 2003.

Performance Tuning can be a little confusing — there are so many places that you can start the process that it is difficult to know where to begin. You can start with the hardware side of things, the queries, the indexes, or any number of other places. It often becomes a matter of the tools you have at hand, and are familiar with. Hardware-oriented professionals tend to start with tools like the Windows Performance Monitor to locate hardware bottlenecks to see if adding more memory or faster drives will help. Those with more of a development background might start with the queries the server is running, using various tools provided in Visual Studio and SQL Server Management Studio (SSMS) to find and diagnose those queries taking the most amount of time. Still other professionals start with the database platform, evaluating the indexes, looking at the "Wait-States" that indicate which activity is taking the longest.

But if you start with only a "tools" approach, you might miss a chance to fix not one but many performance issues. You also run the risk of making a decision because of that tool that actually doesn't address the problem. For instance, why add more memory if the query is poorly designed? And why tune a query over and over if the indexes are inefficient or the drives are too slow?

What is more important is to define a process to systematically tune your system. I'll cover tools throughout this section of the InformIT Guide, but you should start with a solid plan for evaluating your system first.

Start With a New System or Tune One That is Currently in Use

The overall process of the tuning effort is based on when you start it. It's a bit like tuning an automobile for a race. If a mechanic is brought in during the design phase, he or she can build the car with light materials, a large engine, and will leave out the things like radios and air conditioning that just add weight and aren't necessary to win the race. If, on the other hand, they're brought in after the car is already built, there's a different process to follow to tune the car, especially if time is limited. And when isn't time a big factor?

Tuning a system is the same way. If you are brought in during the design, you can evaluate the requirements along with the application team. You can apply proper object considerations and add in just what you need. On the other hand, if you're brought in after the application is already in place and running, there are other processes to follow.

So I'll start with what you're more likely to be faced with. Most of the time you'll be called in to look at a current system that is having problems. Often these problems deal with timeouts in the application or general slowness, and the business staff will contact the application team. Many times they are also being brought into the system after its deployed, so they're starting in the same place you are. Sometimes technical professionals immediately suspect the database, and indeed as DBAs we have the most levers and knobs we can move around that affect performance. For that reason, you may even be asked to lead the tuning effort, which involves far more than just the database.

I've developed a simple outline that I use to tune a system. When I say "system," I mean all of the components — technical or not — that go into producing the output the business uses. The reason you have to consider all of these elements is that it is impossible to adequately tune a system if you just focus on one area. Think about the system as a series of roads. If you focus on one road to create better traffic patterns, you only create bottlenecks elsewhere. You have to evaluate the system as a whole.

I've broken this process into phases. Tuning a system takes time. Don't take the quick approach and bring your system down below the threshold of pain and then stop the process. Be systematic, and follow through with each step.

Unless you're only evaluating the database (always a bad idea), you need to include lots of other professionals in the process. With a set of phases like these, however, not everyone has to be involved al the time. You can bring in others or even ask them for information. The beauty of this approach is that you can define the tasks that can be done in parallel, and the ones that need to be done serially.

Let's take a look at the phases of the process. After you understand what is done in each phase, future tutorials will focus on the steps within the phases and how you can accomplish them.

Step One: Defining Components

Before you can tune a system, you need to know what the parts are. That's what is involved in the first phase of the process. You need to document each and every part of the system, starting with an overview of what it does.

Expect a lot of pushback from the business when you describe this part of the process as an integral step. For one thing they don't understand its importance. You'll need to explain that each of the technical professionals is focused on their individual areas, and that the problem might exist in more than one part of the system. Without a solid understanding of how the system works, the team won't be able to correct the issues.

Step Two: Evaluation

In the next phase you'll detail what can and should be tested, and develop a plan for that. Almost everything in a computer system has metrics that can be measured, but not all of them need to be. In fact, since monitoring the system can impact performance, you shouldn't monitor everything.

During the evaluation, you'll dispatch the members of the team to collect metrics on their respective parts of the system. You'll do the database, the developers will check the application, and the hardware folks will measure the physical parts of the system.

Step Three: Interpretation

Once you've collected the metrics from your measurements, you need to bring the team back together all at one time to look over the results. The process I recommend is that each member is required to review all of the metrics from every component and comment on what they think it means. While the hardware folks might not know what a database lock is, they might notice that the number changes dramatically at a certain time. They might be able to correlate that change with a hardware event, something you can't do.

Once a coherent view emerges, you can move on to the next phase.

Step Four: Creating the Action Plan

Finally you'll develop the steps you want to take to correct the problems you've seen in the previous phase. For each step of the plan you need to include how long the correction will take, what kind of impact it will have, the risks and who is assigned to implement it. With that list you can group the activities into things that you should do now, later and never. You can also assign the timeline for the implementation.

To perform a tuning exercise you'll need all of the skills an experienced DBA has. You can't just focus on the hardware, the platform settings, or the code. You'll also need to understand the business and the "soft" processes the users employ. For instance, if the users are printing large reports from the production database in the middle of the day, you'll have to deal with that.

The process I've described is certainly reactionary. A much better place to start the performance tuning is during the system's design. I've covered a lot of the material in this guide that you can use to develop good coding practices, size your hardware properly, and even the business practices you need to navigate to ensure that you have a fast system. I'll also cover more specific steps you can take during the design phase to prevent the major performance issues in the first place.

In the articles that follow, I'll show you how to follow each of these steps, and I'll detail the tools you need to perform each one.