Database Reference
In-Depth Information
Figure 25-9.
New execution plan after breaking apart the query
The two costliest operators are now gone. There are no more scan operations, and all the join operations are now
loop joins. But, a new data access operation has been added. You're now seeing a key lookup operation, as described
in Chapter 11, so you have more tuning opportunities.
Fixing the Key Lookup Operation
Now that you know you have a key lookup, you need to determine whether any of the methods for addressing it
suggested in Chapter 11 can be applied. First, you need to know what columns are being retrieved in the operation.
This means accessing the properties of the key lookup operator. The properties show the
VendorID
and
OrderDate
columns. This means you only need to add those columns to the leaf pages of the index through the
INCLUDE
part of
the nonclustered index. You can modify that index as follows:
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_EmployeeID] ON [Purchasing].[PurchaseOrderHeader]
(
[EmployeeID] ASC
)
INCLUDE (VendorID, OrderDate)
WITH DROP_EXISTING;
Applying this index results in a change in the execution plan and a modification in the performance. The
previous structure and code resulted in 267ms. With this new index in place, the query execution time dropped to
56ms. The execution plan is now completely different, as shown in Figure
25-10
.
