Database Reference
In-Depth Information
Costly step 2 is the hash match join operation of the query. This again is not necessarily a problem. But,
sometimes, a hash match is an indication of bad or missing indexes, or queries that can't make use of the existing
indexes, so they are frequently an area that needs work. At least, that's frequently the case for OLTP systems. For large
data warehouse systems, a hash match may be ideal for dealing with the types of queries you'll see there.
■
at times you may find that no improvements can be made to the costliest step in a processing strategy. in that
case, concentrate on the next costliest step to identify the problem. if none of the steps can be optimized further, then
move on to the next costliest query in the workload. You may need to consider changing the database design or the
construction of the query.
Tip
Optimizing the Costliest Query
Once you've diagnosed the queries with costly steps, the next stage is to implement the necessary corrections to
reduce the cost of these steps.
The corrective actions for a problematic step can have one or more alternative solutions. For example, should you
create a new index or structure the query differently? In such cases, you should prioritize the solutions based on their
expected effectiveness and the amount of work required. For example, if a narrow index can more or less do the job,
then it is usually better to prioritize that over changes to code that might lead to business testing. Making changes to
code may also be the less intrusive approach. You need to evaluate each situation within the business and application
construct you have.
Apply the solutions individually in the order of their expected benefit, and measure their individual effect on
the query performance. Finally, you can apply the solution (or solutions) that provides the greatest performance
improvement to correct the problematic step. Sometimes, it may be evident that the best solution will hurt other
queries in the workload. For example, a new index on a large number of columns can hurt the performance of action
queries. However, since that's not always true, it's better to determine the effect of such optimization techniques on
the complete workload through testing. If a particular solution hurts the overall performance of the workload, choose
the next best solution while keeping an eye on the overall performance of the workload.
Modifying the Code
The costliest operation in the query is a clustered index scan of the
PurchaseOrderHeader
table. The first thing you
need to do is understand if the clustered index scan is necessary for the query and data returned or may be there
because of the code or even because another index or a different index structure could work better. To begin to
understand why you're getting a clustered index scan, you should look at the properties of the scan operation. Since
you're getting a scan, you also need to look to the code to ensure it's sargable. Specifically you're interested in the
Predicate
property, as shown in Figure
25-8
.
