Database Reference
In-Depth Information
Chapter 25
Database Workload Optimization
So far, you have learned about a number of aspects that can affect query performance, such as the tools that you can
use to analyze query performance and the optimization techniques you can use to improve query performance. Next,
you will learn how to apply this information to analyze, troubleshoot, and optimize the performance of a database
workload. I'll walk you through a tuning process, including possibly going down a bad path or two, so bear with me as
we navigate the process.
In this chapter, I cover the following topics:
•
The characteristics of a database workload
•
The steps involved in database workload optimization
•
How to identify costly queries in the workload
•
How to measure the baseline resource use and performance of costly queries
•
How to analyze factors that affect the performance of costly queries
•
How to apply techniques to optimize costly queries
•
How to analyze the effects of query optimization on the overall workload
Workload Optimization Fundamentals
Optimizing a database workload often fits the 80/20 rule: 80 percent of the workload consumes about 20 percent of
server resources. Trying to optimize the performance of the majority of the workload is usually not very productive.
So, the first step in workload optimization is to find the 20 percent of the workload that consumes 80 percent of the
server resources.
Optimizing the workload requires a set of tools to measure the resource consumption and response time of
the different parts of the workload. As you saw in Chapters 4 and 5, SQL Server provides a set of tools and utilities to
analyze the performance of a database workload and individual queries.
In addition to using these tools, it is important to know how you can use different techniques to optimize a
workload. The most important aspect of workload optimization to remember is that not every optimization technique
is guaranteed to work on every performance problem. Many optimization techniques are specific to certain database
application designs and database environments. Therefore, for each optimization technique, you need to measure the
performance of each part of the workload (that is, each individual query) before and after you apply an optimization
technique. After this, you need to measure the impact of the optimization on the complete workload using the testing
techniques outlined in Chapter 24.
