Database Reference
In-Depth Information
•
Observe the thickness of the connecting arrows between nodes. A very thick connecting
arrow indicates a large number of rows being transferred between the corresponding nodes.
Analyze the node to the left of the arrow to understand why it requires so many rows. Check
the properties of the arrows too. You may see that the estimated rows and the actual rows are
different. This can be caused by out-of-date statistics, among other things. If you see thick
arrows through much of the plan and then a thin arrow at the end, it might be possible to
modify the query or indexes to get the filtering done earlier in the plan.
•
Look for hash join operations. For small result sets, a nested loop join is usually the preferred
join technique. You will learn more about hash joins compared to nested loop joins later in
this chapter. Just remember that hash joins are not necessarily bad, and loop joins are not
necessarily good. It does depend on the amounts of data being returned by the query.
•
Look for key lookup operations. A lookup operation for a large result set can cause a large
number of random reads. I will cover key lookups in more detail in Chapter 11.
•
There may be warnings, indicated by an exclamation point on one of the operators, which
are areas of immediate concern. These can be caused by a variety of issues, including a
join without join criteria or an index or a table with missing statistics. Usually resolving the
warning situation will help performance.
•
Look for steps performing a sort operation. This indicates that the data was not retrieved in the
correct sort order. Again, this may not be an issue, but it is an indicator of potential problems,
possibly a missing or incorrect index. This assumes you don't have an
ORDER BY
clause, which
could be the cause of the sort operation.
•
Watch for extra operators that may be placing additional load on the system such as table
spools. They may be necessary for the operation of the query, or they may indicate an
improperly written query or badly designed indexes.
•
The default cost threshold for parallel query execution is an estimated cost of 5, and that's
very low. Watch for parallel operations where they are not warranted. Just remember that the
estimated costs are numbers assigned by the query optimizer representing a mathematical
model of CPU and I/O but are not actual measures.
Analyzing Index Effectiveness
To examine a costly step in an execution plan further, you should analyze the data retrieval mechanism for the
relevant table or index. First, you should check whether an index operation is a seek or a scan. Usually, for best
performance, you should retrieve as few rows as possible from a table, and an index
seek
is usually the most efficient
way of accessing a small number of rows. A
scan
operation usually indicates that a larger number of rows have been
accessed. Therefore, it is generally preferable to seek rather than scan.
Next, you want to ensure that the indexing mechanism is properly set up. The query optimizer evaluates the
available indexes to discover which index will retrieve data from the table in the most efficient way. If a desired index
is not available, the optimizer uses the next best index. For best performance, you should always ensure that the best
index is used in a data retrieval operation. You can judge the index effectiveness (whether the best index is used or
not) by analyzing the Argument section of a node detail for the following:
•
A data retrieval operation
•
A join operation
Let's look at the data retrieval mechanism for the
SalesOrderHeader
table in the previous execution plan
(Figure
7-2
). Figure
7-5
shows the operator properties.
