Database Reference
In-Depth Information
It is not unusual to find that an optimization technique has little effect—or even a negative effect—on the
other parts of the workload, thereby hurting the overall performance of the workload. For instance, a nonclustered
index added to optimize a
SELECT
statement can hurt the performance of
UPDATE
statements that modify the value
of the indexed column. The
UPDATE
statements have to update index rows in addition to the data rows. However,
as demonstrated in Chapter 6, sometimes indexes can improve the performance of action queries, too. Therefore,
improving the performance of a particular query could benefit or hurt the performance of the overall workload. As
usual, your best course of action is to validate any assumptions through testing.
Workload Optimization Steps
The process of optimizing a database workload follows a specific series of steps. As part of this process, you will
use the set of optimization techniques presented in previous chapters. Since every performance problem is a new
challenge, you can use a different set of optimization techniques for troubleshooting different performance problems.
Just remember that the first step is always to ensure that the server is well configured and operating within acceptable
limits, as defined in Chapters 2 and 3.
To understand the query optimization process, you will simulate a sample workload using a set of queries. These
are the optimization steps you will follow as you optimize the sample workload:
1.
Capture the workload.
2.
Analyze the workload.
3.
Identify the costliest/most frequently called/longest-running query.
4.
Quantify the baseline resource use of the costliest query.
5.
Determine the overall resource use.
6.
Compile detailed information on resource use.
7.
Analyze and optimize external factors.
8.
Analyze the use of indexes.
9.
Analyze the batch-level options used by the application.
10.
Analyze the effectiveness of statistics.
11.
Assess the need for defragmentation.
12.
Analyze the internal behavior of the costliest query.
13.
Analyze the query execution plan.
14.
Identify the costly operators in the execution plan.
15.
Analyze the effectiveness of the processing strategy.
16.
Optimize the costliest query.
17.
Analyze the effects of the changes on database workload.
18.
Iterate through multiple optimization phases.
As explained in Chapter 1, performance tuning is an iterative process. Therefore, you should iterate through the
performance optimization steps multiple times until you achieve the desired application performance targets. After a
certain period of time, you will need to repeat the process to address the impact on the workload caused by data and
database changes.
